SQLServer·最佳實踐·透明資料加密TDE在SQLServer的應用

---

title: SQLServer · 最佳實踐 · 透明資料加密TDE在SQLServer的應用

author: 石沫

背景

作為雲端計算的服務提供者，我們在向使用者提供優秀的服務能力時會遇到一個合規的問題。在資料庫領域，資料是極其敏感和珍貴的，保護好資料，就如保護好企業的生命線。因此，需要採取一些預防措施來幫助保護資料庫的安全，如設計一個安全系統、加密機密資產以及在資料庫伺服器的周圍構建防火牆。但是，如果遇到物理介質被盜的情況，惡意破壞方只需還原或附加資料庫即可瀏覽資料，或者遭遇拖庫情況。一種解決方案是加密資料庫中的敏感資料，並通過證照保護用於加密資料的金鑰。這可以防止任何沒有金鑰的人使用這些資料，但這種保護必須事先計劃。在SQL Server中，透明資料加密 (TDE) 可以加密 SQL Server資料檔案，能夠有效保護好我們的資料資產。

實現原理

資料庫檔案的加密在頁級別執行。已加密資料庫中的頁在寫入磁碟之前會進行加密，在讀入記憶體時會進行解密，TDE 不會增加已加密資料庫的大小。TDE 可對資料和日誌檔案執行實時 I/O 加密和解密。這種加密使用資料庫加密金鑰，該金鑰儲存在資料庫引導記錄中以供恢復時使用。資料庫加密金鑰是使用儲存在伺服器的 master 資料庫中的證照保護的對稱金鑰，或者是由 EKM 模組保護的非對稱金鑰。TDE 保護“處於休眠狀態”的資料，即資料和日誌檔案。體系如下:

實現方法

場景說明：

  如果只是簡單的配置一個加密資料庫，如下步驟即可：建立主金鑰，建立證照，建立DEK，應用加密，我們選取了一個比較複雜的場景，與資料庫映象的共存。。同時映象的建立是在非WINDOWS認證的安全策略，是通過證照的安全認證，這裡涉及到master key等資訊的特殊處理，證照的多樣性。  

下面列舉實現的方法：首先，需要配置好使用者資料庫的映象:

• 在principal: 建立master key
  先判斷是否存在master key, 如果存在可以先DROP再建立，因為涉及到master key密碼需要利用，老的master key不一定記得住密碼:

USE master
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD=`mypassword`
查詢驗證：
SELECT 
    * 
FROM sys.symmetric_keys
WHERE name=`##MS_DatabaseMasterKey##`

• 在Principal:建立映象使用的證照

USE master
GO
CREATE CERTIFICATE cer_db_mirror_principal
WITH SUBJECT=`certification for mirror`, 
START_DATE=`01/01/1999`, 
EXPIRY_DATE=`12/31/2099`;

查詢驗證：
SELECT 
    *
FROM sys.certificates
WHERE name=`cer_db_mirror_principal`

• 在principal: 建立映象使用的端點

CREATE ENDPOINT endpoint_mirroring 
STATE=STARTED 
AS TCP ( 
    LISTENER_PORT=5022, LISTENER_IP=ALL ) 
FOR data_mirroring( 
    AUTHENTICATION=CERTIFICATE cer_db_mirror_principal,
    ENCRYPTION= REQUIRED ALGORITHM RC4, ROLE=ALL 
)
查詢驗證：
SELECT 
    *
FROM sys.tcp_endpoints
WHERE name=`endpoint_mirroring`

• 在principal: 備份master key

OPEN MASTER KEY 
DECRYPTION BY PASSWORD = `mypassword`;
BACKUP MASTER KEY 
TO FILE = `D:ackupmaster_key.mky`
ENCRYPTION BY PASSWORD = `context`

• 在principal: 備份證照

BACKUP CERTIFICATE cer_db_mirror_principal 
TO FILE=`D:ackupcer_db_mirror_principal.cer`

• 在mirror: 還原master key

RESTORE MASTER KEY
FROM FILE = `D:Backupmaster_key.mky`
DECRYPTION BY PASSWORD = `context`
ENCRYPTION BY PASSWORD = `context`;

查詢驗證：
SELECT 
*
FROM sys.symmetric_keys
WHERE name=`##MS_DatabaseMasterKey##`

• 在mirror: 建立證照

USE master
GO 
OPEN MASTER KEY DECRYPTION BY PASSWORD = `context`

