sqlserver启用tde透明数据加密

今天接到一个客户要配置 SQL Server 的透明数据加密（TDE）的需求，本来这个太简单不想写的，但既然遇到了就记录一下配置过程吧。

限制条件

• 功能描述：TDE的主要作用是防止数据库备份或数据文件被非法获取之后，在没有数据加密密钥的情况下是无法恢复或附加数据库的
• 启用步骤：创建主密钥 -> 创建由主密钥保护的证书 -> 创建由此证书保护的数据库加密密钥 -> 将数据库设置为使用加密
• 性能影响：加密会增加少量的 CPU 开销，并且加密后的数据库备份文件压缩率会降低
• tempdb：对任何用户数据库开启TDE加密，则会同时加密 tempdb 系统数据库，可能会对同一个实例上的未加密数据库产生性能影响
• 版本支持：TDE 仅在 SQL Server 2008 和更高版本的 Enterprise Edition 上可用，但从 SQL Server 2019 开始，标准版也支持该功能

如何启用 TDE

• 第1步，在 master 库下，创建主密钥和证书

USE master 
GO 

--.查看master数据库是否被加密
SELECT name,is_master_key_encrypted_by_server FROM sys.databases 

--.创建master数据库下的主密钥
-- select * from sys.symmetric_keys where name ='##MS_DatabaseMasterKey##' 
-- DROP MASTER KEY 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Passw0rd_123' 

--.创建由主密钥保护的证书,用来保护数据库加密密钥(DEK)
-- select * from sys.certificates where name not like '##MS%' 
-- DROP CERTIFICATE master_cert 
CREATE CERTIFICATE master_cert WITH SUBJECT = N'Master Protect DEK Certificate'

• 第2步，创建测试数据库

CREATE DATABASE tde_demo 
GO 
CREATE TABLE tde_demo..tab_001(id int, name sysname)
INSERT INTO tde_demo..tab_001 VALUES(1,'aaa')
SELECT * FROM tde_demo..tab_001

• 第3步，在测试数据库下，启用TDE加密

USE tde_demo 
GO 

--.创建由 MASTER CERTIFICATE 保护的数据库加密密钥(DEK)
-- SELECT db_name(database_id),encryption_state,encryption_state_desc FROM sys.dm_database_encryption_keys
-- DROP DATABASE ENCRYPTION KEY 
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE master_cert 
GO

--.开启tde加密
--.建议先设置成单用户模式,开启tde加密之后再改回多用户模式
ALTER DATABASE tde_demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
ALTER DATABASE tde_demo SET ENCRYPTION ON 
ALTER DATABASE tde_demo SET MULTI_USER WITH ROLLBACK IMMEDIATE 
GO

--.查看哪些库被已TDE加密
--.对任何用户数据库开启TDE加密,则会同时加密 tempdb 系统数据库,可能会对同一个实例上的未加密数据库产生性能影响
SELECT db_name(database_id),encryption_state,encryption_state_desc FROM sys.dm_database_encryption_keys

• 第4步，在 master 库下，备份一下证书和主密钥（请务必妥善保存），以便后续还原

USE master 
GO 
EXEC master.sys.xp_create_subdir 'C:\CERTIFICATE'

--.备份证书
BACKUP CERTIFICATE master_cert TO FILE = 'C:\CERTIFICATE\master_cert.cer' 
WITH PRIVATE KEY (FILE = 'C:\CERTIFICATE\master_cert.pvk', ENCRYPTION BY PASSWORD = N'Passw0rd_123')

--.备份主密钥
BACKUP MASTER KEY TO FILE = 'C:\CERTIFICATE\master_key.key' ENCRYPTION BY PASSWORD = N'Passw0rd_123'
GO

• 第5步，备份一次数据库

BACKUP DATABASE tde_demo TO DISK='C:\CERTIFICATE\tde_demo.bak'

恢复测试

• 在另外一台机器上执行 RESTORE DATABASE 恢复数据库，则会报错如下

--.恢复数据库
RESTORE DATABASE tde_demo FROM DISK = 'C:\tde_demo.bak'

--.报错如下
消息 33111，级别 16，状态 3，第 1 行
找不到指纹为 '0x257CB79CAE38E5157FC6E029DA92D62FB1A68971' 的服务器 证书。
消息 3013，级别 16，状态 1，第 1 行
RESTORE DATABASE 正在异常终止。

• 方式1，新机器通过密码来恢复主密钥和证书，则相对简单

USE master 
GO 

--.创建数据库主密钥
-- select * from sys.symmetric_keys where name ='##MS_DatabaseMasterKey##' 
-- DROP MASTER KEY 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Passw0rd_123'

--.还原证书
-- select * from sys.certificates where name not like '##MS%' 
-- DROP CERTIFICATE master_cert 
CREATE CERTIFICATE master_cert FROM FILE = 'C:\master_cert.cer' WITH PRIVATE KEY (FILE = 'C:\master_cert.pvk', DECRYPTION BY PASSWORD = N'Passw0rd_123')

--.再次恢复数据库
RESTORE DATABASE tde_demo FROM DISK = 'C:\tde_demo.bak'

--.确认数据
SELECT * FROM tde_demo..tab_001

• 方式2，新机器通过主密钥来恢复主密钥和证书，则每次操作都要先 OPEN MASTER KEY（在会话中打开主密钥）

USE master 
GO 

--.恢复数据库主密钥
-- select * from sys.symmetric_keys where name ='##MS_DatabaseMasterKey##' 
-- DROP MASTER KEY 
RESTORE MASTER KEY FROM FILE = 'C:\master_key.key' DECRYPTION BY PASSWORD = N'Passw0rd_123' ENCRYPTION BY PASSWORD = N'Passw0rd_123'

--.还原证书
-- select * from sys.certificates where name not like '##MS%' 
-- DROP CERTIFICATE master_cert 
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'Passw0rd_123'
CREATE CERTIFICATE master_cert FROM FILE = 'C:\master_cert.cer' WITH PRIVATE KEY (FILE = 'C:\master_cert.pvk', DECRYPTION BY PASSWORD = N'Passw0rd_123')
CLOSE MASTER KEY

--.再次恢复数据库
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'Passw0rd_123'
RESTORE DATABASE tde_demo FROM DISK = 'C:\tde_demo.bak'
CLOSE MASTER KEY

--.确认数据
SELECT * FROM tde_demo..tab_001

• 对于恢复后的数据库，则默认启用了 TDE 加密

--.对任何用户数据库开启TDE加密，则会同时加密 tempdb 系统数据库，可能会对同一个实例上的未加密数据库产生性能影响
SELECT db_name(database_id),encryption_state,encryption_state_desc FROM sys.dm_database_encryption_keys

附录

如何移除特定库的TDE加密

• 如何移除特定库的TDE加密

USE tde_demo 
GO 

--.先禁用tde加密,再删除DEK加密密钥
ALTER DATABASE tde_demo SET ENCRYPTION OFF 
DROP DATABASE ENCRYPTION KEY 
SELECT db_name(database_id),encryption_state,encryption_state_desc FROM sys.dm_database_encryption_keys

如何移除整个实例的TDE加密

• 如果要关闭实例级别的TDE加密，首先要移除所有用户数据库的TDE加密，再依次删除证书和主密钥

USE master 
GO 

--.先删证书,再删主密钥
DROP CERTIFICATE master_cert 
DROP MASTER KEY 
SELECT name,is_master_key_encrypted_by_server FROM sys.databases

• 参考：SQL Server 各个版本及其支持的功能
• 参考：透明数据加密TDE