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')