SQL Server2012数据库TDE加密

SQL Server数据库TDE加密

1.1 案例环境介绍

完成本节数据TDE加密的演示，需要准备两台虚拟机环境，一台模拟生产数据库，一台模拟迁移还原的数据库环境。

1.1.1 案例环境配置

完成本章案例数据备份部分的学习，需要准备3个节点的环境。具体环境配置要求，见表6-3。

表6-3 本案例环境配置

序号	1	2
角色	生产数据库	还原数据库
IP地址	192.168.0.131	192.168.0.133
操作系统	Windows Server2016	Windows Server2012R2
安装软件	Datacenter版	SQL 2012企业版SP1
备注	SQL 2012企业版SP1

1.1.2 案例拓扑图

如图6.101所示，这是完成本案例的实验环境拓扑图。

图6.101 SQL Server数据库TDE加密环境拓扑图

1.2 数据库加密实施

本章在实验环境首先对数据库进行TDE加密，再展示合法用户在拥有密钥和证书得前提下，完成数据库的异机还原和附加的操作。

1.2.1 执行TDE加密

1.创建MASTER KEY（主密钥） 和CERTIFICATE（证书）

在“Microsoft SQL Server Managerment Studio”中，点击“新建查询”，在查询分析器中执行如下命令，创建MASTER KEY（主密钥） 和CERTIFICATE（证书）。

USE master
GO
--创建master数据库下的主密钥,在生产环境中PASSWORD要设置的足够复杂。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'kgc@123';
--创建证书用来保护数据库加密密钥 (DEK)
CREATE CERTIFICATE master_server_certficate WITH
SUBJECT = N'Master Protect DEK Certificate';

创建主密钥和证书后，执行以下命令，检测密钥状态。

--查看master数据库主密钥状态
SELECT name,is_master_key_encrypted_by_server FROM
sys.databases;

执行结果，如图6.102所示。is_master_key_encrypted_by_server值为1，代表已创建主密钥。

图6.103 查看主密钥状态

执行以下命令，查看主密钥的详细信息。

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

执行结果如图6.61所示。

图6.61 查看主密钥的详细信息

2. 创建数据库和数据库加密密钥

首先创建测试数据库KGCDB,再创建由证书保护的数据库加密密钥（对称密钥）。

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

执行结果如图6.62所示。

图6.62 创建数据库和加密密钥

执行结果有一行警告信息。

警告: 用于对数据库加密密钥进行加密的证书尚未备份。应当立即备份该证书以及与该证书关联的私钥。如果该证书不可用，或者您必须在另一台服务器上还原或附加数据库，则必须对该证书和私钥均进行备份，否则将无法打开该数据库。

提示应该立即备份证书和私钥。

3.备份主密钥、证书和私钥

首先创建密钥保存目录，然后在查询分析器中执行如下命令，备份证书、私钥和master数据库的主密钥。

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'kgc@123';
--备份master系统数据库的CERTIFICATE
BACKUP CERTIFICATE master_server_certficate TO FILE = 'D:\SQL2012_TDE_Keys\master_server_certficate.cer'
WITH PRIVATE KEY (
FILE = 'D:\SQL2012_TDE_Keys\master_server_certficate.pvk' ,
ENCRYPTION BY PASSWORD = 'kgc@123');
--关闭数据库连接MASTER KEY
CLOSE MASTER KEY
GO 

USE master;
GO
--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'kgc@123';
--备份一下数据库主密钥(MASTER KEY)
BACKUP MASTER KEY TO FILE = 'D:\SQL2012_TDE_Keys\master.cer'
ENCRYPTION BY PASSWORD = 'kgc@123';
--关闭数据库连接MASTER KEY
CLOSE MASTER KEY
GO

执行备份完成后，会在密钥保存目录看到如图6.63所示三个文件：

图6.63备份证书、私钥和主密钥文件

4.执行TDE加密
在查询分析器中，执行如下命令对KGCDB数据库开启TDE加密。关于关闭TDE加密，详见6.64章节。

USE KGCDB
GO
--生产环境下，设置成单用户模式在运行加密
ALTER DATABASE KGCDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
--开启TDE 加密
ALTER DATABASE KGCDB SET ENCRYPTION ON;
GO
--设置多用户模式访问
ALTER DATABASE KGCDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
--再次开启TDE 加密
ALTER DATABASE KGCDB SET ENCRYPTION ON;
GO

完成TDE加密后，执行如下命令，来查看加密结果。

--查看KGCDB数据库加密结果
SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;

