SQLServer · 最佳实践 · 透明数据加密在SQLServer的应用

背景

作为云计算的服务提供者，我们在向用户提供优秀的服务能力时会遇到一个合规的问题。在数据库领域，数据是极其敏感和珍贵的，保护好数据，就如保护好企业的生命线。因此，需要采取一些预防措施来帮助保护数据库的安全，如设计一个安全系统、加密机密资产以及在数据库服务器的周围构建防火墙。但是，如果遇到物理介质被盗的情况，恶意破坏方只需还原或附加数据库即可浏览数据，或者遭遇拖库情况。一种解决方案是加密数据库中的敏感数据，并通过证书保护用于加密数据的密钥。这可以防止任何没有密钥的人使用这些数据，但这种保护必须事先计划。在SQL Server中，透明数据加密 (TDE) 可以加密 SQL Server数据文件，能够有效保护好我们的数据资产。

实现原理

数据库文件的加密在页级别执行。已加密数据库中的页在写入磁盘之前会进行加密，在读入内存时会进行解密，TDE 不会增加已加密数据库的大小。TDE 可对数据和日志文件执行实时 I/O 加密和解密。这种加密使用数据库加密密钥，该密钥存储在数据库引导记录中以供恢复时使用。数据库加密密钥是使用存储在服务器的 master 数据库中的证书保护的对称密钥，或者是由 EKM 模块保护的非对称密钥。TDE 保护“处于休眠状态”的数据，即数据和日志文件。体系如下:

实现方法

场景说明：
如果只是简单的配置一个加密数据库，如下步骤即可：创建主密钥，创建证书，创建DEK，应用加密，我们选取了一个比较复杂的场景，与数据库镜像的共存。同时镜像的创建是在非WINDOWS认证的安全策略，是通过证书的安全认证，这里涉及到master key等信息的特殊处理，证书的多样性。
下面列举实现的方法：首先，需要配置好用户数据库的镜像:

• 在principal: 创建master key
  先判断是否存在master key, 如果存在可以先DROP再创建，因为涉及到master key密码需要利用，老的master key不一定记得住密码:

USE master
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD='mypassword'
查询验证：
SELECT
	*
FROM sys.symmetric_keys
WHERE name='##MS_DatabaseMasterKey##'

• 在Principal:创建镜像使用的证书

USE master
GO
CREATE CERTIFICATE cer_db_mirror_principal
WITH SUBJECT='certification for mirror',
START_DATE='01/01/1999',
EXPIRY_DATE='12/31/2099';

查询验证：
SELECT
	*
FROM sys.certificates
WHERE name='cer_db_mirror_principal'

• 在principal: 创建镜像使用的端点

CREATE ENDPOINT endpoint_mirroring
STATE=STARTED
AS TCP (
	LISTENER_PORT=5022, LISTENER_IP=ALL )
FOR data_mirroring(
	AUTHENTICATION=CERTIFICATE cer_db_mirror_principal,
	ENCRYPTION= REQUIRED ALGORITHM RC4, ROLE=ALL
)
查询验证：
SELECT
	*
FROM sys.tcp_endpoints
WHERE name='endpoint_mirroring'

• 在principal: 备份master key

OPEN MASTER KEY
DECRYPTION BY PASSWORD = 'mypassword';
BACKUP MASTER KEY
TO FILE = 'D:\backup\master_key.mky'
ENCRYPTION BY PASSWORD = 'context'

• 在principal: 备份证书

BACKUP CERTIFICATE cer_db_mirror_principal
TO FILE='D:\backup\cer_db_mirror_principal.cer'

• 在mirror: 还原master key

RESTORE MASTER KEY
FROM FILE = 'D:\Backup\master_key.mky'
DECRYPTION BY PASSWORD = 'context'
ENCRYPTION BY PASSWORD = 'context';

查询验证：
SELECT
*
FROM sys.symmetric_keys
WHERE name='##MS_DatabaseMasterKey##'

• 在mirror: 创建证书

USE master
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'context'

