SQL Server 2016生产库部署Always Encrypted遇Set-SqlColumnEncryption密钥错误求助

首先先明确你遇到的错误：

Set-SqlColumnEncryption : 使用密钥存储提供程序'MSSQL_CERTIFICATE_STORE'解密列加密密钥失败。加密列加密密钥的最后10个字节为：'xx-xx-xx-xx-xx-xx-xx-xx-xx-xx'。密钥集不存在...

UAT能成功但生产环境出问题，大概率是密钥同步、权限或者配置匹配的坑，下面是几个针对性的排查和修复步骤：

1. 确认列主密钥证书已正确部署到生产环境 #

UAT能正常解密，说明列主密钥证书在UAT的证书存储里，但生产环境的服务器（或者你执行Set-SqlColumnEncryption命令的客户端机器）里大概率没有这个证书，或者证书没包含私钥。

• 操作步骤：
  1. 从UAT导出证书：打开证书管理器（certmgr.msc或通过mmc添加证书Snap-in），找到对应列主密钥的证书，右键→所有任务→导出，选择是，导出私钥，保存为PFX格式并设置保护密码。
  2. 在生产环境导入证书：同样打开证书管理器，导入刚才的PFX文件，注意要导入到和UAT一致的存储位置（比如当前用户\个人或者本地计算机\个人，取决于你创建列主密钥时指定的路径）。
  3. 给执行命令的账号授权私钥访问：右键导入的证书→所有任务→管理私钥，添加执行Set-SqlColumnEncryption的账号，授予它读取权限。

2. 核对列主密钥的存储路径配置 #

有时候UAT和生产环境的列主密钥key_path定义不一致，导致找不到证书。

• 操作步骤：
  1. 在UAT和生产环境分别执行以下SQL查询，对比结果：

     SELECT name, key_store_provider_name, key_path FROM sys.column_master_keys
     

  2. 确保生产环境的key_path和UAT完全一致（比如CurrentUser/My/ABC123...这种指纹路径），如果不一致，需要重新创建列主密钥或者调整证书存储位置匹配这个路径。

3. 检查执行账号的权限 #

除了证书权限，数据库层面的权限也不能少：

• 确保执行命令的账号拥有：
  • ALTER ANY COLUMN ENCRYPTION KEY和ALTER ANY COLUMN MASTER KEY数据库权限
  • 目标表的ALTER权限
• 如果是在服务器上执行，还要确认账号有权限访问对应的证书存储（比如访问本地计算机存储需要管理员权限，或者已被授权）。

4. 验证列加密密钥（CEK）的一致性 #

如果生产环境的CEK是手动部署的，可能存在加密值不匹配的情况：

• 在UAT和生产环境分别执行：

  SELECT name, encrypted_value FROM sys.column_encryption_keys
  

• 对比两个环境的encrypted_value，如果不一致，说明生产环境的CEK没有用正确的列主密钥加密，需要重新在生产环境生成CEK，再关联到目标列。

5. 排查驱动和证书兼容性问题 #

MS SQL Server 2016的Always Encrypted对证书和驱动有一些限制：

• 确保你用的SqlServer PowerShell模块是最新版本：

  Update-Module -Name SqlServer
  

• 检查证书算法：Always Encrypted只支持RSA证书，避免使用ECDSA等其他算法的证书。
• 确认服务器的Cryptographic Services服务处于运行状态，这个服务负责证书的加密解密操作。

内容的提问来源于stack exchange，提问作者David I. McIntosh