SQLServer的TDE加密

TDE的主要作用是防止数据库备份或数据文件被偷了以后，偷数据库备份或文件的人在没有数据加密密钥的情况下是无法恢复或附加数据库的。

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

USE [master];
GO


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


--创建master数据库下的主数据库密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'1qaz@WSX3edc$RFV';

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


--查看master数据库下的密钥信息
SELECT * FROM sys.symmetric_keys;


--创建证书用来保护 数据库加密密钥 (DEK)
CREATE CERTIFICATE master_server_certficate WITH
SUBJECT = N'Master Protect DEK Certificate';

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

创建测试数据库TestDbEncryption，该数据库将会开启TDE加密：

CREATE DATABASE TestDbEncryption;

GO

接下来启用数据库TestDbEncryption的DEK 数据库加密密钥（对称密钥）

USE TestDbEncryption;
GO

--创建由master_server_cert保护的DEK 数据库加密密钥 （对称密钥）
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE master_server_certficate;

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

--执行上面的CREATE DATABASE ENCRYPTION KEY语句以后出现：
/*
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
*/
--提示你，立刻备份证书;这里备份证书,不比制定加密私钥的 对称密钥了.因为他的密钥是通过master数据库的主数据库密钥加密了.

执行上面CREATE DATABASE ENCRYPTION KEY会提示备份master系统数据库的CERTIFICATE，所以接下来我们备份master系统数据库的MASTER KEY和CERTIFICATE：

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV';

--备份master系统数据库的CERTIFICATE
BACKUP CERTIFICATE master_server_certficate TO FILE = 'D:MSSQL_TDE_Keysmaster_server_certficate.cer' 
WITH PRIVATE KEY ( 
FILE = 'D:MSSQL_TDE_Keysmaster_server_certficate.pvk' , 
ENCRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV');

--关闭数据库连接MASTER KEY
CLOSE MASTER KEY
GO 


USE master;
GO

--相应的，我们也备份一下数据库主密钥(master)
--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV';
BACKUP MASTER KEY TO FILE = 'D:MSSQL_TDE_Keysmaster.cer' 
ENCRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV';

--关闭数据库连接MASTER KEY
CLOSE MASTER KEY
GO

之后在SQL Server服务器的D:MSSQL_TDE_Keys路径下会出现三个文件，保存好这三个文件，如下所示：

下面我们就要开启测试数据库TestDbEncryption的TDE加密了：

USE TestDbEncryption
GO

--生产环境下，设置成单用户在运行加密
ALTER DATABASE TestDbEncryption SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--开启TDE 加密
ALTER DATABASE TestDbEncryption SET ENCRYPTION ON;
GO

--设置多用户访问
ALTER DATABASE TestDbEncryption SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

--再次开启TDE 加密，解释下为什么在上面设置多用户访问后，这里还要执行一次SET ENCRYPTION ON，因为不知道是不是SQL Server的一个BUG，如果只执行上面一次SET ENCRYPTION ON，后面查询SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys时，encryption_state的值永远为2
ALTER DATABASE TestDbEncryption SET ENCRYPTION ON;
GO

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

USE TestDbEncryption
GO

--生产环境下，设置成单用户在运行加密
ALTER DATABASE TestDbEncryption SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--关闭TDE 加密
ALTER DATABASE TestDbEncryption SET ENCRYPTION OFF;
GO

--设置多用户访问
ALTER DATABASE TestDbEncryption SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

--再次关闭TDE 加密，解释下为什么在上面设置多用户访问后，这里还要执行一次SET ENCRYPTION OFF，因为不知道是不是SQL Server的一个BUG，如果只执行上面一次SET ENCRYPTION OFF，后面查询SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys时，encryption_state的值永远为5
ALTER DATABASE TestDbEncryption SET ENCRYPTION OFF;
GO

查看TestDbEncryption数据库是否被加密：

--查看TestDbEncryption数据库是否被加密 encryption_state:3 TDE加密了
SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;
/*
发现tempdb也被加密了。MSDN解释是：如果实例中有一个数据库启用了TDE加密，那么tempdb也被加密
*/

查询结果为：

这里给出MSDN上列出的encryption_state所有值的含义：

encryption_state
Indicates whether the database is encrypted or not encrypted.

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)

