SQL Server数据库TDE加密
1.1 案例环境介绍
完成本节数据TDE加密的演示,需要准备两台虚拟机环境,一台模拟生产数据库,一台模拟迁移还原的数据库环境。
1.1.1 案例环境配置
完成本章案例数据备份部分的学习,需要准备3个节点的环境。具体环境配置要求,见表6-3。
表6-3 本案例环境配置
序号
角色
IP地址
操作系统
安装软件
备注
1
生产数据库
192.168.0.131
Windows Server2016
Datacenter版
SQL 2012企业版SP1
2
还原数据库
192.168.0.133
Windows Server2012R2
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 查看主密钥的详细信息
- 创建数据库和数据库加密密钥
首先创建测试数据库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值得含义,如下:
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 (正在进行保护更改:正在更改加密数据库加密密钥的证书或非对称密钥)
- 备份数据库
为完成后面得测试,先备份数据库。在服务器上创建一个备份文件存放目录,本案例是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
- 删除当前SQL Server中master系统数据库的MASTER KEY和CERTIFICATE,执行如下命令:
USE master
GO
--如果创建后要删除master数据库下的证书,可以使用下面的语句
DROP CERTIFICATE master_server_certficate
GO
--如果创建后要删除master数据库下的主数据库密钥,可以使用下面的语句
DROP MASTER KEY
GO
- 重新创建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
- 新的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 注意事项
-
完成MASTER KEY(密钥)和CERTIFICATE(证书)配置后,应立即进行导出备份,详见6.7.1章节。
-
保障TDE加密的安全性,对导出密钥的管理是关键。应妥善保管好证书和密钥,不能和数据库放在同一台服务器上。
————————————————
原文链接:https://blog.csdn.net/xiaohuixing16134/article/details/105710109