数据库内核月报－ 2016/05 - SQLServer · 最佳实践 · 透明数据加密在SQLServer的应用 - 《数据库内核月报》

实现方法
还原数据库

实现原理

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

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


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'
查询验证：
*
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: 创建端点的连接认证用户

在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: 设置镜像

在principal: 应用镜像

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证书

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证书

还原数据库

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 数据。