使用AlwaysOn集群进行加密

Question

我有一个数据库，它从旧的SQL Server 2008R2实例移动到了SQL Server 2012 AlwaysOn集群。数据库中有几个字段使用SQL服务器内置的加密功能进行了加密（主密钥、证书、对称密钥）。

我在我的QA AO实例上运行了以下命令（与旧服务器上运行的相同步骤）：

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

 CREATE CERTIFICATE myCert  
    WITH SUBJECT = 'password'

 CREATE SYMMETRIC KEY myKeyName    
    WITH ALGORITHM = TRIPLE_DES 
    ENCRYPTION BY CERTIFICATE myCert 

另外，我还需要运行以下命令才能正确解密数据：

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

当我运行这个命令时，我随后可以看到所有已解密的数据：

OPEN SYMMETRIC KEY myKeyName
DECRYPTION BY CERTIFICATE myCert 
select TOP 1000 userid, CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) from  users
CLOSE SYMMETRIC KEY myKeyName

到目前为止都很好。然而，如果我在生产AO集群上运行相同的步骤，则该查询：

select TOP 1000 userid, CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) from  users

返回密码的值为NULL。更让人感到疯狂的是，这个语句（在QA环境中运行）可以很好地解密来自两个数据库的所有内容：

 OPEN SYMMETRIC KEY myKeyName
 DECRYPTION BY CERTIFICATE myCert 

 SELECT TOP 1000 
    userid, 
    CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) 
 FROM users

 SELECT TOP 1000 
    userid, 
    CONVERT(nVARCHAR(255),DECRYPTBYKEY(password))  
 FROM PRODUCTIONAO.prod_database.dbo.users

 CLOSE SYMMETRIC KEY myKeyName

我不确定为什么这在我的QA实例上可以工作，但在我的生产实例上不能。任何帮助将不胜感激！

- dparsons

---

你的QA盒子和生产盒子有什么区别？它们是否在同一硬件上？相同的操作系统？相同的服务包等等？ - Charles Farr

它们是不同的AO集群，但每个集群中的两台机器是相同的。这些机器是虚拟的。 - dparsons

为您添加了另一个答案。 - Charles Farr

Answer 1

你上一次查询成功的原因是由于你使用QA实例的密钥/证书来解密生产数据。在QA中，您可以使用数据库主密钥（DMK）自动解密证书，因为它是由QA服务主密钥（SMK）加密的，如下所示：

服务主密钥（QA）
  数据库主密钥（QA）
    证书（QA）
      对称密钥（QA）
        数据（Prod）

在生产环境中，您有一个不同的SMK，因此打开DMK的唯一选择是使用密码。看起来你在QA环境中运行了以下命令，但在生产环境中没有运行：

/* Add service master key encryption to the database master key */
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

尝试在生产环境中执行以下操作：

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
OPEN SYMMETRIC KEY myKeyName
DECRYPTION BY CERTIFICATE myCert 
select TOP 1000 userid, CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) from users
CLOSE SYMMETRIC KEY myKeyName

如果返回数据，则需要在生产环境中向DMK添加SMK加密（第一个脚本）。另一个选择是从源实例备份SMK并在辅助实例上还原。我只建议在HA用例中使用此选项，其中实例是故障转移伙伴，并且两个实例都在同一环境中。在QA和prod之间共享SMK是不好的做法。

- Ben Campbell

Answer 2

创建数据库主密钥时，服务器会保存两个版本的密钥。一个版本由主服务密钥加密，默认情况下由服务器使用。第二个版本是由您在创建数据库主密钥时向服务器提供密码所加密的。通常不会使用此版本。将数据库移动到不同环境（您的生产环境）时，新的服务器具有不同的主服务密钥。由于它不是用于加密数据库主密钥的服务密钥，因此也无法用于打开数据库的主密钥。这就是您应该使用用密码加密的版本的地方。您需要使用密码打开主密钥，然后使用新的服务密钥对其进行加密并关闭。完成后，数据库的主密钥可以与主服务密钥一起工作，因此您不需要再次执行此操作。

步骤/代码:

打开主密钥解密 by password = '在此处输入您的原始密码'

alter master key add encryption by service master key

关闭主密钥

- Charles Farr