SQLServer · 最佳實踐 · 透明數據加密在SQLServer的應用 · 數據庫內核月報

## 背景作為云計算的服務提供者，我們在向用戶提供優秀的服務能力時會遇到一個合規的問題。在數據庫領域，數據是極其敏感和珍貴的，保護好數據，就如保護好企業的生命線。因此，需要采取一些預防措施來幫助保護數據庫的安全，如設計一個安全系統、加密機密資產以及在數據庫服務器的周圍構建防火墻。但是，如果遇到物理介質被盜的情況，惡意破壞方只需還原或附加數據庫即可瀏覽數據，或者遭遇拖庫情況。一種解決方案是加密數據庫中的敏感數據，并通過證書保護用于加密數據的密鑰。這可以防止任何沒有密鑰的人使用這些數據，但這種保護必須事先計劃。在SQL Server中，透明數據加密 (TDE) 可以加密 SQL Server數據文件，能夠有效保護好我們的數據資產。 ## 實現原理數據庫文件的加密在頁級別執行。已加密數據庫中的頁在寫入磁盤之前會進行加密，在讀入內存時會進行解密，TDE 不會增加已加密數據庫的大小。TDE 可對數據和日志文件執行實時 I/O 加密和解密。這種加密使用數據庫加密密鑰，該密鑰存儲在數據庫引導記錄中以供恢復時使用。數據庫加密密鑰是使用存儲在服務器的 master 數據庫中的證書保護的對稱密鑰，或者是由 EKM 模塊保護的非對稱密鑰。TDE 保護“處于休眠狀態”的數據，即數據和日志文件。體系如下: ![](https://box.kancloud.cn/2016-06-02_574fe05b3816a.jpg) ## 實現方法場景說明：如果只是簡單的配置一個加密數據庫，如下步驟即可：創建主密鑰，創建證書，創建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:\backup\master_key.mky' ENCRYPTION BY PASSWORD = 'context' ~~~ * 在principal: 備份證書 ~~~ BACKUP CERTIFICATE cer_db_mirror_principal TO FILE='D:\backup\cer_db_mirror_principal.cer' ~~~ * 在mirror: 還原master key ~~~ RESTORE MASTER KEY FROM FILE = 'D:\Backup\master_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:\Backup\cer_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:\Backup\cer_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:\Backup\cer_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\tde_mirror.bak' WITH STATS=5,COMPRESSION BACKUP LOG tde_mirror TO DISK='D:\Backup\tde_mirror.trn' WITH STATS=5,COMPRESSION ~~~ * 在mirror: 還原數據庫 ~~~ RESTORE DATABASE tde_mirror FROM DISK='D:\Backup\tde_mirror.bak' WITH STATS=5,NORECOVERY RESTORE LOG tde_mirror FROM DISK='D:\Backup\tde_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:\Backup\cer_tde.cer' WITH PRIVATE KEY ( FILE = 'D:\Backup\cer_tde.pvk', ENCRYPTION BY PASSWORD = 'tde_password'); ~~~ * 在mirror: 創建證書 ~~~ OPEN MASTER KEY DECRYPTION BY PASSWORD = 'context' CREATE CERTIFICATE cer_tde FROM FILE = 'D:\Backup\cer_tde.cer' WITH PRIVATE KEY (FILE = 'D:\Backup\cer_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:\Backup\cer_tde.cer' WITH PRIVATE KEY ( FILE = 'D:\Backup\cer_tde.pvk', ENCRYPTION BY PASSWORD = 'tde_password'); ~~~ * 備份數據庫 ~~~ BACKUP DATABASE tde_mirror TO DISK='d:\backup\tde_mirror_201605.bak' WITH STATS=5,COMPRESSION ~~~ * 在需要還原的數據庫上創建TDE證書 ~~~ CREATE CERTIFICATE cer_tde FROM FILE = 'D:\Backup\cer_tde.cer' WITH PRIVATE KEY (FILE = 'D:\Backup\cer_tde.pvk', DECRYPTION BY PASSWORD = 'tde_password') ~~~ * 還原數據庫 ~~~ RESTORE FILELISTONLY FROM DISK='D:\backup\tde_mirror_201605.bak' RESTORE DATABASE tde_mirror FROM DISK='D:\backup\tde_mirror_201605.bak' ~~~ ## 透明數據庫加密共存性 * 事務日志允許數據庫使用 TDE 具有將虛擬事務日志的剩余部分“清零”以強制加密下一個虛擬事務日志的效果。這可以保證在數據庫設置為加密后事務日志中不會留有明文。所有在數據庫加密密鑰更改前寫入事務日志的數據都將使用之前的數據庫加密密鑰加密。在數據庫加密密鑰修改過兩次后，必須執行日志備份才能再次對數據庫加密密鑰進行修改 * tempdb系統數據庫如果 tempdb 實例中的任何用戶數據庫是使用 TDE 加密的，則會加密tempdb數據庫。如果取消所有數據庫加密狀態，tempdb的加密數據庫狀態不會改變。 * 復制復制不會以加密形式從啟用了 TDE 的數據庫中自動復制數據。如果您想保護分發和訂閱服務器數據庫，則必須單獨啟用 TDE。快照復制以及用于事務和合并復制的初始數據分發，都能夠在未加密的中間文件（例如 bcp 文件）中存儲數據。在事務或合并復制期間，可以啟用加密來保護通信信道。 * 與FileStream數據即使啟用了 TDE，也不會加密 FILESTREAM 數據。 * 內存中的OLTP 可在擁有內存中 OLTP 對象的數據庫上啟用 TDE。如果啟用 TDE，則內存中 OLTP 日志記錄會被加密。如果啟用了 TDE，則不對 MEMORY_OPTIMIZED_DATA 文件組中的數據進行加密。