SQL Server的透明数据加密（TDE）

小马同学
SQL Server
2020-08-26
17热度
0评论

USE [master];

--首先创建SQL Server中master系统数据库的MASTER KEY和CERTIFICATE

--查看master数据库是否被加密

SELECT name,is_master_key_encrypted_by_server FROM sys.databases;

--创建master数据库下的主数据库密钥

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54';

--如果创建后要删除master数据库下的主数据库密钥，可以使用下面的语句

--DROP MASTER KEY

--查看master数据库下的密钥信息

SELECT * FROM sys.symmetric_keys;

--创建证书用来保护数据库加密密钥(DEK)

CREATE CERTIFICATE master_server_cert WITH SUBJECT = N'Master Protect DEK Certificate';

--如果创建后要删除master数据库下的证书，可以使用下面的语句

--DROP CERTIFICATE master_server_cert

--创建测试数据库，后边对该数据库开启TDE加密

--IF DB_ID('db_encryption_test') IS NOT NULL

-- DROP DATABASE db_encryption_test

CREATE DATABASE db_encryption_test;

USE db_encryption_test;

--创建由master_server_cert保护的DEK 数据库加密密钥（对称密钥）

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE master_server_cert;

--如果创建后，要删除db_encryption_test数据库上的DEK 数据库加密密钥，可以使用下面的语句

--DROP DATABASE ENCRYPTION KEY

--执行上面CREATE DATABASE ENCRYPTION KEY会提示备份master系统数据库的CERTIFICATE

--提示：立刻备份证书；这里备份证书，不必制定加密私钥的对称密钥了，因为他的密钥是通过master数据库的主数据库密钥加密了。

--备份master系统数据库的MASTER KEY和CERTIFICATE：

USE master;

--打开数据库连接MASTER KEY

--OPEN MASTER KEY DECRYPTION BY PASSWORD = 'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54';

BACKUP CERTIFICATE master_server_cert TO FILE = 'D:\master_server_cert.cer'

WITH PRIVATE KEY (

FILE = 'D:\master_server_cert.pvk' ,

ENCRYPTION BY PASSWORD = 'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54');

--相应的，我们也备份一下数据库主密钥(master)

BACKUP MASTER KEY TO FILE = 'D:\master.cer'

ENCRYPTION BY PASSWORD = 'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54';

--之后在D:\路径下会出现三个文件，保存好这三个文件。

--对数据库db_encryption_test进行TDE加密，将数据库db_encryption_test设置成单用户再运行加密

ALTER DATABASE db_encryption_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--开启TDE 加密

ALTER DATABASE db_encryption_test SET ENCRYPTION ON;

--设置多用户访问

ALTER DATABASE db_encryption_test SET MULTI_USER WITH ROLLBACK IMMEDIATE;

--再次开启TDE 加密，解释下为什么在这里还要执行一次SET ENCRYPTION ON，因为不知道是不是SQL Server的一个BUG，如果只执行上面一次SET ENCRYPTION ON，后面查询是否已加密时，encryption_state的值永远为2。

ALTER DATABASE db_encryption_test SET ENCRYPTION ON;

--查看db_encryption_test数据库是否已加密 encryption_state:3 TDE加密了。tempdb也被加密了，MSDN解释是：如果实例中有一个数据库启用了TDE加密，那么tempdb也被加密。

SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;

SELECT is_encrypted FROM sys.databases WHERE name = 'db_encryption_test'

encryption_state所有值的含义：

0 = No database encryption key present, no encryption

1 = Unencrypted

2 = Encryption in progress

3 = Encrypted

4 = Key change in progress

5 = Decryption in progress

6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

详情可以查看：sys.dm_database_encryption_keys (Transact-SQL)

--加密完成，结束！

--备份数据库

--打开数据库连接MASTER KEY

OPEN MASTER KEY DECRYPTION BY PASSWORD=N'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54';

