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