五餅二魚工作室

在早些時候的 SQL Server 版本中，有時為了想讓 SQL 語法可以擴充一些比較方便的處理，像是要處理正規表示，或者是組合拆解字串，有時會使用 C# 來寫一些擴充的函數，方便在 SQL 語法內直接處理，又不少時候多虧了有這樣的特性，讓我可以有效地解決一些問題。

而此次剛好遇到朋友有需要在 SQL Server借用到 HtmlDecode 和 HtmlEncode 的處理，這些原本在 .Net 內都有直接現成的函數，實在不需要花太多時間自己用 SQL 語法做處理，因此就想到借用 SQL CLR C# 使用者定義函數來做處理，而程式碼也如下面所展示的，直接套用現成的即可

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString HtmlDecoder(string source)
    {
        using (StringWriter myWriter = new StringWriter())
        {
            WebUtility.HtmlDecode(source, myWriter);
            return new SqlString(myWriter.ToString());
        }
    }
}

而這樣的程式，在 SQL Server 2016 和之前的版本，對於這類的程式碼，基本上使用 Visual Studio 直接發行即可，也沒有甚麼特別的困難。

因此佈署到我們的測試資料庫下面，就可以看到有新增的兩個函數

因此我們也寫了一個簡單的範例，來測試看看我們的擴充函數是否可以正常執行

DECLARE @source NVARCHAR(256) = '資料有 & < > \'
DECLARE @encode NVARCHAR(256)
DECLARE @decode NVARCHAR(256)

select @encode = [dbo].[HtmlEncode](@source) 
select @decode = [dbo].[HtmlDecode](@encode) 

PRINT @@VERSION
PRINT @encode ;
PRINT @decode ;

如果伺服器環境上是允許「啟用 CLR 的擴充」的情況下，上述的指令應該可以呈現以下的結果，看起來就是一碟小菜的問題。

---

然而同樣的程式碼，要是到 SQL Server 2017 和之後的版本上要進行發佈的時候，因為 2017 針對 CLR 的安全性又強化了規範，使得原本可以很容易佈署的處理，就要多繞一下路來進行。

首先因為我們原本的專案是 SQL Server 2016 的版本，因此在專案設定上要做點調整，雖然我們都已經使用 Visual Studio 2022 的版本，但是在資料庫專案上，目前還無法選擇目標平台是 SQL Server 2022，因此在這裡我們先選擇目前支援最高的版本 SQL Server 2019

可是當我們設定完後重新發行一次，此時就會出現類似以下的錯誤訊息

完整的訊息如下

正在建立 組件 [TestCLR]...
(104,1): SQL72014: .Net SqlClient Data Provider: 訊息 10343，層級 14，狀態 1，行 1 因為 sp_configure 的 [CLR 嚴格安全性] 選項設定為 1，=導致使用 SAFE 或 EXTERNAL_ACCESS 選項對組件 'TestCLR' 執行 CREATE 或 ALTER ASSEMBLY 失敗。Microsoft 建議您使用憑證或對應到使用 UNSAFE ASSEMBLY 權限登入的非對稱金鑰簽署組件。此外也可使用 sp_add_trusted_assembly 信任組件。
(104,0): SQL72045: 指令碼執行錯誤。已執行的指令碼:
CREATE ASSEMBLY [TestCLR]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103005809EF630000000000000000E00022200B013000000600000006000000000000EA250000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000982500004F00000000400000A002000000000000000000000000000000000000006000000C000000602400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F0050000002000000006000000020000000000000000000000000000200000602E72737263000000A0020000004000000004000000080000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000C00000
批次執行時發生錯誤。

而從錯誤訊息中可以看出，因為預設有啟用 [CLR 嚴格安全性] 的選項，因此除非我們關閉該選項，要不然就要按照訊息中所建議的方法，使用非對稱金鑰或者是使用憑證的方式，才能進行安裝。因此後續我們就來使用比較簡單的非對稱金鑰的方式來做設定。

---

此時我們要將我們的資料庫專案上，來建立一個私有的憑證，所幸這個部分 Visual Studio 已經內建相關現成的功能，因此我們可以很容易地來用 UI 來建立該專案所需要使用的 pfx 憑證

因為一開始專案會沒有 pfx 憑證，因此我們要勾選簽署組件 → 新增 

這裡我們設定一組密碼來保護我們的 pfx 憑證

確定後回來我們的方案總管，就可以看到我們的專案下已經多了一個 pfx 憑證，這樣當我們建立程式的時候，Visual Studio 就會自動使用該憑證，將我們所建立出來的檔案用該憑證來進行保護。

此時我們將專案所編譯出來的 TestCLR.dll 的檔案，將其複製出來到一個暫存的目錄下面，接著開啟 SSMS 來進行相關設定

-- 切記要記得切到 master 資料庫下
USE [master]
GO


-- 從我們的檔案來建立非對稱金鑰
CREATE ASYMMETRIC KEY CLRKey FROM EXECUTABLE FILE = 'C:\Temp\TestCLR.dll'
GO


-- 建立一個特別的帳號對應上的步驟所建立的金鑰
CREATE LOGIN CLRKeyLonin FROM ASYMMETRIC KEY CLRKey;
GO


-- 賦予該登入帳號具有使用非安全的程式
GRANT UNSAFE ASSEMBLY TO CLRKeyLonin
GO

完成前面的步驟後，接下來到我們要安裝該套件的資料庫，因此針對登入會建立一個對應到資料庫的使用者 CLRKeyUser，接著我們就可以設定 SQL Server 可以載入該檔案，必且建立兩個函數來對應到我們所撰寫的 SQL CLR 上對應的功能

USE DEMO
GO

CREATE USER CLRKeyUser FOR LOGIN CLRKeyLonin
GO

CREATE ASSEMBLY [TestCLR] FROM 'C:\Temp\TestCLR.dll' WITH PERMISSION_SET = SAFE
GO

CREATE FUNCTION [dbo].[HtmlDecode]
(@source NVARCHAR (MAX) NULL)
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [TestCLR].[UserDefinedFunctions].[HtmlDecode]
GO

CREATE FUNCTION [dbo].[HtmlEncode]
(@source NVARCHAR (MAX) NULL)
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [TestCLR].[UserDefinedFunctions].[HtmlEncode]
GO

完成上述相關佈署之後，我們也就可以開始來進行測試了，這裡我們依然使用跟 SQL Server 2016 測試中相同的指令

DECLARE @source NVARCHAR(256) = '資料有 & < > \'
DECLARE @encode NVARCHAR(256)
DECLARE @decode NVARCHAR(256)

select @encode = [dbo].[HtmlEncode](@source) 
select @decode = [dbo].[HtmlDecode](@encode) 

PRINT @@VERSION
PRINT @encode ;
PRINT @decode ;

此時可以跟 SQL Server 2016 的環境來比對，我們直接使用跟之前環境相同的語法來測試，看起來使用非對稱金鑰和相關設定後，我們程式已經可以正常佈署和使用。

而且如果後續我們這個擴增函數還要再多新增其他功能的話，也都可以直接在 Visual Studio 下直接發行即可，因此只要第一次建立步驟稍微辛苦一點，以後每次要重新發行就跟之前發佈的方式相同，直接在 Visual Studio 上發佈就可以了。

雖然這個只是其中一種方式，但相對其他幾中方式來說，該方式相對來說比較簡單。也不需要好幾個格是之間換來換去，因此目前我也都使用這樣的方式，來佈署相關 CLR 擴增函數。