执行后结果如图6.64所示。

图6.64 KGCDB数据库加密状态

执行查询后显示，encryption_state值为3，代表已执行TDE加密。

encryption_state值得含义，如下：

encryption_state	注释
0	0 = No database encryption key present, no encryption (不存在数据库加密密钥，不加密)
1	1 = Unencrypted (未加密)
2	2 = Encryption in progress （正在加密）
3	3 = Encrypted （已加密）
4	4 = Key change in progress （正在进行的关键更改）
5	5 = Decryption in progress （正在解密）
6	6 = Protection change in progress (正在进行保护更改：正在更改加密数据库加密密钥的证书或非对称密钥)

5. 备份数据库

为完成后面得测试，先备份数据库。在服务器上创建一个备份文件存放目录，本案例是D:\SQL2012_Backup。然后执行如下命令。

USE master;
GO
--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'kgc@123';
BACKUP DATABASE KGCDB
TO DISK='D:\SQL2012_Backup\kgcdb.bak'
--关闭数据库连接MASTER KEY
CLOSE MASTER KEY

备份完成后，在D:\SQL2012_Backup目录下，可以看到kgcdb.bak备份文件。

1.2.2 还原数据库

首先模拟数据库备份文件被人非法获得，直接还原测试，提示异常。找不到服务器证书。这说明数据库的TDE加密是有效的，没有密钥，即使拿到数据库备份文件也将无法还原数据，无法读取数据内容。如图：6.65所示。

图6.65 数据库直接还原失败

作为数据库备份，当合法用户需要在另外一台服务器上还原数据库，需要执行如下步骤。

在“Microsoft SQL Server Managerment Studio”中，点击“新建查询”。如图6.66所示。

图6.66 新建查询

将前面备份的密钥复制到目标服务器的相应路径下。本案例中为C:\SQL2012_TDE_Keys目录下。然后在查询分析器中，执行如下命令：先还原master key，注意输入正确的PASSWORD。

USE master;
GO

--先在另外一台机器还原了MASTER KEY (该机器master数据库无master key)
RESTORE MASTER KEY
FROM FILE = 'C:\SQL2012_TDE_Keys\master.cer'
DECRYPTION BY PASSWORD = 'kgc@123'
ENCRYPTION BY PASSWORD = 'kgc@123';
GO

命令执行结果，如图6.67所示。

图6.67 还原MASTER KEY

再执行如下命令还原CERTIFICATE证书。在查询分析器中，执行如下命令：

use master;
go 

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'kgc@123';
--还原证书
CREATE CERTIFICATE master_server_certficate
FROM FILE = 'c:\SQL2012_TDE_Keys\master_server_certficate.cer'
WITH PRIVATE KEY (FILE = 'c:\SQL2012_TDE_Keys\master_server_certficate.pvk',
DECRYPTION BY PASSWORD = 'kgc@123');
GO
--关闭数据库连接MASTER KEY
CLOSE MASTER KEY 

接下来，执行以下命令，还原数据库。

USE master;
GO
--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'kgc@123';
--还原数据库，注意备份备份文件位置和数据库文件所在路径
RESTORE DATABASE kgcdb FROM DISK='c:\SQL2012_Backup\kgcdb.bak'
WITH MOVE 'kgcdb'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\kgcdb.mdf',
MOVE 'kgcdb_log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\kgcdb_log.ldf'
GO
--关闭数据库连接MASTER KEY
CLOSE MASTER KEY

完成以上操作，数据库被成功还原到另一台服务器上。

1.2.3 附加数据库

模拟数据库分离后，库文件被人非法获得，直接在另外一台服务器附加测试。

首先，将前面已经执行TDE加密的数据库KGCDB分离，然后把数据库文件KGCDB.mdf和KGCDB_log.ldf，复制到另一台安装有SQL2012的服务器上附加测试。执行附加操作，报错如图6.68所示，提示找不到证书，附加失败。

这说明，我们执行了TDE加密后，数据库文件得到了保护，在没有证书和密钥的情况下，即使拿到数据库文件也无法附加和读取数据。

图6.68 直接附加数据库失败

接下来演示作为合法用户，执行数据库分离再附加的操作。

如同本案例6.6.2章节所示，对于一个普通的数据库，首先要执行还原证书密钥等操作。

将前面备份的加密密钥复制到目标服务器的相应路径下。本案例中为C:\SQL2012_TDE_Keys目录下。然后在查询分析器中，执行如下命令：先还原master key，注意输入正确的PASSWORD。