之后我们先备份已启用TDE的测试数据库TestDbEncryption到数据库备份文件TestDbEncryption.bak：

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'1qaz@WSX3edc$RFV';

BACKUP DATABASE TestDbEncryption
TO DISK='D:MSSQL_BackupTestDbEncryption.bak'

--关闭数据库连接MASTER KEY
CLOSE MASTER KEY

接下来，找另外一台机器或者实例来测试，如果数据库备份文件被盗走了，防止被还原。这时就要用到我们前面生成的三个文件了：

先在另外一台机器还原MASTER KEY：

USE master;
GO

--先在另外一台机器还原了MASTER KEY (该机器master数据库无master key)
RESTORE MASTER KEY 
FROM FILE = 'D:MSSQL_TDE_Keysmaster.cer' 
DECRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV'
ENCRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV';
GO

再还原CERTIFICATE：

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'1qaz@WSX3edc$RFV';
--创建证书
CREATE CERTIFICATE master_server_certficate
FROM FILE = 'D:MSSQL_TDE_Keysmaster_server_certficate.cer' 
WITH PRIVATE KEY (FILE = 'D:MSSQL_TDE_Keysmaster_server_certficate.pvk', 
DECRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV');
GO 

--关闭数据库连接MASTER KEY
CLOSE MASTER KEY

最后使用前面准备的数据库备份文件TestDbEncryption.bak，还原数据库TestDbEncryption：

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'1qaz@WSX3edc$RFV';

RESTORE DATABASE TestDbEncryption FROM DISK='D:MSSQL_BackupTestDbEncryption.bak'
WITH MOVE 'TestDbEncryption'
TO 'D:MSSQL_DataTestDbEncryption.mdf',
MOVE 'TestDbEncryption_log'
TO 'D:MSSQL_LogTestDbEncryption_log.ldf'
GO


--关闭数据库连接MASTER KEY
CLOSE MASTER KEY

如果要直接附加启用TDE的数据库mdf和ldf文件到SQL Server，可以采用下面的语句：

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'1qaz@WSX3edc$RFV';

--附加数据库
CREATE DATABASE TestDbEncryption 
ON PRIMARY 
(
FILENAME=N'C:UsersAdministratorDesktopTestDbEncryption.mdf'
)
LOG ON 
(
FILENAME=N'C:UsersAdministratorDesktopTestDbEncryption_log.ldf'
)
FOR ATTACH ;
GO

--关闭数据库连接MASTER KEY
CLOSE MASTER KEY

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

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'1qaz@WSX3edc$RFV';

DBCC CHECKDB([TestDbEncryption]) WITH NO_INFOMSGS

--关闭数据库连接MASTER KEY
CLOSE MASTER KEY

如果DBCC CHECKDB语句没有报错，就没有问题。

所以如果有人盗走数据库备份文件TestDbEncryption.bak，直接拿到另外一台SQL Server服务器上还原，或者直接附加TestDbEncryption.mdf和TestDbEncryption_log.ldf数据库文件到另一台SQL Server，是不行的，会报错。

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

在新的SQL Server服务器或实例还原MASTER KEY和CERTIFICATE后，还需要注意的几个地方：
首先，当你在新的SQL Server服务器或实例还原MASTER KEY和CERTIFICATE后，执行下面的SQL语句：

USE master;
GO

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

你会发现master系统数据库的is_master_key_encrypted_by_server为0

这是因为is_master_key_encrypted_by_server表示的是当前SQL Server实例使用的MASTER KEY，是否是在当前SQL Server实例中创建的，很明显由于我们此时的MASTER KEY是通过RESTORE MASTER KEY语句还原得到的，所以不是由当前SQL Server实例创建的，因此master系统数据库的is_master_key_encrypted_by_server为0.

MSDN上对is_master_key_encrypted_by_server的解释如下:

The is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted by the service master key.

详情可以查看 CREATE MASTER KEY (Transact-SQL)

当前SQL Server实例master系统数据库的is_master_key_encrypted_by_server为0，会引出下一个问题，那就是当前SQL Server实例中新的数据库都无法成功开启TDE，例如我们现在创建一个数据库Demo

CREATE DATABASE Demo

你会发现按照我们前面介绍的方法开启数据库Demo的TDE后，执行如下查询

--查看TestDbEncryption数据库是否被加密 encryption_state:3 TDE加密了
SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;

结果Demo的encryption_state始终为2：

这就是因为master系统数据库的is_master_key_encrypted_by_server为0导致的。

所以当在新的SQL Server服务器或实例上还原或附加TestDbEncryption数据库后，我们应该重建新的SQL Server服务器或实例的MASTER KEY和CERTIFICATE

因此当还原或附加TestDbEncryption数据库后，我们首先应该关闭TestDbEncryption数据库的TDE加密，如下SQL语句所示：

USE TestDbEncryption
GO

--生产环境下，设置成单用户在运行加密
ALTER DATABASE TestDbEncryption SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--关闭TDE 加密
ALTER DATABASE TestDbEncryption SET ENCRYPTION OFF;
GO

--设置多用户访问
ALTER DATABASE TestDbEncryption SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

--再次关闭TDE 加密，解释下为什么在上面设置多用户访问后，这里还要执行一次SET ENCRYPTION OFF，因为不知道是不是SQL Server的一个BUG，如果只执行上面一次SET ENCRYPTION OFF，后面查询SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys时，encryption_state的值永远为5
ALTER DATABASE TestDbEncryption SET ENCRYPTION OFF;
GO

接着删除数据库TestDbEncryption的DEK 数据库加密密钥（对称密钥）

USE TestDbEncryption;
GO

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

然后删除当前SQL Server中master系统数据库的MASTER KEY和CERTIFICATE

USE [master]
GO

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

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

重新创建SQL Server中master系统数据库的MASTER KEY和CERTIFICATE

USE [master]
GO

--创建master数据库下的主数据库密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'1qaz@WSX3edc$RFV';
GO

--创建证书用来保护 数据库加密密钥 (DEK)
CREATE CERTIFICATE master_server_certficate WITH
SUBJECT = N'Master Protect DEK Certificate';
GO

这时再查询

USE [master];
GO

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

我们就会发现master系统数据库的is_master_key_encrypted_by_server为1了

原因就是我们现在通过新的SQL Server服务器或实例，重新创建了master系统数据库的MASTER KEY和CERTIFICATE

接着和前面一样记住备份新创建的MASTER KEY和CERTIFICATE

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV';

--备份master系统数据库的CERTIFICATE
BACKUP CERTIFICATE master_server_certficate TO FILE = 'D:MSSQL_TDE_Keysmaster_server_certficate.cer' 
WITH PRIVATE KEY ( 
FILE = 'D:MSSQL_TDE_Keysmaster_server_certficate.pvk' , 
ENCRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV');

--关闭数据库连接MASTER KEY
CLOSE MASTER KEY
GO 


USE master;
GO

--相应的，我们也备份一下数据库主密钥(master)
--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV';
BACKUP MASTER KEY TO FILE = 'D:MSSQL_TDE_Keysmaster.cer' 
ENCRYPTION BY PASSWORD = '1qaz@WSX3edc$RFV';

--关闭数据库连接MASTER KEY
CLOSE MASTER KEY
GO

最后按照本文前面说的方法，开启新的SQL Server服务器或实例上各个数据库的TDE加密就行了，这时新的SQL Server服务器或实例上的数据库应该都可以成功开启TDE加密了，开启后查询

--查看TestDbEncryption数据库是否被加密 encryption_state:3 TDE加密了
SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;

encryption_state都应该为3了