CREATE CERTIFICATE cer_db_mirror_mirror
WITH SUBJECT='CERTIFICATION FOR MIRROR',
START_DATE='01/01/1999', EXPIRY_DATE='12/31/2099'
查询验证：

SELECT
*
FROM sys.certificates
WHERE name='cer_db_mirror_mirror'

• 在mirror: 创建端点

CREATE ENDPOINT endpoint_mirroring
STATE=STARTED
AS TCP (
	LISTENER_PORT=5022, LISTENER_IP=ALL )
FOR DATA_MIRRORING(
	AUTHENTICATION=CERTIFICATE cer_db_mirror_mirror,
	ENCRYPTION= REQUIRED ALGORITHM RC4, ROLE=ALL
)
查询验证：
SELECT *
FROM sys.tcp_endpoints
WHERE name='endpoint_mirroring'

• 在mirror: 备份镜像证书

BACKUP CERTIFICATE cer_db_mirror_mirror TO FILE='D:\Backup\cer_db_mirror_mirror.cer'

• 在principal: 创建端点的连接认证用户

USE master
GO
CREATE LOGIN mirror_for_login
WITH PASSWORD=N'22266320-AA49-4F52-A38E-98D5DE313B85'
GO
CREATE USER mirror_for_user
FOR LOGIN mirror_for_login

• 在principal: 创建镜像的证书，以打通相互成功握手通道

CREATE CERTIFICATE  cer_db_mirror_mirror
AUTHORIZATION mirror_for_user
FROM FILE='D:\Backup\cer_db_mirror_mirror.cer';

• 在principal: 为端点授权

GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO mirror_for_login;

• 在mirror: 创建端点的连接认证用户

USE master
GO
CREATE LOGIN principal_for_login
WITH PASSWORD=N'dd266320-AA4d-4R52-G38E-9DF5DE313B85'
GO
CREATE USER principal_for_user
FOR LOGIN principal_for_login

• 在mirror: 创建镜像的证书，以打通相互成功握手通道

CREATE CERTIFICATE cer_db_mirror_principal
AUTHORIZATION principal_for_user
FROM FILE='D:\Backup\cer_db_mirror_principal.cer'

• 在mirror: 为端点授权

GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO principal_for_login

• 在principal: 创建一个测试书库

CREATE DATABASE tde_mirror

• 在principal: 设置数据库的恢复模式为FULL，并备份数据库和日志

ALTER DATABASE tde_mirror
SET RECOVERY FULL

BACKUP DATABASE tde_mirror
TO DISK='D:\Backup\tde_mirror.bak'
WITH STATS=5,COMPRESSION

BACKUP LOG tde_mirror
TO DISK='D:\Backup\tde_mirror.trn'
WITH STATS=5,COMPRESSION

• 在mirror: 还原数据库

RESTORE DATABASE tde_mirror
FROM DISK='D:\Backup\tde_mirror.bak'
WITH STATS=5,NORECOVERY

RESTORE LOG tde_mirror
FROM DISK='D:\Backup\tde_mirror.trn'
WITH STATS=5,NORECOVERY

• 在mirror: 设置镜像

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'context'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
ALTER DATABASE  tde_mirror
SET PARTNER = 'TCP://10.0.0.1:5022'  --ip address or host name

• 在principal: 应用镜像

ALTER DATABASE  tde_mirror
SET PARTNER = 'TCP://10.0.0.2:5022' --ip address or host name

• 在principal: 创建TDE需要的证书

CREATE CERTIFICATE cer_tde
WITH SUBJECT='cert for tde',
START_DATE='01/01/1999',
EXPIRY_DATE='12/31/2099';

注意，创建完证书，数据库的同步状态可能会是SUSPEND，主备完成加密设置后才会恢复正常。

• 在principal: 在主库用户数据库创建DEK

USE tde_mirror
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128 ENCRYPTION
BY SERVER CERTIFICATE cer_tde

• 在principal: 设置数据库加密

USE master
GO
ALTER DATABASE  tde_mirror
SET ENCRYPTION ON

查询验证：这个状态应该是3
SELECT
		d.name,DEK.encryption_state
FROM sys.dm_database_encryption_keys dek
  INNER JOIN sys.databases d
		ON dek.database_id=d.database_id

