MSSQL数据库加密与TDE加密后的数据库如何加入到可用性组(AVAILABILITY GROUP)

历史简介

SQL Server2005，引入了列级加密。使得加密可以对特定列执行，这个过程涉及4对加密和解密的内置函数。

SQL Server 2008时代，则引入的了透明数据加密（TDE），所谓的透明数据加密，就是加密在数据库中进行，但从程序的角度来看就好像没有加密一样，和列级加密不同的是，TDE加密的级别是整个数据库。使用TDE加密的数据库文件或备份在另一个没有证书的实例上是不能附加或恢复的。

单节点TDE加密

贴一张官方文档的图

TDE加密数据库加入可用性组

前提：

1、主节点证书和密钥的备份

2、备节点证书和密钥的恢复

后续：

1. 创建主数据库的日志备份。

2. 创建主数据库的完整数据库备份。

3. 在承载辅助副本的服务器实例上，还原数据库备份。

4. 从主数据库创建新的日志备份。

5. 在辅助数据库上还原此日志备份。

正文

如果数据库进行了加密或者数据库甚至包含数据库加密密钥 (DEK)，则您无法使用 新建可用性组向导 或 将数据库添加到可用性组向导 将该数据库添加到某一可用性组。 即使已对加密的数据库进行了解密，其日志备份也可能包含加密的数据。 在此情况下，在该数据库上完整的初始数据同步可能会失败。 其原因在于，还原日志操作可能要求数据库加密密钥 (DEK) 使用的证书，但该证书可能不可用。

1、主节点创建主密钥

要在test数据库上配置TDE，我们应该首先在master数据库中创建一个主密钥。每个SQL Server实例只能创建一个主密钥。托管在同一SQL实例中并启用了TDE的所有用户数据库将共享对同一主键的依赖关系。下面的CREATE MASTER KEY T-SQL语句用于在master数据库下创建主密钥，并且此主密钥将通过复杂的密码进行加密。最好将该密码备份保存在安全的地方：

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD='Q123456.q@'; GO

2、主节点创建加密证书

成功创建主密钥后，下一步是创建将用于加密数据库加密密钥的证书。证书将受到先前创建的主密钥的保护。可以使用下面的CREATE CERTIFICATE T-SQL语句创建证书：

CREATE CERTIFICATE ZHOUJJ WITH SUBJECT='Zhoujj'; GO

若要确保成功创建证书，可以查询sys.certificate系统对象以获取在当前SQL Server实例下创建的所有证书，如下所示：

SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date FROM sys.certificates;

 

3、主节点加密数据库

证书现已准备就绪，因此我们将在数据库端开始工作。我们将使用CREATE DATABASE ENCRYPTION KEY T-SQL命令创建test数据库加密密钥，该密钥由先前创建的ZHOUJJ证书加密，并指定加密算法，256位高级加密标准（AES）密钥长度,如下：

USE test GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE ZHOUJJ; GO

配置TDE的最后一步是使用以下ALTER DATABASE…SET ENCRYPTION ON T-SQL命令在test数据库上打开TDE加密：

ALTER DATABASE test SET ENCRYPTION ON; GO

这就对了！透明数据加密已在test数据库上完全配置。该sys.dm_database_encryption_keys DMV可以用来列出所有数据库上的数据库启用TDE：

SELECT DB_NAME(database_id) AS DatabaseName ,Encryption_State AS EncryptionState ,key_algorithm AS Algorithm ,key_length AS KeyLength FROM sys.dm_database_encryption_keys GO

结果显示test数据库上启用了TDE，并且数据库加密状态的值3表示数据库已完全加密。

4、主节点备份证书

作为最佳实践，最好在数据库上启用TDE之后备份TDE证书和与该证书关联的私钥。这样，您将能够还原数据库备份文件或将数据库数据文件附加到另一个SQL Server实例上。可以通过运行以下BACKUP CERTIFICATE T-SQL命令执行备份，该命令将备份证书本身及其私钥，并提供一个复杂的密码来加密该私钥，如下所示：

USE master GO BACKUP CERTIFICATE ZHOUJJ TO FILE = 'C:\test\ZHOUJJ' WITH PRIVATE KEY (file='C:\test\ZHOUJJ_Pirvate', ENCRYPTION BY PASSWORD='QWER@123.')

如果不提供以下路径，则可以通过浏览脚本中提供的路径或默认情况下SQL实例DATA路径来查看生成的备份文件：

此时，test数据库已加密，该数据库充当可用性组中的主要副本。

5、加密后的数据库加入到可用性组

加密后的数据库不能通过向导加入到可用性组，需要通过手动的方式加入。

5.1、备节点创建主密钥

为此，我们首先应该使用以下CREATE MASTER KEY T-SQL命令在通过复杂密码加密的辅助服务器上创建一个主密钥：

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Q123456.q@'

您可以选择对先前的主密钥使用相同的密码，也可以使用新的密码，因为主密钥用于加密证书而不是数据库本身以保护证书。在所有情况下，由于密钥生成过程的执行方式不同，密钥本身将有所不同。

5.2、备节点还原证书

下一步是将先前从DB41VS主副本生成的证书和私钥备份文件复制到DB42VS辅助服务器。您可以将其保留在辅助服务器上的默认数据文件夹中，或保留在应在CREATE CERTIFICATE T-SQL语句中提供该浴的自定义文件夹中。证书的私钥应使用创建备份时用于对其加密的相同密码解密：

文件地址file，是你复制到备节点的文件地址,这里是c:\test\ZHOUJJ

CREATE CERTIFICATE ZHOUJJ FROM FILE='c:\test\ZHOUJJ' WITH PRIVATE KEY ( FILE = 'c:\test\ZHOUJJ_Private', DECRYPTION BY PASSWORD='QWER@123.')

5.3、主节点数据库备份

现在，加密TDE_Test数据库的证书在辅助副本中可用。我们将从主副本中的TDE_Test数据库创建完整备份和事务日志备份，并将其复制到辅助副本中：

BACKUP DATABASE [test] TO  DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test.bak' WITH NOFORMAT, NOINIT,   NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO   BACKUP LOG [test] TO  DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test.trn' WITH NOFORMAT, NOINIT,   NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO

5.4、主节点数据库添加到可用性组

在主副本上，我们将使用ALTER AVAILABILITY GROUP…ADD DATABASE T-SQL命令将数据库添加到可用性组，如下所示：

USE master GO ALTER AVAILABILITY GROUP AG40VS ADD DATABASE [test]

 

5.5、备节点还原数据库备份

将备份文件复制到辅助服务器后，我们将使用WITH NORECOVERY选项在该服务器上还原完整备份和事务日志备份：

USE [master] RESTORE DATABASE [test] FROM  DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 GO   RESTORE LOG [test] FROM  DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test.trn' WITH  FILE = 1,   NORECOVERY,  NOUNLOAD,  STATS = 10 GO

这次还原过程将成功完成，因为在辅助服务器上创建了用于加密数据库加密密钥的相同证书。该数据库现在在辅助副本上可用，并且正在还原状态下等待。

5.6、备节点加数据库加入可用性组

在辅助副本端，我们需要使用下面的ALTER AVAILABILITY GROUP T-SQL命令将数据库加入可用性

USE master GO ALTER DATABASE test SET HADR AVAILABILITY GROUP = AG1;