SQL Server存储过程解密加密列返回NULL问题解决

在SQL Server 2016环境中，已创建对称密钥SymKey_Test，并通过证书Certificate_Test加密ClientContactTest表的PhoneNo列（加密后存储为PhoneNo_Encrypt）。直接执行SQL语句可正常解密该列，但将相同代码封装到存储过程ClientContactTest_LoadRecord_Encrypted后，解密后的PhoneNo列返回NULL。

已完成的配置：

• 存储过程已授予CRMObjects角色执行权限
• 尝试为CRMObjects角色授予证书和对称密钥的CONTROL、VIEW DEFINITION权限，但问题未解决

直接执行的SQL代码 #

OPEN SYMMETRIC KEY SymKey_Test DECRYPTION BY CERTIFICATE Certificate_Test;

SELECT        
    ClientContactTest.ClientID, ClientContactTest.ContactID, 
    ClientContactTest.Title, ClientContactTest.Forename, 
    ClientContactTest.Surname, 
    CONVERT(varchar, DecryptByKey(ClientContactTest.PhoneNo_Encrypt)) AS PhoneNo,  
    ClientContactTest.MobileNo, ClientContactTest.EMailAddress, 
    Lookup_ContactType.Description AS ContactTypeDescription
FROM
    ClientContactTest 
LEFT OUTER JOIN
    Lookup_ContactType ON ClientContactTest.ContactTypeID = Lookup_ContactType.ContactTypeID
WHERE
    (ClientContactTest.ClientID = 7) 
    AND (ClientContactTest.SiteID = 0) 
    AND (ClientContactTest.ContactID = 1)

CLOSE SYMMETRIC KEY SymKey_Test

存储过程代码 #

CREATE PROCEDURE [dbo].[ClientContactTest_LoadRecord_Encrypted]
AS
BEGIN
    OPEN SYMMETRIC KEY SymKey_Test DECRYPTION BY CERTIFICATE Certificate_Test;

    SELECT        
        ClientContactTest.ClientID, ClientContactTest.ContactID, 
        ClientContactTest.Title, ClientContactTest.Forename, 
        ClientContactTest.Surname, 
        CONVERT(varchar, DecryptByKey(ClientContactTest.PhoneNo_Encrypt)) AS PhoneNo, 
        ClientContactTest.MobileNo, ClientContactTest.EMailAddress, 
        Lookup_ContactType.Description AS ContactTypeDescription
    FROM
        ClientContactTest 
    LEFT OUTER JOIN
        Lookup_ContactType ON ClientContactTest.ContactTypeID = Lookup_ContactType.ContactTypeID
    WHERE
        (ClientContactTest.ClientID = 7) 
        AND (ClientContactTest.SiteID = 0) 
        AND (ClientContactTest.ContactID = 1)

    CLOSE SYMMETRIC KEY SymKey_Test
END

已尝试的权限配置 #

GRANT CONTROL ON CERTIFICATE :: Certificate_Test TO CRMObjects;
GRANT CONTROL ON SYMMETRIC KEY :: SymKey_Test TO CRMObjects

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymKey_Test TO CRMObjects
GRANT VIEW DEFINITION ON Certificate::[Certificate_Test] TO CRMObjects

1. 补充必要权限 #

存储过程执行时，CRMObjects角色需要具备证书的REFERENCES权限，以及对称密钥的REFERENCES权限（仅CONTROL和VIEW DEFINITION不足以支持解密操作）。执行以下权限授予语句：

-- 授予证书的REFERENCES权限
GRANT REFERENCES ON CERTIFICATE::Certificate_Test TO CRMObjects;
-- 授予对称密钥的REFERENCES权限
GRANT REFERENCES ON SYMMETRIC KEY::SymKey_Test TO CRMObjects;

2. 配置存储过程执行上下文（可选但推荐） #

如果证书和对称密钥的所有者是dbo，而存储过程也是dbo所有，可以通过EXECUTE AS OWNER让存储过程以所有者身份执行，继承所有者的权限，避免权限上下文问题。修改存储过程：

ALTER PROCEDURE [dbo].[ClientContactTest_LoadRecord_Encrypted]
WITH EXECUTE AS OWNER
AS
BEGIN
    OPEN SYMMETRIC KEY SymKey_Test DECRYPTION BY CERTIFICATE Certificate_Test;

    SELECT        
        ClientContactTest.ClientID, ClientContactTest.ContactID, 
        ClientContactTest.Title, ClientContactTest.Forename, 
        ClientContactTest.Surname, 
        CONVERT(varchar, DecryptByKey(ClientContactTest.PhoneNo_Encrypt)) AS PhoneNo, 
        ClientContactTest.MobileNo, ClientContactTest.EMailAddress, 
        Lookup_ContactType.Description AS ContactTypeDescription
    FROM
        ClientContactTest 
    LEFT OUTER JOIN
        Lookup_ContactType ON ClientContactTest.ContactTypeID = Lookup_ContactType.ContactTypeID
    WHERE
        (ClientContactTest.ClientID = 7) 
        AND (ClientContactTest.SiteID = 0) 
        AND (ClientContactTest.ContactID = 1)

    CLOSE SYMMETRIC KEY SymKey_Test
END

3. 验证权限生效 #

执行以下SQL检查CRMObjects角色的权限是否正确配置：

SELECT 
    dp.name AS principal_name,
    perm.class_desc AS securable_type,
    COALESCE(sec.name, sk.name) AS securable_name,
    perm.permission_name
FROM sys.database_permissions perm
JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
LEFT JOIN sys.certificates sec ON perm.class = 1 AND perm.major_id = sec.certificate_id
LEFT JOIN sys.symmetric_keys sk ON perm.class = 2 AND perm.major_id = sk.symmetric_key_id
WHERE dp.name = 'CRMObjects'
AND perm.class_desc IN ('CERTIFICATE', 'SYMMETRIC_KEY');

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