BACKUP DATABASE db_encryption_test TO DISK='D:\db_encryption_test.bak'

--关闭数据库连接MASTER KEY

CLOSE MASTER KEY

--数据库还原测试（需要用到前面生成的三个文件），将备份文件发到另外的机器

USE master;

--先还原MASTER KEY (该机器master数据库无master key)

RESTORE MASTER KEY

FROM FILE = 'D:\master.cer'

DECRYPTION BY PASSWORD = 'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54'

ENCRYPTION BY PASSWORD = 'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54';

--再还原CERTIFICATE

--打开数据库连接MASTER KEY

OPEN MASTER KEY DECRYPTION BY PASSWORD=N'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54';

--创建证书

CREATE CERTIFICATE master_server_cert

FROM FILE = 'D:\master_server_cert.cer'

WITH PRIVATE KEY (FILE = 'D:\master_server_cert.pvk',

DECRYPTION BY PASSWORD = 'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54');

--关闭数据库连接MASTER KEY

CLOSE MASTER KEY

--还原数据库

--打开数据库连接MASTER KEY

OPEN MASTER KEY DECRYPTION BY PASSWORD=N'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54';

RESTORE DATABASE db_encryption_test FROM DISK='D:\db_encryption_test.bak'

WITH MOVE 'db_encryption_test' TO 'D:\db_encryption_test.mdf',

MOVE 'db_encryption_test_log' TO 'D:\db_encryption_test_log.ldf'

--关闭数据库连接MASTER KEY

CLOSE MASTER KEY

--附加数据库

--打开数据库连接MASTER KEY

OPEN MASTER KEY DECRYPTION BY PASSWORD=N'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54';

CREATE DATABASE db_encryption_test ON PRIMARY (FILENAME=N'D:\db_encryption_test.mdf')

LOG ON (FILENAME=N'D:\db_encryption_test_log.ldf')

FOR ATTACH ;

--关闭数据库连接MASTER KEY

CLOSE MASTER KEY

--在还原或附加数据库后，最好用下面的语句检查下数据库文件是否有错误

USE master;

--打开数据库连接MASTER KEY

OPEN MASTER KEY DECRYPTION BY PASSWORD=N'CE3ECF26-6C4C-44B7-A25E-FD0A5AF9BC54';

DBCC CHECKDB([db_encryption_test]) WITH NO_INFOMSGS

--关闭数据库连接MASTER KEY

CLOSE MASTER KEY

注：

1、如果有人窃走数据库备份文件，直接拿到另外一台SQL Server服务器上还原或直接附加数据库文件，是不行的，会报错。

2、上面很多SQL脚本中使用了OPEN MASTER KEY 和 CLOSE MASTER KEY来打开和关闭MASTER KEY连接，只有像备份还原等这种关键SQL才需要打开和关闭MASTER KEY连接，普通的SELECT、UPDATE、INSERT、DELETE等数据操作SQL语句是不需要打开和关闭MASTER KEY连接的，TDE加密对于数据库用户来说是透明的，不会影响普通SQL语句的使用。

--如果开启数据库的TDE加密后，之后想要关闭TDE加密，可以使用下面的语句：

USE db_encryption_test

--设置成单用户再运行关闭加密

ALTER DATABASE TestDbEncryption SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--关闭TDE 加密

ALTER DATABASE TestDbEncryption SET ENCRYPTION OFF;

--设置多用户访问

ALTER DATABASE TestDbEncryption SET MULTI_USER WITH ROLLBACK IMMEDIATE;

--再次关闭TDE 加密，解释下为什么在这里还要执行一次SET ENCRYPTION OFF，因为不知道是不是SQL Server的一个BUG，如果只执行上面一次SET ENCRYPTION OFF，后面查询是否已关闭加密时，encryption_state的值永远为5

ALTER DATABASE TestDbEncryption SET ENCRYPTION OFF;

--查看数据库是否已关闭加密

SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;