Sqlserver關(guān)于TDE透明數(shù)據(jù)加密的使用總結(jié)

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

TDE：Transparent Data Encryption透明數(shù)據(jù)加密

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

CERTIFICATE YY：SSMS圖形界面工具中見(jiàn)master-security-certificates或見(jiàn)sys.certificates

數(shù)據(jù)庫(kù)啟用TDE：

大致步驟

在master數(shù)據(jù)庫(kù)里創(chuàng)建主密匙。

創(chuàng)建/使用受主密匙保護(hù)的證書(shū)。

對(duì)某個(gè)受證書(shū)保護(hù)的數(shù)據(jù)庫(kù)加密密匙。

對(duì)某個(gè)數(shù)據(jù)庫(kù)啟用TDE。

1、先drop master key主秘鑰

drop master key

如果報(bào)錯(cuò)，說(shuō)明有certificate在使用它，需要先把certificate刪除再刪除master key

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

2、創(chuàng)建master key主秘鑰

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XX';

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

3、創(chuàng)建certificate證書(shū)，名稱(chēng)一般為certdbname

create certificate certtificatename with subject ='XX';

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

4、備份上面第3步創(chuàng)建certificate證書(shū)

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、對(duì)某個(gè)數(shù)據(jù)庫(kù)使用上面第3步的certificate進(jìn)行加密，并啟用這個(gè)加密

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

異機(jī)恢復(fù)一個(gè)TDE備份的數(shù)據(jù)庫(kù)

1、備份TDE數(shù)據(jù)庫(kù)庫(kù)

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

2、異機(jī)恢復(fù)這個(gè)數(shù)據(jù)庫(kù)

2.1、異機(jī)創(chuàng)建master key，這個(gè)密碼可以隨便

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

2.2、異機(jī)創(chuàng)建CERTIFICATE證書(shū)，這個(gè) 密碼必須和源端備份CERTIFICATE時(shí)的密碼一致(即上面第4步) ，否則會(huì)報(bào)錯(cuò)

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'

異機(jī)恢復(fù)這個(gè)數(shù)據(jù)庫(kù)時(shí)如果直接恢復(fù)，有報(bào)錯(cuò)，說(shuō)明需要在異機(jī)創(chuàng)建certificate證書(shū)

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

報(bào)錯(cuò)Cannot find server certificate with thumbprint '0x1640C78B8E4C6DCFA2DB4D2E97E3B206F2672FAB'.

異機(jī)創(chuàng)建certificate證書(shū)，有報(bào)錯(cuò)說(shuō)明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

報(bào)錯(cuò)The private key password is invalid

異機(jī)創(chuàng)建certificate證書(shū)，正確密碼還有報(bào)錯(cuò)，說(shuō)明需要先在異機(jī)建立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

報(bào)錯(cuò)Please create a master key in the database or open the master key in the session before performing this operation.

創(chuàng)建master key隨便設(shè)置密碼password = '999_TD999'，創(chuàng)建證書(shū)輸入正確密碼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')