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

因為一些原因,系統內使用了少量的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) 

 


參考:  

SQL CLR 整合簡介 

SQL CLR Function(codeproject) 

TRUSTWORTHY 資料庫屬性 

主機保護屬性 Host Protection Attributes and CLR Integration Programming 

CLR Strict Security