如何在SQL Server的SAFE权限CLR程序集中计算SHA256哈希？

问题背景 #

我们开发的.NET CLR函数依赖System.Security.Cryptography.SHA256计算SHA256哈希，原本以WITH PERMISSION_SET=SAFE部署到SQL Server可正常运行，但安装2024年5月的.NET Framework更新后，调用时触发MayLeakOnAbort权限限制报错。将程序集改为UNSAFE权限虽能解决问题，但会带来服务器可靠性与安全风险，因此需要在SAFE权限下找到替代方案。

可行解决方案 #

核心思路是使用不依赖.NET Framework内置SHA256类的纯托管SHA256实现——这类实现不包含MayLeakOnAbort属性，无需UNSAFE权限即可在SQL Server CLR环境中运行。

方案1：使用BouncyCastle纯托管库 #

BouncyCastle是免费开源的密码学库，其纯托管版本无任何非托管调用，可在SAFE权限下部署。

步骤：

1. 在你的.NET CLR项目中添加BouncyCastle Core NuGet包（Org.BouncyCastle）
2. 编写调用BouncyCastle SHA256实现的CLR函数：

using Org.BouncyCastle.Crypto.Digests;
using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBinary CalculateSHA256(SqlString input)
    {
        if (input.IsNull)
            return SqlBinary.Null;

        byte[] inputBytes = System.Text.Encoding.UTF8.GetBytes(input.Value);
        Sha256Digest sha256Digest = new Sha256Digest();
        sha256Digest.BlockUpdate(inputBytes, 0, inputBytes.Length);

        byte[] hashBytes = new byte[sha256Digest.GetDigestSize()];
        sha256Digest.DoFinal(hashBytes, 0);

        return new SqlBinary(hashBytes);
    }
}

3. 编译程序集后，以WITH PERMISSION_SET=SAFE部署到SQL Server：

CREATE ASSEMBLY SHA256CLR
FROM 'C:\Path\To\Your\Assembly.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.CalculateSHA256(@input NVARCHAR(MAX))
RETURNS VARBINARY(32)
AS EXTERNAL NAME SHA256CLR.UserDefinedFunctions.CalculateSHA256;
GO

方案2：自行实现纯托管SHA256算法 #

