Sqlserver關于TDE透明數據加密的使用總結

官方文檔https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2017

TDE：Transparent Data Encryption透明數據加密

master key XX：SSMS圖形界面工具中見master-security-symmetric key或見sys.symmetric_keys

CERTIFICATE YY：SSMS圖形界面工具中見master-security-certificates或見sys.certificates

數據庫啟用TDE：

大致步驟

在master數據庫里創建主密匙。

創建/使用受主密匙保護的證書。

對某個受證書保護的數據庫加密密匙。

對某個數據庫啟用TDE。

1、先drop master key主秘鑰

drop master key

如果報錯，說明有certificate在使用它，需要先把certificate刪除再刪除master key

Cannot drop master key because certificate 'C_databaseXX' is encrypted by it.

2、創建master key主秘鑰

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XX';

示例create master key encryption by password = 'TD_123456';

3、創建certificate證書，名稱一般為certdbname

create certificate certtificatename with subject ='XX';

示例create certificate certSSRSTEST with subject ='SSRSTEST database certificate data encription';

4、備份上面第3步創建certificate證書

BACKUP CERTIFICATE certtificatename TO FILE = 'XX'

WITH PRIVATE KEY ( FILE = 'XXkey' ,

ENCRYPTION BY PASSWORD = 'XX' );

示例

BACKUP CERTIFICATE certSSRSTEST TO FILE = '\\testdb1\mirror\certSSRSTEST'

WITH PRIVATE KEY ( FILE = '\\testdb1\mirror\certSSRSTESTkey' ,

ENCRYPTION BY PASSWORD = '654321_DT' );

5、對某個數據庫使用上面第3步的certificate進行加密，并啟用這個加密

create database encryption key with algorithm = XX encryption by server certificate certtificatename

alter database databasename set encryption on

示例

use SSRSTEST;

go

create database encryption key with algorithm = AES_128 encryption by server certificate certSSRSTEST

go

alter database SSRSTEST set encryption on

go

異機恢復一個TDE備份的數據庫

1、備份TDE數據庫庫

backup database SSRSTEST to disk = '\\testdb1\mirror\SSRSTEST.bak'

2、異機恢復這個數據庫

2.1、異機創建master key，這個密碼可以隨便

create master key encryption by password = '999_TD999';

2.2、異機創建CERTIFICATE證書，這個 密碼必須和源端備份CERTIFICATE時的密碼一致(即上面第4步) ，否則會報錯

CREATE CERTIFICATE certClientData

FROM FILE='\\testdb1\mirror\certSSRSTEST'

WITH PRIVATE KEY(

FILE='\\testdb1\mirror\certSSRSTESTkey',

DECRYPTION BY PASSWORD='654321_DT')

2.3、

restore database SSRSTEST from disk = '\\testdb1\mirror\SSRSTEST.bak'

異機恢復這個數據庫時如果直接恢復，有報錯，說明需要在異機創建certificate證書

restore database SSRSTEST from disk = '\\testdb1\mirror\SSRSTEST.bak'

報錯Cannot find server certificate with thumbprint '0x1640C78B8E4C6DCFA2DB4D2E97E3B206F2672FAB'.

異機創建certificate證書，有報錯說明DECRYPTION BY PASSWORD必須等于上面第4步的ENCRYPTION BY PASSWORD = '654321_DT'

use master;

go

CREATE CERTIFICATE certClientData

FROM FILE='\\testdb1\mirror\certSSRSTEST'

WITH PRIVATE KEY(

FILE='\\testdb1\mirror\certSSRSTESTkey',

DECRYPTION BY PASSWORD='TD_123456')

go

報錯The private key password is invalid

異機創建certificate證書，正確密碼還有報錯，說明需要先在異機建立master key

use master;

go

CREATE CERTIFICATE certClientData

FROM FILE='\\testdb1\mirror\certSSRSTEST'

WITH PRIVATE KEY(

FILE='\\testdb1\mirror\certSSRSTESTkey',

DECRYPTION BY PASSWORD='654321_DT')

go

報錯Please create a master key in the database or open the master key in the session before performing this operation.

創建master key隨便設置密碼password = '999_TD999'，創建證書輸入正確密碼PASSWORD='654321_DT'，一切正常

use master;

create master key encryption by password = '999_TD999';

CREATE CERTIFICATE certClientData

FROM FILE='\\testdb1\mirror\certSSRSTEST'

WITH PRIVATE KEY(

FILE='\\testdb1\mirror\certSSRSTESTkey',

DECRYPTION BY PASSWORD='654321_DT')