[SQL Server]On Linux奇幻旅程(三) SQLCLR

2017-07-15

9109
0
SQLOnLinux

因為一些原因，系統內使用了少量的SQLCLR，來讓T-SQL撰寫的Script可以透過SQL函數呼叫C# Method(SqlFunction)來取得特殊環境的運算結果，來試試SQL Server SQLCLR在Linux版本環境的相容性。

測試環境

開發環境: Windows 10 + Visual Studio 2017
DB環境: Red Hat Enterprise Linux 7.3 + SQL Server 2017 CTP2.1

步驟:

1.建立C#組件

2.啟用SQL CLR

3.啟用信任資料庫以及內容

4.新增SQL組件

5.從SQL組件新增函數

6.測試SQL CLR

1.建立C#組件(dll)

從Windows使用Visual Studio新增SQL Server資料庫專案，這邊我們用SQLCLR作為專案名稱。

新增一個SQL CLR C# 使用者定義函式(HelloWorld.cs)

輸入以下程式碼

using System.Data.SqlTypes; 
public partial class UserDefinedFunctions 
{ 
    [Microsoft.SqlServer.Server.SqlFunction] 
    public static SqlString HelloWorld() 
    { 
        return new SqlString("Hello World! 你好，世界！"); 
    } 
}

編譯程式後，取得SQLCLR.dll ，假設我們編譯後的路徑是C:\SQL\SQLCLR.dll

2.啟用SQL CLR

由於SQL CLR 組件是外部程式碼(external)，預設SQL CLR 是被禁止使用的，如果要使用SQLCLR，需要透過sp_configure啟用。

打開SSMS管理工具，連接到SQL Server On Linux測試機，執行以下指令

exec sp_configure 'clr enabled', 1; 
reconfigure

3.啟用信任資料庫及其中的內容

除了啟用SQLCLR之外，還需要啟用信任。

ALTER DATABASE CARD_DEMO SET TRUSTWORTHY ON

*較安全的使用方式是將以sn.exe工具將C#寫好的組件簽署，並且將金鑰匯入master資料庫中 (CREATE ASYMMETRIC KEY FROM FILE)。

4.新增SQL組件

使用pscp指令從Windows目錄把組件搬到Linux預設SQL Server Bin目錄下。

pscp C:\SQL\SQLCLR.dll root@192.168.100.102:/opt/mssql/bin/

打開SSMS管理工具從剛剛Linux環境下的SQL目錄建立組件

CREATE ASSEMBLY [SQLCLR] 
AUTHORIZATION [dbo] 
FROM '/opt/mssql/bin/SQLCLR.dll'   WITH PERMISSION_SET = SAFE;

5.從SQL組件新增SQL函數

CREATE FUNCTION [dbo].HelloWorld() RETURNS nvarchar(max)  
EXTERNAL NAME  SQLCLR.UserDefinedFunctions.HelloWorld  
Go

新增的函數

7.測試SQL CLR

您好! 世界!

小結:

只支援SQL組件SAFE等級Permission set

雖然Hello World很順利的執行完成，但由於目前使用的版本(Microsoft SQL Server 2017 (CTP2.1)只支援SQL組件SAFE等級Permission set，如果我們寫的組件目前是使用EXTERNAL_ACCESS或是UNSAFE等級，運氣好一點，也許調整設定就能解決；但運氣差，則可能會因無法通過Code Access Security驗證而必須改寫或放棄使用。

我們就得要傷腦筋了。

Only Support SAFE assemblies

Assembly 'SQLCLR' cannot be loaded because this edition of SQL Server only supports SAFE assemblies. (Microsoft SQL Server, 錯誤: 10342)

安全性錯誤

組件'xxx' 的 ALTER ASSEMBLY 失敗，因為組件 'xxx' 驗證失敗。請檢查參考的組件是否為最新的，而且受信任 (針對 external_access 或不安全) 於資料庫中執行。

接著將會出現 CLR 驗證器的訊息 (如果有的話)

[ : xxxx::Method][mdToken=0x6000002] 發生關於安全性的錯誤。 (Microsoft SQL Server, 錯誤: 6218)