Sqlserver关于TDE透明数据加密的使用总结

官方文档
https://docs.microsoft.com/zh-cn/sql/relational-databases/security/encryption/encryption-hierarchy?view=sql-server-ver15，
https://docs.microsoft.com/zh-cn/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15
https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-database-encryption-key-transact-sql?view=sql-server-ver15

TDE：Transparent Data Encryption透明数据加密

master key XX：SSMS图形界面工具中见master-security-symmetric key或见sys.symmetric_keys
CERTIFICATE YY：SSMS图形界面工具中见master-security-certificates或见sys.certificates

总结
1、每个数据库中都可以创建master key和CERTIFICATE，一个数据中要创建CERTIFICATE，则这个数据库中必须先存在master key。如果master库有master key但是DBA库没有master key，这时在DBA库中建立CERTIFICATE会报错Please create a master key in the database or open the master key in the session before performing this operation.
2、一个数据库是否创建了master key，可以进入这个数据库下查询sys.symmetric_keys,当然更好的方法是通过sys.databases的is_master_key_encrypted_by_server字段来看，也就是说要查询哪些数据库创建了master key可以使用语句select name,is_master_key_encrypted_by_server from sys.databases
3、一个数据库是否创建CERTIFICATE，只能进入这个数据库下查询sys.certificates才能看到
4、如果创建master key或证书certificate都使用密码加密了，但是忘记了加密的密码，可以使用alter master key和alter certificate改过来
5、某个数据库是否已经启用TDE加密，可以通过sys.dm_database_encryption_keys视图的字段encryption_state看到，此视图是实例级别的全局在任何数据库下执行的结果都是一样。对某个数据库create database encryption key后在sys.dm_database_encryption_keys中可查到该数据库了，但是sys.dm_database_encryption_keys的encryption_state字段还是显示1未加密，只有alter database databasename set encryption on后，sys.dm_database_encryption_keys的encryption_state字段显示为2正在加密或3已加密。
6、如果 SQL Server 实例中的任何其他数据库是使用 TDE 加密的，则会加密 tempdb 系统数据库。
7、TDE透明数据加密用到master key和证书 certificate，而master key就是SYMMETRIC KEY，因为master key和SYMMETRIC KEY都存放在sys.symmetric_keys中，当然TDE透明数据加密也可以使用ASYMMETRIC KEY，参见CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } ENCRYPTION BY SERVER { CERTIFICATE Encryptor_Name | ASYMMETRIC KEY Encryptor_Name }，所以SYMMETRIC KEY和ASYMMETRIC KEY和证书certificate都是为TDE透明数据加密服务的
8、TDE透明数据加密是使用对称密钥进行加密的一种特殊情况，TDE使用称为数据库加密密钥的对称密钥加密整个数据库。 数据库加密密钥受由数据库主密钥或存储在EKM模块中的非对称密钥保护的其他密钥或证书保护。TDE透明数据加密一般用到master key和证书CERTIFICATE即可完成，虽然对数据库创建加密秘钥也可以使用ASYMMETRIC KEY，但是很少用这种方式，衍生出来的SYMMETRIC KEY和ASYMMETRIC KEY可参考官方文档。TDE透明数据加密是针对整个数据库层面的也就是针对数据和日志文件，比如影响整个数据库的备份恢复、快照、删除、脱机、分离、转换READ ONLY 状态等，主要是DBA这种管理人员使用的。
9、涉及的系统视图或系统表是sys.databases、sys.symmetric_keys、sys.certificates、sys.dm_database_encryption_keys

TDE涉及到的一些管理规范
1、in one instance，should all dbs encryption use the same certificate or each db have a seperate certificate
在一个实例中，是否只在master库在建立一个证书，然后所有的数据库共用这个证书？还是在master库中为每个数据库建立一个证书？
2、if each db have a seperate certificate， how do we name the certificate name，like instancename_dbname_cert?
如果在master库中为每个数据库建立一个证书，证书的命名规范?
3、if we backup certificate ，should we need private key and encryption password？
备份证书时，是否需要使用私有秘钥和加密密码对备份文件加密？
4、if we need private key and encryption password， should the private key named as instancename_dbname_certificatename_privatekey, and every certificate backup file use the same encryption password ?
如果备份证书时，使用了私有秘钥文件和加密密码对备份文件加密，私有秘钥文件的命名规范？每个证书的备份是否使用相同的加密密码

数据库启用TDE的大致步骤：
在master数据库里创建主秘钥。
创建/使用受主密匙保护的证书。
使用证书对某个数据库创建加密秘钥。
对某个数据库启用加密。

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

–备注：直接CREATE MASTER KEY会有报错Database master keys without password are not supported in this version of SQL Server.

3、创建certificate证书，名称一般为certdbname
create certificate certtificatename with subject =‘XX’;
示例

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

–备注：如果该数据库中没有master key就创建certificate证书会报错Please create a master key in the database or open the master key in the session before performing this operation.

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 = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } ENCRYPTION BY SERVER { CERTIFICATE Encryptor_Name | ASYMMETRIC KEY Encryptor_Name }
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

–备注1：对某个数据库create database encryption key可以使用证书CERTIFICATE也可以使用非对称秘钥ASYMMETRIC KEY，但是只能使用master库中的证书XX，使用其他数据库或当前数据库的证书XX会报错Cannot find the certificate ‘XX’, because it does not exist or you do not have permission。比如DBA数据库也有master key也有证书DBA_cert,但是master库并没有证书DBA_cert,此时DBA数据库创建加密秘钥时如果指定DBA_cert会报错Cannot find the certificate ‘DBA_cert’
–备注2：对某个数据库create database encryption key后在sys.dm_database_encryption_keys中可查到该数据库了，但是sys.dm_database_encryption_keys的encryption_state字段还是显示1未加密，只有alter database databasename set encryption on后，sys.dm_database_encryption_keys的encryption_state字段显示为2正在加密或3已加密

异机恢复一个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')

取消数据库加密的方法
1、

use master
select * from sys.certificates

–查出certificatename

2、

use master
SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length FROM sys.dm_database_encryption_keys WHERE db_name(database_id) not in('tempdb')

–查询哪个数据使用了加密

3、

use master
alter database databasename set encryption off

–在master中取消某个databasename的加密功能

use databasename
DROP DATABASE ENCRYPTION KEY  

–如果不先把数据库的加密功能取消，则无法删除数据库的加密，会出现这个报错Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.
–一般到了这个步骤，再对这个数据库备份，后面这个备份拿到其他实例恢复就不会出现这个报错Cannot find server certificate with thumbprint ‘XX’.

4、

use master
drop certificate certificatename

–删除加密证书，如果不先把某个数据库的加密删除，会出现这个报错The certificate ‘certificatename’ cannot be dropped because it is bound to one or more database encryption key.