CREATE CERTIFICATE cer_db_mirror_mirror 
WITH SUBJECT=`CERTIFICATION FOR MIRROR`,
START_DATE=`01/01/1999`, EXPIRY_DATE=`12/31/2099`
查詢驗證：

SELECT 
*
FROM sys.certificates
WHERE name=`cer_db_mirror_mirror`

• 在mirror: 建立端點

CREATE ENDPOINT endpoint_mirroring 
STATE=STARTED 
AS TCP ( 
    LISTENER_PORT=5022, LISTENER_IP=ALL ) 
FOR DATA_MIRRORING( 
    AUTHENTICATION=CERTIFICATE cer_db_mirror_mirror, 
    ENCRYPTION= REQUIRED ALGORITHM RC4, ROLE=ALL 
)
查詢驗證：
SELECT * 
FROM sys.tcp_endpoints
WHERE name=`endpoint_mirroring`

• 在mirror: 備份映象證照

BACKUP CERTIFICATE cer_db_mirror_mirror 
TO FILE=`D:ackupcer_db_mirror_mirror.cer`

• 在principal: 建立端點的連線認證使用者

USE master
GO
CREATE LOGIN mirror_for_login 
WITH PASSWORD=N`22266320-AA49-4F52-A38E-98D5DE313B85`
GO
CREATE USER mirror_for_user 
FOR LOGIN mirror_for_login

• 在principal: 建立映象的證照，以打通相互成功握手通道

CREATE CERTIFICATE  cer_db_mirror_mirror
AUTHORIZATION mirror_for_user 
FROM FILE=`D:Backupcer_db_mirror_mirror.cer`;

• 在principal: 為端點授權

GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO mirror_for_login;

• 在mirror: 建立端點的連線認證使用者

USE master
GO
CREATE LOGIN principal_for_login 
WITH PASSWORD=N`dd266320-AA4d-4R52-G38E-9DF5DE313B85`
GO
CREATE USER principal_for_user
FOR LOGIN principal_for_login

• 在mirror: 建立映象的證照，以打通相互成功握手通道

CREATE CERTIFICATE cer_db_mirror_principal 
AUTHORIZATION principal_for_user 
FROM FILE=`D:Backupcer_db_mirror_principal.cer`

• 在mirror: 為端點授權

GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO principal_for_login

• 在principal: 建立一個測試書庫

CREATE DATABASE tde_mirror

• 在principal: 設定資料庫的恢復模式為FULL，並備份資料庫和日誌

ALTER DATABASE tde_mirror
SET RECOVERY FULL

BACKUP DATABASE tde_mirror 
TO DISK=`D:Backup	de_mirror.bak`
WITH STATS=5,COMPRESSION

BACKUP LOG tde_mirror 
TO DISK=`D:Backup	de_mirror.trn`
WITH STATS=5,COMPRESSION

• 在mirror: 還原資料庫

RESTORE DATABASE tde_mirror 
FROM DISK=`D:Backup	de_mirror.bak`
WITH STATS=5,NORECOVERY

RESTORE LOG tde_mirror 
FROM DISK=`D:Backup	de_mirror.trn`
WITH STATS=5,NORECOVERY

• 在mirror: 設定映象

OPEN MASTER KEY DECRYPTION BY PASSWORD = `context`  
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
ALTER DATABASE  tde_mirror
SET PARTNER = `TCP://10.0.0.1:5022`  --ip address or host name

• 在principal: 應用映象

ALTER DATABASE  tde_mirror
SET PARTNER = `TCP://10.0.0.2:5022` --ip address or host name

• 在principal: 建立TDE需要的證照

CREATE CERTIFICATE cer_tde 
WITH SUBJECT=`cert for tde`,
START_DATE=`01/01/1999`,
EXPIRY_DATE=`12/31/2099`;

注意，建立完證照，資料庫的同步狀態可能會是SUSPEND，主備完成加密設定後才會恢復正常。

• 在principal: 在主庫使用者資料庫建立DEK

USE tde_mirror
GO
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_128 ENCRYPTION 
BY SERVER CERTIFICATE cer_tde

• 在principal: 設定資料庫加密

USE master
GO
ALTER DATABASE  tde_mirror
SET ENCRYPTION ON 

查詢驗證：這個狀態應該是3
SELECT 
        d.name,DEK.encryption_state 