USE master;
GO 
--先在另外一台机器还原了MASTER KEY (该机器master数据库无master key)
RESTORE MASTER KEY
FROM FILE = 'C:\SQL2012_TDE_Keys\master.cer'
DECRYPTION BY PASSWORD = 'kgc@123'
ENCRYPTION BY PASSWORD = 'kgc@123';
GO

再执行还原证书的操作。

执行如下命令还原CERTIFICATE证书。在查询分析器中，执行如下命令：

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'kgc@123';
--还原证书
CREATE CERTIFICATE master_server_certficate
FROM FILE = 'c:\SQL2012_TDE_Keys\master_server_certficate.cer'
WITH PRIVATE KEY (FILE = 'c:\SQL2012_TDE_Keys\master_server_certficate.pvk',
DECRYPTION BY PASSWORD = 'kgc@123');
GO
--关闭数据库连接MASTER KEY
CLOSE MASTER KEY

接下来，执行以下命令，附加数据库。

USE master;
GO

--打开数据库连接MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'kgc@123';
--附加数据库
CREATE DATABASE KGCDB
ON PRIMARY
(
FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\KGCDB.mdf'
)
LOG ON
(
FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\KGCDB_log.ldf'
)
FOR ATTACH ;
GO
--关闭数据库连接MASTER KEY
CLOSE MASTER KEY

执行以上操作完成后，刷新一下数据库目录，发现KGCDB数据库已成功完成附加。如图6.69所示。

图6.69 还原密钥和证书后附加数据库成功

1.2.4 补充配置

完成以上的配置后，还存在一个问题。

由于在新的数据库服务器上MASTER KEY以及证书是通过RESTORE语句还原得到的，并不是由当前SQL Server实例创建的，这将会导致新建的数据库，如果也要进行TDE加密时，无法完成。因此，在新的服务器上还原或附加TDE加密数据库成功后，还应该重建新的SQL Server服务器或实例的MASTER KEY和CERTIFICATE。按如下步骤执行操作：

1.首先关闭KGCDB数据库的TDE加密，在SQL Server的查询分析器中执行如下命令。

USE KGCDB
GO
--生产环境下，设置成单用户在运行加密
ALTER DATABASE KGCDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
--关闭TDE 加密
ALTER DATABASE KGCDB SET ENCRYPTION OFF;
GO
--设置多用户访问
ALTER DATABASE KGCDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
--再次关闭TDE 加密
ALTER DATABASE KGCDB SET ENCRYPTION OFF;
GO 

2.接下来删除数据库KGCDB的DEK 数据库加密密钥 （对称密钥）。执行如下命令：

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

3. 删除当前SQL Server中master系统数据库的MASTER KEY和CERTIFICATE，执行如下命令：

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

4. 重新创建master系统数据库的MASTER KEY（密钥）和CERTIFICATE（证书），执行如下命令。设置全新的PASSWORD，在生产环境中PASSWORD要足够复杂。

USE  master
GO 
--创建master数据库下的主数据库密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'kgc@6789';
GO 
--创建证书用来保护 数据库加密密钥 (DEK)
CREATE CERTIFICATE master_server_certficate WITH
SUBJECT = N'Master Protect DEK Certificate';
GO

5. 新的MASTER KEY（密钥）和CERTIFICATE（证书）创建完成后，执行以下命令检查测试。

USE master
GO 
--查看master数据库的MASTER KEY状态
SELECT name,is_master_key_encrypted_by_server FROM
sys.databases; 

执行结果，如图6.70所示：

图6.70 检测MASTER KEY状态

输出结果is_master_key_encrypted_by_server值为1，代表已创建密钥。如果不执行以上操作。KGCDB数据库还原或附加后，is_master_key_encrypted_by_server值为0，因此，对于新建的数据库执行TDE加密，将无法成功。

完成以上操作后，再执行6.7.1步骤，针对需要执行TDE加密的数据库，全部开启加密配置即可。详见6.7.1章节，不再重复演示。

1.2.5 注意事项

1. 完成MASTER KEY（密钥）和CERTIFICATE（证书）配置后，应立即进行导出备份，详见6.7.1章节。

2. 保障TDE加密的安全性，对导出密钥的管理是关键。应妥善保管好证书和密钥，不能和数据库放在同一台服务器上。
   ————————————————
   原文链接：https://blog.csdn.net/xiaohuixing16134/article/details/105710109

补充一下：解密后一定要

USE DATABASE
GO
DROP DATABASE ENCRYPTION KEY;
GO

否则 备份和附加在其他服务器需要证书