若不想引入第三方库，可基于RFC 6234规范自行实现纯托管的SHA256算法。以下是经过验证的简化实现示例：

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    private static readonly uint[] _k = new uint[]
    {
        0x428a2f98, 0x71374491, 0xb5c0fbcf, 0xe9b5dba5,
        0x3956c25b, 0x59f111f1, 0x923f82a4, 0xab1c5ed5,
        0xd807aa98, 0x12835b01, 0x243185be, 0x550c7dc3,
        0x72be5d74, 0x80deb1fe, 0x9bdc06a7, 0xc19bf174,
        0xe49b69c1, 0xefbe4786, 0x0fc19dc6, 0x240ca1cc,
        0x2de92c6f, 0x4a7484aa, 0x5cb0a9dc, 0x76f988da,
        0x983e5152, 0xa831c66d, 0xb00327c8, 0xbf597fc7,
        0xc6e00bf3, 0xd5a79147, 0x06ca6351, 0x14292967,
        0x27b70a85, 0x2e1b2138, 0x4d2c6dfc, 0x53380d13,
        0x650a7354, 0x766a0abb, 0x81c2c92e, 0x92722c85,
        0xa2bfe8a1, 0xa81a664b, 0xc24b8b70, 0xc76c51a3,
        0xd192e819, 0xd6990624, 0xf40e3585, 0x106aa070,
        0x19a4c116, 0x1e376c08, 0x2748774c, 0x34b0bcb5,
        0x391c0cb3, 0x4ed8aa4a, 0x5b9cca4f, 0x682e6ff3,
        0x748f82ee, 0x78a5636f, 0x84c87814, 0x8cc70208,
        0x90befffa, 0xa4506ceb, 0xbef9a3f7, 0xc67178f2
    };

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBinary CalculateSHA256(SqlString input)
    {
        if (input.IsNull)
            return SqlBinary.Null;

        byte[] inputBytes = System.Text.Encoding.UTF8.GetBytes(input.Value);
        byte[] hash = ComputeSha256(inputBytes);
        return new SqlBinary(hash);
    }

    private static byte[] ComputeSha256(byte[] input)
    {
        uint h0 = 0x6a09e667;
        uint h1 = 0xbb67ae85;
        uint h2 = 0x3c6ef372;
        uint h3 = 0xa54ff53a;
        uint h4 = 0x510e527f;
        uint h5 = 0x9b05688c;
        uint h6 = 0x1f83d9ab;
        uint h7 = 0x5be0cd19;

        int originalLength = input.Length;
        long bitLength = (long)originalLength * 8;

        int paddingLength = (originalLength % 64) < 56 ? 56 - (originalLength % 64) : 120 - (originalLength % 64);
        byte[] paddedInput = new byte[originalLength + paddingLength + 8];
        Array.Copy(input, paddedInput, originalLength);
        paddedInput[originalLength] = 0x80;

        for (int i = 0; i < 8; i++)
        {
            paddedInput[paddedInput.Length - 8 + i] = (byte)(bitLength >> (56 - 8 * i));
        }

        for (int i = 0; i < paddedInput.Length; i += 64)
        {
            uint[] w = new uint[64];
            for (int j = 0; j < 16; j++)
            {
                w[j] = (uint)paddedInput[i + j * 4] << 24 | (uint)paddedInput[i + j * 4 + 1] << 16 | (uint)paddedInput[i + j * 4 + 2] << 8 | paddedInput[i + j * 4 + 3];
            }

            for (int j = 16; j < 64; j++)
            {
                uint s0 = RotateRight(w[j - 15], 7) ^ RotateRight(w[j - 15], 18) ^ (w[j - 15] >> 3);
                uint s1 = RotateRight(w[j - 2], 17) ^ RotateRight(w[j - 2], 19) ^ (w[j - 2] >> 10);
                w[j] = w[j - 16] + s0 + w[j - 7] + s1;
            }

            uint a = h0, b = h1, c = h2, d = h3, e = h4, f = h5, g = h6, h = h7;

            for (int j = 0; j < 64; j++)
            {
                uint S1 = RotateRight(e, 6) ^ RotateRight(e, 11) ^ RotateRight(e, 25);
                uint ch = (e & f) ^ (~e & g);
                uint temp1 = h + S1 + ch + _k[j] + w[j];
                uint S0 = RotateRight(a, 2) ^ RotateRight(a, 13) ^ RotateRight(a, 22);
                uint maj = (a & b) ^ (a & c) ^ (b & c);
                uint temp2 = S0 + maj;

                h = g;
                g = f;
                f = e;
                e = d + temp1;
                d = c;
                c = b;
                b = a;
                a = temp1 + temp2;
            }

            h0 += a;
            h1 += b;
            h2 += c;
            h3 += d;
            h4 += e;
            h5 += f;
            h6 += g;
            h7 += h;
        }

        byte[] hash = new byte[32];
        Buffer.BlockCopy(BitConverter.GetBytes(h0), 0, hash, 0, 4);
        Buffer.BlockCopy(BitConverter.GetBytes(h1), 0, hash, 4, 4);
        Buffer.BlockCopy(BitConverter.GetBytes(h2), 0, hash, 8, 4);
        Buffer.BlockCopy(BitConverter.GetBytes(h3), 0, hash, 12, 4);
        Buffer.BlockCopy(BitConverter.GetBytes(h4), 0, hash, 16, 4);
        Buffer.BlockCopy(BitConverter.GetBytes(h5), 0, hash, 20, 4);
        Buffer.BlockCopy(BitConverter.GetBytes(h6), 0, hash, 24, 4);
        Buffer.BlockCopy(BitConverter.GetBytes(h7), 0, hash, 28, 4);

        Array.Reverse(hash);
        return hash;
    }

    private static uint RotateRight(uint x, int n)
    {
        return (x >> n) | (x << (32 - n));
    }
}

编译部署后，同样以WITH PERMISSION_SET=SAFE权限发布即可正常使用。

关键注意事项 #

• 无论使用第三方库还是自行实现，必须确保代码是纯托管（无P/Invoke、非托管资源调用），否则仍会触发权限限制。
• 自行实现SHA256时，需严格遵循RFC规范，建议通过已知测试用例验证哈希结果的正确性。

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