如何在SQL Server外实现ENCRYPTBYPASSPHRASE的密钥派生逻辑？

SQL Server 2017及更高版本中，ENCRYPTBYPASSPHRASE的密钥派生和加密逻辑可复现，核心参数与规则如下：

密钥派生核心参数 #

• 算法：使用PBKDF2进行密钥派生
• 哈希算法：SHA-1
• 迭代次数：固定为1000次
• 盐值规则：
  • 若调用时未指定验证器（add_authenticator参数为0或省略），盐值为空字节数组
  • 若指定add_authenticator=1并传入验证器值，盐值为该验证器的SHA-1哈希结果（验证器需以Unicode编码处理）
• 派生密钥长度：16字节（对应AES-128加密）

加密核心规则 #

• 加密算法：AES-128
• 加密模式：CBC
• 填充方式：PKCS7
• IV：随机生成16字节IV，且IV会直接前置在最终密文的开头（SQL Server解密时依赖此格式）

外部实现示例（C#） #

以下代码可生成与ENCRYPTBYPASSPHRASE完全兼容的加密数据：

using System;
using System.Security.Cryptography;
using System.Text;

public class SqlPassphraseEncryption
{
    public static byte[] Encrypt(string passphrase, string plainText, bool useAuthenticator = false, string authenticator = null)
    {
        byte[] salt;
        // 处理盐值
        if (useAuthenticator)
        {
            if (string.IsNullOrWhiteSpace(authenticator))
                throw new ArgumentException("验证器不能为空", nameof(authenticator));
            
            using var sha1 = SHA1.Create();
            salt = sha1.ComputeHash(Encoding.Unicode.GetBytes(authenticator));
        }
        else
        {
            salt = Array.Empty<byte>();
        }

        // 派生AES密钥
        using var pbkdf2 = new Rfc2898DeriveBytes(
            Encoding.Unicode.GetBytes(passphrase),
            salt,
            1000,
            HashAlgorithmName.SHA1);
        byte[] aesKey = pbkdf2.GetBytes(16);

        // 生成随机IV
        byte[] iv = new byte[16];
        using var rng = RandomNumberGenerator.Create();
        rng.GetBytes(iv);

        // 执行加密
        using var aes = Aes.Create();
        aes.Key = aesKey;
        aes.IV = iv;
        aes.Mode = CipherMode.CBC;
        aes.Padding = PaddingMode.PKCS7;

        using var ms = new System.IO.MemoryStream();
        // 先写入IV
        ms.Write(iv, 0, iv.Length);
        using var encryptor = aes.CreateEncryptor();
        using var cs = new CryptoStream(ms, encryptor, CryptoStreamMode.Write);
        
        byte[] plainBytes = Encoding.Unicode.GetBytes(plainText);
        cs.Write(plainBytes, 0, plainBytes.Length);
        cs.FlushFinalBlock();

        return ms.ToArray();
    }
}

验证解密 #

将生成的字节数组存入SQL Server的varbinary字段后，可直接用DECRYPTBYPASSPHRASE解密：

• 未使用验证器的情况：

SELECT CAST(DECRYPTBYPASSPHRASE(N'MyPassphrase', EncryptedData) AS NVARCHAR(MAX)) AS DecryptedText
FROM YourTable;

• 使用验证器的情况：

SELECT CAST(DECRYPTBYPASSPHRASE(N'MyPassphrase', EncryptedData, 1, N'MyAuthenticator') AS NVARCHAR(MAX)) AS DecryptedText
FROM YourTable;

关键注意事项 #

• 所有字符串（密码短语、明文、验证器）必须以Unicode编码处理，对应SQL Server的NVARCHAR类型
• 迭代次数、哈希算法、盐值规则必须严格匹配，否则密钥派生错误无法解密
• IV必须随机生成并前置在密文前，这是SQL Server解密的格式要求

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