SQL Server：创建视图并利用证书加密字段及证书创建报错求助

一、解决CREATE CERTIFICATE报错问题 #

你遇到的15208错误，常见原因和解决方法如下：

• 权限问题：SQL Server的服务账户（而非你的本地用户账户）没有访问C:\Users\Desktop目录的权限。解决办法：
  1. 打开Windows服务，找到你的SQL Server服务，查看其登录身份（通常是NT SERVICE\MSSQLSERVER或自定义账户）。
  2. 给该账户授予证书文件所在目录的读取权限。
  3. 或者将证书文件移动到SQL Server默认有权限的目录，比如C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\DATA（XX是你的SQL版本号）。
• 文件有效性问题：确认EncryptionGDPRCert.cer是有效的X.509证书，没有损坏。可以双击文件用Windows证书管理器打开验证。
• 替代方案（推荐测试/内部场景）：直接在SQL Server中创建自签名证书，无需外部文件，步骤更简单：

  CREATE CERTIFICATE ID_cert
  WITH SUBJECT = 'GDPR加密证书',
  EXPIRY_DATE = '2030-12-31';
  

二、在视图中用证书加密EncrptID列 #

拥有有效证书后，使用ENCRYPTBYCERT函数对EncrptID（原lee列）进行加密，注意需将列转换为字符串类型（ENCRYPTBYCERT仅接受字符输入）。修改后的视图代码如下：

CREATE OR ALTER VIEW [dbo].[Taxid_vw] 
AS
    SELECT 
        [AccountID], 
        [Property],
        [TimeSet],
        -- 使用证书加密lee列，转换为VARCHAR避免类型不兼容
        ENCRYPTBYCERT(CERT_ID('ID_cert'), CAST([lee] AS VARCHAR(MAX))) AS EncrptID,
        HASHBYTES('SHA2_512', UPPER([Value])) AS HASHV
    FROM 
        [kdo].[Property] q
    WHERE
        q.[Property] = 'IdNumber';

解密说明 #

加密后的EncrptID是二进制类型，如需查看明文，可使用DECRYPTBYCERT函数（需持有证书私钥权限）：

SELECT 
    AccountID,
    -- 解密时转换回原数据类型
    CAST(DECRYPTBYCERT(CERT_ID('ID_cert'), EncrptID) AS VARCHAR(MAX)) AS Decrypted_EncrptID,
    HASHV
FROM [dbo].[Taxid_vw];

其他证书加密方案 #

如果需要更严谨的密钥管理，可结合非对称密钥+证书的层级结构：

1. 创建主密钥（若未创建）：

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
   

2. 创建证书：

   CREATE CERTIFICATE ID_cert
   WITH SUBJECT = 'GDPR加密证书',
   EXPIRY_DATE = '2030-12-31';
   

3. 创建非对称密钥并绑定证书：

   CREATE ASYMMETRIC KEY ID_asym_key
   WITH ALGORITHM = RSA_2048
   ENCRYPTION BY CERTIFICATE ID_cert;
   

4. 用非对称密钥加密列（视图中替换ENCRYPTBYCERT为ENCRYPTBYASYMKEY）：

   ENCRYPTBYASYMKEY(ASYMKEY_ID('ID_asym_key'), CAST([lee] AS VARCHAR(MAX))) AS EncrptID
   

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