SQL Server 2019生产库无法找到对称键DataFileKey问题求助

问题背景 #

在SQL Server 2019 Enterprise环境中，已在Dev、Test、Production三个独立服务器实例部署数据库，且在三个实例中均拥有db_owner角色权限。为加密某表中一列数据，在每个实例中依次执行以下SQL语句创建主密钥、证书和对称键：

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '[password]'
GO

CREATE CERTIFICATE DataFileCert
   WITH SUBJECT = 'Data File Info';
GO

CREATE SYMMETRIC KEY DataFileKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE DataFileCert;
GO  

在Dev和Test实例中，执行以下打开对称键的语句可正常运行：

OPEN SYMMETRIC KEY DataFileKey
DECRYPTION BY CERTIFICATE DataFileCert;  

但在Production实例中执行该语句时，报错：

Cannot find the symmetric key 'DataFileKey', because it does not exist or you do not have permission.

已确认在对象资源管理器中能看到该证书和对称键，且可删除并重新创建，说明其存在且拥有相应权限。

可能原因及排查方法 #

• 数据库上下文不匹配：对称键是数据库级对象，若执行OPEN SYMMETRIC KEY时当前连接的数据库并非创建对象的目标库，就会出现找不到的错误。可执行SELECT DB_NAME()确认当前数据库，或在语句中显式指定数据库和架构：

  OPEN SYMMETRIC KEY YourTargetDB.dbo.DataFileKey
  DECRYPTION BY CERTIFICATE YourTargetDB.dbo.DataFileCert;
  

• 架构归属不一致：创建对象时未指定架构，Production实例中用户的默认架构可能与Dev/Test不同（比如默认不是dbo），导致对称键存在于非dbo架构下。可通过以下查询确认对象的架构归属：

  SELECT name, SCHEMA_NAME(schema_id) AS schema_name
  FROM sys.symmetric_keys WHERE name = 'DataFileKey';
  

  确认后在打开语句中指定架构：

  OPEN SYMMETRIC KEY [TargetSchema].DataFileKey
  DECRYPTION BY CERTIFICATE [TargetSchema].DataFileCert;
  

• 数据库主密钥加密状态异常：若Production实例中数据库主密钥仅用密码加密，未被服务主密钥加密，SQL Server重启后主密钥会处于未打开状态，无法解密证书进而无法打开对称键。可检查并修复：

  -- 检查主密钥是否被服务主密钥加密
  SELECT is_master_key_encrypted_by_server 
  FROM sys.databases WHERE name = DB_NAME();
  
  -- 若返回0，执行以下语句重新加密
  ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
  

• 隐性DENY权限覆盖：尽管拥有db_owner权限，但Production实例可能存在针对对称键或证书的DENY权限（DENY会优先于GRANT）。可查询权限配置：

  -- 检查对称键的DENY权限
  SELECT dp.state_desc, dp.permission_name, OBJECT_NAME(dp.major_id) AS object_name
  FROM sys.database_permissions dp
  JOIN sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
  WHERE dp.major_id = OBJECT_ID('DataFileKey') AND dp.state_desc = 'DENY';
  
  -- 检查证书的DENY权限
  SELECT dp.state_desc, dp.permission_name, OBJECT_NAME(dp.major_id) AS object_name
  FROM sys.database_permissions dp
  JOIN sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
  WHERE dp.major_id = OBJECT_ID('DataFileCert') AND dp.state_desc = 'DENY';
  

  若存在DENY权限，需执行REVOKE语句撤销。

• 排序规则大小写敏感差异：Production实例的数据库可能启用了区分大小写的排序规则（如SQL_Latin1_General_CP1_CS_AS），而Dev/Test使用不区分大小写的规则。若创建对象时的大小写与打开语句不一致，会导致找不到对象。可检查排序规则：

  SELECT collation_name FROM sys.databases WHERE name = DB_NAME();
  

  若为区分大小写，需保证打开语句中的对象名称与创建时完全一致。

内容的提问来源于stack exchange，提问作者Green Grasso Holm