FROM sys.dm_database_encryption_keys dek
  INNER JOIN sys.databases d
        ON dek.database_id=d.database_id

• 在principal: 備份TDE證照

BACKUP CERTIFICATE cer_tde 
TO FILE = `D:Backupcer_tde.cer`
WITH PRIVATE KEY ( FILE = `D:Backupcer_tde.pvk`,
ENCRYPTION BY PASSWORD = `tde_password`);

• 在mirror: 建立證照

OPEN MASTER KEY DECRYPTION BY PASSWORD = `context`
CREATE CERTIFICATE cer_tde
FROM FILE = `D:Backupcer_tde.cer`
WITH PRIVATE KEY (FILE = `D:Backupcer_tde.pvk`,
DECRYPTION BY PASSWORD = `tde_password`)

驗證： 這個狀態應該是1，做FAILOVER後才變成3
SELECT 
    d.name,DEK.encryption_state 
FROM sys.dm_database_encryption_keys dek
INNER JOIN sys.databases d
    ON dek.database_id=d.database_id

總結

當TDE和映象共存時，很多步驟還是不一樣，配置也多了許多步驟，可以看出有幾個點特別注意：第一是master key的處理方式；第二是映象的證照和TDE證照的區分；第三是映象資料庫TDE的狀態變化。

還原資料庫

啟用了 TDE 的資料庫的備份檔案也使用資料庫加密金鑰進行加密。因此，當您還原這些備份時，用於保護資料庫加密金鑰的證照必須可用。也就是說，除了備份資料庫之外，您還要確保自己保留了伺服器證照的備份以防資料丟失。如果證照不再可用，將會導致資料丟失。還原資料庫需要解密過程，restore database, resotore filelistonly等等，都需要先還原證照，因此備份證照和金鑰是必須的。
示例：

• 首先備份TDE證照

BACKUP CERTIFICATE cer_tde 
TO FILE = `D:Backupcer_tde.cer`
WITH PRIVATE KEY ( FILE = `D:Backupcer_tde.pvk`,
ENCRYPTION BY PASSWORD = `tde_password`);

• 備份資料庫

BACKUP DATABASE tde_mirror 
TO DISK=`d:ackup	de_mirror_201605.bak`
WITH STATS=5,COMPRESSION

• 在需要還原的資料庫上建立TDE證照

CREATE CERTIFICATE cer_tde
FROM FILE = `D:Backupcer_tde.cer`
WITH PRIVATE KEY (FILE = `D:Backupcer_tde.pvk`,
DECRYPTION BY PASSWORD = `tde_password`)

• 還原資料庫

RESTORE FILELISTONLY  
FROM DISK=`D:ackup	de_mirror_201605.bak`
RESTORE DATABASE  tde_mirror
FROM DISK=`D:ackup	de_mirror_201605.bak`  

透明資料庫加密共存性

• 事務日誌
  允許資料庫使用 TDE 具有將虛擬事務日誌的剩餘部分“清零”以強制加密下一個虛擬事務日誌的效果。這可以保證在資料庫設定為加密後事務日誌中不會留有明文。所有在資料庫加密金鑰更改前寫入事務日誌的資料都將使用之前的資料庫加密金鑰加密。在資料庫加密金鑰修改過兩次後，必須執行日誌備份才能再次對資料庫加密金鑰進行修改
• tempdb系統資料庫
  如果 tempdb 例項中的任何使用者資料庫是使用 TDE 加密的，則會加密tempdb資料庫。如果取消所有資料庫加密狀態，tempdb的加密資料庫狀態不會改變。
• 複製
  複製不會以加密形式從啟用了 TDE 的資料庫中自動複製資料。如果您想保護分發和訂閱伺服器資料庫，則必須單獨啟用 TDE。快照複製以及用於事務和合並複製的初始資料分發，都能夠在未加密的中間檔案（例如 bcp 檔案）中儲存資料。 在事務或合併複製期間，可以啟用加密來保護通訊通道。
• 與FileStream資料
  即使啟用了 TDE，也不會加密 FILESTREAM 資料。
• 記憶體中的OLTP
  可在擁有記憶體中 OLTP 物件的資料庫上啟用 TDE。如果啟用 TDE，則記憶體中 OLTP 日誌記錄會被加密。如果啟用了 TDE，則不對 MEMORY_OPTIMIZED_DATA 檔案組中的資料進行加密。