• 在principal: 备份TDE证书

BACKUP CERTIFICATE cer_tde
TO FILE = 'D:\Backup\cer_tde.cer'
WITH PRIVATE KEY ( FILE = 'D:\Backup\cer_tde.pvk',
ENCRYPTION BY PASSWORD = 'tde_password');

• 在mirror: 创建证书

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'context'
CREATE CERTIFICATE cer_tde
FROM FILE = 'D:\Backup\cer_tde.cer'
WITH PRIVATE KEY (FILE = 'D:\Backup\cer_tde.pvk',
DECRYPTION BY PASSWORD = 'tde_password')

验证： 这个状态应该是1，做FAILOVER后才变成3
SELECT
	d.name,DEK.encryption_state
FROM sys.dm_database_encryption_keys dek
INNER JOIN sys.databases d
	ON dek.database_id=d.database_id

总结

当TDE和镜像共存时，很多步骤还是不一样，配置也多了许多步骤，可以看出有几个点特别注意：第一是master key的处理方式；第二是镜像的证书和TDE证书的区分；第三是镜像数据库TDE的状态变化。

还原数据库

启用了 TDE 的数据库的备份文件也使用数据库加密密钥进行加密。因此，当您还原这些备份时，用于保护数据库加密密钥的证书必须可用。也就是说，除了备份数据库之外，您还要确保自己保留了服务器证书的备份以防数据丢失。如果证书不再可用，将会导致数据丢失。还原数据库需要解密过程，restore database, resotore filelistonly等等，都需要先还原证书，因此备份证书和密钥是必须的。
示例：
- 首先备份TDE证书

BACKUP CERTIFICATE cer_tde
TO FILE = 'D:\Backup\cer_tde.cer'
WITH PRIVATE KEY ( FILE = 'D:\Backup\cer_tde.pvk',
ENCRYPTION BY PASSWORD = 'tde_password');

• 备份数据库

BACKUP DATABASE tde_mirror
TO DISK='d:\backup\tde_mirror_201605.bak'
WITH STATS=5,COMPRESSION

• 在需要还原的数据库上创建TDE证书

CREATE CERTIFICATE cer_tde
FROM FILE = 'D:\Backup\cer_tde.cer'
WITH PRIVATE KEY (FILE = 'D:\Backup\cer_tde.pvk',
DECRYPTION BY PASSWORD = 'tde_password')

• 还原数据库

RESTORE FILELISTONLY
FROM DISK='D:\backup\tde_mirror_201605.bak'
RESTORE DATABASE  tde_mirror
FROM DISK='D:\backup\tde_mirror_201605.bak'

透明数据库加密共存性

• 事务日志
  允许数据库使用 TDE 具有将虚拟事务日志的剩余部分“清零”以强制加密下一个虚拟事务日志的效果。这可以保证在数据库设置为加密后事务日志中不会留有明文。所有在数据库加密密钥更改前写入事务日志的数据都将使用之前的数据库加密密钥加密。在数据库加密密钥修改过两次后，必须执行日志备份才能再次对数据库加密密钥进行修改
• tempdb系统数据库
  如果 tempdb 实例中的任何用户数据库是使用 TDE 加密的，则会加密tempdb数据库。如果取消所有数据库加密状态，tempdb的加密数据库状态不会改变。
• 复制
  复制不会以加密形式从启用了 TDE 的数据库中自动复制数据。如果您想保护分发和订阅服务器数据库，则必须单独启用 TDE。快照复制以及用于事务和合并复制的初始数据分发，都能够在未加密的中间文件（例如 bcp 文件）中存储数据。 在事务或合并复制期间，可以启用加密来保护通信信道。
• 与FileStream数据
  即使启用了 TDE，也不会加密 FILESTREAM 数据。
• 内存中的OLTP
  可在拥有内存中 OLTP 对象的数据库上启用 TDE。如果启用 TDE，则内存中 OLTP 日志记录会被加密。如果启用了 TDE，则不对 MEMORY_OPTIMIZED_DATA 文件组中的数据进行加密。