因為一些原因,系統內使用了少量的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)
參考:
主機保護屬性 Host Protection Attributes and CLR Integration Programming