SQL Server视图对称密钥加密列跨实例返回NULL，如何保持永久加密？

核心原因 #

你当前的视图是在查询时动态执行加密操作，而ENCRYPTBYKEY必须依赖当前会话中对称密钥处于打开状态才能正常工作。当密钥未打开（或跨实例密钥不一致）时，加密操作会失败，返回NULL。

可行解决方案 #

1. 提前在数据写入阶段完成加密（推荐）

不要在视图里做加密，而是在数据插入/更新时就把明文加密后存入表中，视图仅读取已加密的数据，彻底摆脱对会话密钥状态的依赖。

可以通过触发器实现自动加密：

-- 创建触发器，在插入/更新Property表时自动加密Value列
CREATE TRIGGER trg_Property_EncryptValue
ON [extacrt].[dbo].[Property]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 打开对称密钥（需替换为你的解密凭据，比如证书）
    OPEN SYMMETRIC KEY EncryptionKey
    DECRYPTION BY CERTIFICATE YourEncryptionCert;

    -- 处理插入操作
    INSERT INTO [extacrt].[dbo].[Property] (AccountName, Value)
    SELECT AccountName, ENCRYPTBYKEY(KEY_GUID('EncryptionKey'), CAST(Value AS VARCHAR))
    FROM inserted;

    -- 处理更新操作（替换PrimaryKeyColumn为表的实际主键）
    UPDATE p
    SET p.Value = ENCRYPTBYKEY(KEY_GUID('EncryptionKey'), CAST(i.Value AS VARCHAR))
    FROM [extacrt].[dbo].[Property] p
    JOIN inserted i ON p.PrimaryKeyColumn = i.PrimaryKeyColumn;

    -- 关闭密钥
    CLOSE SYMMETRIC KEY EncryptionKey;
END;
GO

之后视图只需直接读取加密后的列：

CREATE VIEW [abc].[Taxid_vw] AS
SELECT AccountName, Value AS encrypted
FROM [extacrt].[dbo].[Property];
GO

2. 用存储过程封装查询，自动管理密钥状态

如果必须在视图中保留加密逻辑，可创建存储过程，在查询视图前自动打开密钥，查询后关闭：

CREATE PROCEDURE [abc].[GetTaxidData]
AS
BEGIN
    SET NOCOUNT ON;
    -- 打开对称密钥（替换为你的解密凭据）
    OPEN SYMMETRIC KEY EncryptionKey
    DECRYPTION BY CERTIFICATE YourEncryptionCert;

    -- 查询视图
    SELECT * FROM [abc].[Taxid_vw];

    -- 关闭密钥
    CLOSE SYMMETRIC KEY EncryptionKey;
END;
GO

后续通过调用EXEC [abc].[GetTaxidData]获取数据，而非直接查询视图。

3. 跨实例同步密钥环境

如果是在另一个SQL Server实例运行视图，需确保目标实例存在与原实例完全一致的对称密钥和加密凭据（如证书）：

1. 在原实例备份证书及私钥：

BACKUP CERTIFICATE YourEncryptionCert
TO FILE = 'D:\Backup\YourEncryptionCert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\Backup\YourEncryptionCert.pvk',
    ENCRYPTION BY PASSWORD = 'YourStrongPassword'
);
GO

2. 将证书和私钥文件复制到目标实例，然后还原：

CREATE CERTIFICATE YourEncryptionCert
FROM FILE = 'D:\Backup\YourEncryptionCert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\Backup\YourEncryptionCert.pvk',
    DECRYPTION BY PASSWORD = 'YourStrongPassword'
);
GO

-- 创建与原实例同名的对称密钥
CREATE SYMMETRIC KEY EncryptionKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE YourEncryptionCert;
GO

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