SQL Server 呼叫 C# 組件 (CLR 整合) 實作解密功能：從建立 DLL 到 T-SQL 部署完整流程

當加密邏輯以 .NET 實作且需在 SQL Server 內解密時，CLR 整合 是最佳橋樑。本文完整示範：建立 C# 類別庫、簽署/部署組件、啟用 CLR、建立 T-SQL 包裝函數、實際呼叫驗證的端到端流程。

應用場景

• .NET 系統使用特殊加密演算法 (如自訂 AES 模式、URL 編碼加密)。
• 舊版 Delphi/其他系統需解密共用資料，移植演算法成本高。
• 集中在資料庫層解密，避免應用層分散實作與金鑰管理。

步驟 1：建立 C# 類別庫 (DLL)

using System;
using System.Web;  // 需參考 System.Web.dll
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

namespace MyDll
{
    public partial class MyClass
    {
        [SqlMethod(IsDeterministic = true, IsPrecise = true)]
        public static SqlString UrlDecode(string value)
        {
            if (value == null) return SqlString.Null;
            return new SqlString(HttpUtility.UrlDecode(value));
        }
    }
}

• 編譯目標框架：.NET Framework 2.0/3.5/4.x (視 SQL Server 版本相容性)。
• 簽署強名稱金鑰 (可選，但建議生產環境簽署)。
• 複製 DLL 至 SQL Server 主機本地路徑 (如 C:\CLR\MyDll.dll)。

步驟 2：啟用 CLR 並部署組件

-- 1. 啟用 CLR 整合 (需 sysadmin)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
GO

-- 2. 設定資料庫 TRUSTWORTHY (簡化權限，或改用非對稱金鑰/憑證授權)
ALTER DATABASE YourDB SET TRUSTWORTHY ON;
GO

-- 3. 部署相依組件 (此處為 System.Web)
CREATE ASSEMBLY [System.Web]
FROM 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
WITH PERMISSION_SET = UNSAFE;
GO

-- 4. 部署自訂組件
CREATE ASSEMBLY SQL_CLR_Url
FROM 'C:\CLR\MyDll.dll'
WITH PERMISSION_SET = UNSAFE;
GO

權限集說明

注意： UNSAFE 需 TRUSTWORTHY ON 或非對稱金鑰授權，生產環境建議使用簽署金鑰方式而非 TRUSTWORTHY。

步驟 3：建立 T-SQL 包裝函數

CREATE FUNCTION dbo.SqlUrlDecode(@urlstr NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS EXTERNAL NAME SQL_CLR_Url.[MyDll.MyClass].UrlDecode;
GO

命名規則： 組件名稱.[命名空間.類別名稱].方法名稱

步驟 4：呼叫驗證

SELECT dbo.SqlUrlDecode(Name) FROM UserTable WHERE Id = 1;
-- 回傳 URL 解碼後的字串

常見問題與除錯

安全性與維護建議

• 組件版本升級：ALTER ASSEMBLY SQL_CLR_Url FROM 'C:\CLR\MyDll_v2.dll'
• 移除組件：DROP FUNCTION dbo.SqlUrlDecode; DROP ASSEMBLY SQL_CLR_Url; DROP ASSEMBLY [System.Web];
• 審計：定期查詢 sys.assemblies、sys.assembly_modules 確認部署狀態。
• 效能：CLR 函數有呼叫開銷，高頻查詢 (百萬列) 評估是否改用 T-SQL 原生邏輯或內建函數。

CLR 整合讓 SQL Server 能無縫復用 .NET 生態系的豐富函式庫，解決跨平台加密/解密/編碼互通難題，是混合技術棧環境的關鍵整合點。