SQL Server 2019企业版HA故障转移后主密钥重复配置如何解决

问题本质是SQL Server实例级的服务主密钥（SMK）在集群各节点不统一导致的：数据库主密钥（DMK）默认会被当前实例的SMK加密实现自动解密，而每个独立安装的SQL Server实例都会生成唯一的SMK，HA集群各节点的SMK不一致时，故障转移到新节点后，新节点无法解密原有实例加密的DMK，导致加密对象无法访问，必须手动操作DMK才能恢复。
你之前使用的删除并重建主密钥、证书的脚本风险极高，会直接导致所有使用原有密钥加密的历史数据无法解密，仅适用于测试环境或者加密数据可以完全重建的场景，生产环境禁止使用该脚本。

方案1：同步所有集群节点的服务主密钥（推荐，无业务侵入） #

该方案可以从根源解决节点间密钥不兼容问题，无需修改业务代码：

• 第一步：在当前主节点实例上备份SMK，运行如下脚本：

BACKUP SERVICE MASTER KEY TO FILE = '集群所有节点可访问的共享路径\SQL_CLUSTER_SMK.bak'
ENCRYPTION BY PASSWORD = '自定义强密码，务必妥善保管';

• 第二步：依次登录HA集群内的所有辅助节点实例，运行如下脚本覆盖本地SMK：

RESTORE SERVICE MASTER KEY FROM FILE = '集群所有节点可访问的共享路径\SQL_CLUSTER_SMK.bak'
DECRYPTION BY PASSWORD = '上一步设置的SMK备份密码'
FORCE;

注：执行FORCE参数会强制覆盖当前节点原有SMK，操作前建议先对各节点原有SMK单独备份，避免异常。

• 第三步：在当前主节点执行如下脚本，确保DMK被统一后的SMK加密：

USE [MYDATABASE]
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'My_encryption_key';
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
GO

• 第四步：手动触发一次故障转移测试，验证新主节点无需运行任何脚本即可正常访问加密对象。

方案2：移除DMK的SMK加密（仅适合特定场景） #

如果你不希望同步集群内的SMK，可以选择关闭DMK的自动解密机制，该方案需要修改业务代码：

• 运行如下脚本调整DMK配置：

USE [MYDATABASE]
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'My_encryption_key';
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
GO

注：该方案生效后，所有访问加密对称密钥/证书的业务逻辑，都必须先显式执行OPEN MASTER KEY DECRYPTION BY PASSWORD = 'My_encryption_key'打开主密钥，操作完成后再执行CLOSE MASTER KEY关闭，对业务代码有侵入性，普通场景不推荐使用。

操作前置注意事项 #

• 所有操作前务必对服务主密钥、数据库主密钥、证书、对称密钥分别做独立离线备份，存放在安全位置，避免密钥丢失导致加密数据完全无法恢复
• 生产环境操作建议先在同配置测试集群验证完整流程，确认无误后再执行生产操作
• 同步SMK的操作不需要重启实例，不会影响现有业务运行

内容的提问来源于stack exchange，提问作者Jose Barreto