如何在两台服务器上创建相同的数据加密对称密钥？

故事背景：

后台有小伙伴留言，follow前一篇

如何对SQL Server中的敏感数据进行加密解密？-CSDN博客

对测试数据库进行加密，但是发现数据加密解密操作只能在有加密证书和密钥的服务器进行。

当把加密数据同步到其他服务器后，无法在新服务器进行解密。

原因：这是因为在一个数据库中同时执行加密和解密时，密钥保存在数据库中可同时用于（取决于权限）加密和解密。 但在不同的数据库或服务器中执行加密和解密时，保存在一个数据库中的密钥不能用于另一个数据库。

那我们如何在两台服务器上创建相同的数据加密对称密钥呢？

---

如何在两个服务器上创建相同的数据加密对称密钥？

这里我们还是以对SQL Server的【Person】数据库中Employee表中的三个字段Name,email,phone加解密为例进行操作详解。

在第一台服务器数据库，遵循以下步骤和SQL语句示例进行加密设置：

1. 创建主密钥 (Master Key)：主密钥是加密其他密钥的密钥，它在数据库中是最高级别的密钥。

USE Person; -- 指定数据库
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';

2. 创建证书 (Certificate)：证书通常用于加密对称密钥，它包含公钥和私钥。

CREATE CERTIFICATE MyCert
   WITH SUBJECT = 'My Certificate for Encryption',
   EXPIRY_DATE = '20991231';

3. 创建对称密钥 (Symmetric Key)：使用证书加密对称密钥，然后使用对称密钥来加密数据。

重点来了：在创建对称密钥时，需要设置KEY_SOURCE、ALGORITHM 和 IDENTITY_VALUE三个值，他们是保证在不同服务器上创建相同的数据加密对称密钥的关键。

CREATE SYMMETRIC KEY [key_DataShare] WITH  
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',  
    ALGORITHM = AES_256,   
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'  
    ENCRYPTION BY CERTIFICATE [cert_keyProtection];  
GO  

4. 加密数据：使用对称密钥对数据进行加密。

假设我们有一个Employee表，我们想要加密Name, email, phone字段。首先，我们需要为对称密钥创建一个会话，并在这个会话中打开密钥：

OPEN SYMMETRIC KEY MySymmetricKey
   DECRYPTION BY CERTIFICATE MyCert;

然后，我们可以使用ENCRYPTBYKEY函数来加密数据：

UPDATE Employee
SET Name = ENCRYPTBYKEY(Key_GUID('MySymmetricKey'), Name),
    email = ENCRYPTBYKEY(Key_GUID('MySymmetricKey'), email),
    phone = ENCRYPTBYKEY(Key_GUID('MySymmetricKey'), phone);

再完成对加密数据操作后，需要关闭这个对称密钥的会话：

CLOSE SYMMETRIC KEY MySymmetricKey

这样就完成第一台服务器数据库的【Person】数据库中Employee表中的三个字段Name,email,phone的加密工作。

5. 在第二台服务器数据库创建相同的证书，密钥；

直接使用步骤1，2，3的代码创建即可。

---

如何给不同服务器加密的字段进行解密查询呢？

首先，将加密数据同步到第二台服务器的数据库表中。

1. 在第一台服务器上使用对称密钥进行解密数据测试：

打开对称密钥：

OPEN SYMMETRIC KEY MySymmetricKey
   DECRYPTION BY CERTIFICATE MyCert;

然后我们使用DECRYPTBYKEY函数解密数据：

SELECT 
    CONVERT(NVARCHAR, DECRYPTBYKEY(NAME_ENCRYPTED_FIELD)) as Name,
    CONVERT(NVARCHAR, DECRYPTBYKEY(EMAIL_ENCRYPTED_FIELD)) as email,
    CONVERT(NVARCHAR, DECRYPTBYKEY(PHONE_ENCRYPTED_FIELD)) as phone
FROM Employee;

这样你就可以查询到Employee表中的三个字段Name,email,phone解密后的数据了，操作非常简单。

2. 在另外一台服务器上，使用相同的操作步骤进行解密测试：

打开对称密钥，然后使用DECRYPTBYKEY函数解密数据，你会发现同步给第二胎服务器的加密数据也可以正常解密了。

3. 测试完成后，在两个服务器的数据库上关闭对称密钥：

CLOSE SYMMETRIC KEY MySymmetricKey

到这里，在两台服务器上创建相同的数据加密对称密钥的配置工作就完成了。

---

问题补充：

1. 服务器数量不限于两台，如果有多台服务器也可使用相同步骤操作。

2. SQL Server 2016 使用 SHA1 哈希算法进行加密工作。

从 SQL Server 2017（CU2） 开始，改用 SHA2。 这意味着如果是初始加密服务器是2016， 被同步的服务器是2017及以上版本，可能需要额外的步骤。

    额外步骤如下：

        需要先开始追踪标志4631;

DBCC TRACEON(4631, -1);

       然后再进行创建密钥，证书的操作;

       完成后，关闭追踪标志4631：

DBCC TRACEOFF(4631, -1);

请注意，上述步骤中的YourStrongPassword1应替换为一个强密码，以确保安全性。同时，确保在实际应用中，加密和解密操作符合组织的安全政策和最佳实践。

在执行上述操作时，确保您有足够的权限来创建密钥和证书，并且了解SQL Server的加密机制。根据实际需求，还可以根据需要考虑备份和还原加密密钥，在不同的数据库或服务器之间迁移证书和密钥。

今天分享就到这里，想要了解更多数据处理的技巧，记得关注我哦！