最近同事的程式在兩個專案陸續發生SQLCLR無法載入檔案或組件問題，一個發生在測試環境因為備份還原的場景，另一個則是正式環境突發性的發生，自己太少用SQL CLR了，來試試解題然後筆記。
先解其中一家在測試環境發生的問題:
這家客戶發生的狀況是從其他測試機器備份資料庫後再還原到新的測試機，一執行SQL CLR寫好的預存程序，就出現無法載入檔案或組件問題。
解決的方式是
- 確認資料庫引擎對外部組件的信任
- 資料庫dbo的sid與sys.server_principals 及sys.databases是否相符。
SQL CLR
SQL CLR是一種可以讓SQL資料庫的預存程序、功能去呼叫.NET 功能的技術，應用場景通常是SQL 目前還做不到的事，透過.NET來擴充SQL的功能，讓使用者功能實現。不過因為大部分資訊系統設計資料流的特性，都是先在前端AP運算完之後才到DB，到DB前幾乎已經完成大部分的運算工作了，所以其實使用的機會真的很少。
好，來準備模擬的環境重現錯誤。
建立測試資料庫SQLCLRDb(SQL2008)及組件
建立資料庫
/****** Object: Database [SQLCLRDb] Script Date: 04/17/2018 15:05:45 ******/
CREATE DATABASE [SQLCLRDb] ON PRIMARY
( NAME = N'SQLCLRDb', FILENAME = N'D:\data\SQLCLRDb.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SQLCLRDb_log', FILENAME = N'D:\data\SQLCLRDb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
建立組件(直接從組件位元組)
USE SQLCLRDb
CREATE ASSEMBLY [HelloWorld]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001DA06C4B0000000000000000E00002210B010800000800000006000000000000CE2600000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000007826000053000000004000009003000000000000000000000000000000000000006000000C000000002600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000D4060000002000000008000000020000000000000000000000000000200000602E72737263000000900300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000B0260000000000004800000002000500742000008C0500000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002001000000001000011007201000070730F00000A0A2B00062A1E02281000000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000D0010000237E00003C0200007802000023537472696E677300000000B40400001C00000023555300D0040000100000002347554944000000E0040000AC00000023426C6F620000000000000002000001471402000900000000FA01330016000001000000110000000200000002000000100000000C00000001000000010000000200000000000A0001000000000006003E0037000A00660051000600930081000600AA0081000600C70081000600E60081000600FF00810006001801810006003301810006004E01810006008601670106009A0181000600C601B3013700DA01000006000902E90106002902E9010A006202470200000000010000000000010001000100100019000000050001000100502000000000960070000A0001006C200000000086187B000F00010019007B00130021007B00130029007B00130031007B00130039007B00130041007B00130049007B00130051007B00130059007B00180061007B00130069007B001D0079007B00230081007B000F0089007B000F0011007B00130009007B000F002000730028002E002B0032002E00130042002E001B0042002E00230048002E000B0032002E00330057002E003B0042002E004B0042002E005B0078002E00630081002E006B008A002D000480000001000000680E1E760000000000007000000002000000000000000000000001002E0000000000020000000000000000000000010045000000000000000000003C4D6F64756C653E0048656C6C6F576F726C642E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670048656C6C6F576F726C64002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500000017480065006C006C006F00200057006F0072006C006400000000008895DD36598FB94681CF27B4C443A44E0008B77A5C561934E089040000110903200001042001010E04200101020520010111390420010108040100000004070111090F01000A48656C6C6F576F726C6400000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000001DA06C4B0000000002000000590000001C2600001C08000052534453907F1962D03D9843AD482AF957AE4FF801000000493A5C63617365735C636C722E6C6F61645C48656C6C6F576F726C645C48656C6C6F576F726C645C6F626A5C44656275675C48656C6C6F576F726C642E70646200000000A02600000000000000000000BE260000002000000000000000000000000000000000000000000000B026000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001001E76680E000001001E76680E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F0066007400000040000B000100460069006C0065004400650073006300720069007000740069006F006E0000000000480065006C006C006F0057006F0072006C0064000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003600380038002E00330030003200330038000000000040000F00010049006E007400650072006E0061006C004E0061006D0065000000480065006C006C006F0057006F0072006C0064002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310030000000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000480065006C006C006F0057006F0072006C0064002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D00650000000000480065006C006C006F0057006F0072006C0064000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003600380038002E00330030003200330038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003600380038002E00330030003200330038000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000D03600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = UNSAFE
GO
如果遇到授權的問題
訊息10327，層級14，狀態1，行1
組件'HelloWorld' 的CREATE ASSEMBLY 失敗，因為組件'HelloWorld' 沒有PERMISSION_SET = UNSAFE 的授權。
下列之一為True 時，組件才會獲得授權: 資料庫擁有者(DBO) 有UNSAFE ASSEMBLY 權限，且資料庫已開啟TRUSTWORTHY 資料庫屬性；或者組件已使用憑證或非對稱金鑰簽署，且對應的登入具有UNSAFE ASSEMBLY 權限。
暫時的解決辦法: (建議盡可能還是建立SAFE的組件)
ALTER DATABASE SQLCLRDb SET TRUSTWORTHY ON
建立自訂函數
CREATE FUNCTION [HelloWorld]
()
RETURNS nvarchar(4000)
AS
EXTERNAL NAME [HelloWorld].[UserDefinedFunctions].[HelloWorld]
GO
測試SQLCLR
SELECT dbo.HelloWorld()
備份資料庫(SQL2008)
BACKUP DATABASE [SQLCLRDb] TO DISK = N'D:\data\SQLCLRDb.bak'
WITH NOFORMAT, INIT, NAME = N'SQLCLRDb-完整資料庫備份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
還原資料庫(SQL2016)
搬到另一台機器上的SQL 2016後，我們來還原
USE [master]
RESTORE DATABASE [SQLCLRDb] FROM DISK = N'C:\SQL\SQLCLRDb.bak' WITH FILE = 1,
MOVE N'SQLCLRDb' TO N'C:\SQL\SQLCLRDb.mdf',
MOVE N'SQLCLRDb_log' TO N'C:\SQL\SQLCLRDb_log.ldf',
NOUNLOAD, STATS = 5
GO
測試SQLCLR
好，來執行您好，世界
USE SQLCLRDb
SELECT dbo.HelloWorld()
一執行就發生了錯誤，從管理畫面或是SQL Server紀錄檔都能觀察到以下無法載入檔案或組件的錯誤
訊息 10314，層級 16，狀態 11，行 11
嘗試載入組件識別碼 65537 時，Microsoft .NET Framework 發生錯誤。伺服器可能資源不足，或者組件具有 PERMISSION_SET = EXTERNAL_ACCESS 或 UNSAFE 而不受信任。請再次執行查詢，或參閱文件集，以了解如何解決組件信任問題。如需有關此錯誤的詳細資訊:
System.IO.FileLoadException: 無法載入檔案或組件 'helloworld, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' 或其相依性的其中之一。 發生關於安全性的錯誤。 (發生例外狀況於 HRESULT: 0x8013150A)
System.IO.FileLoadException:
觀察執行SQLCLR的AppDomain狀態
SELECT * from sys.dm_clr_appdomains
狀態: E_APPDOMAIN_SHARED : 執行階段 AppDomain 已備妥，可供多位使用者使用。
透過dmv查詢已經載入的組件
SELECT * from sys.dm_clr_loaded_assemblies
果然還沒載入
信任外部存取或不安全的組件
好，因為組件建立時定義為UNSAFE，我們來指定 SQL Server 執行個體信任資料庫及其中的內容
ALTER DATABASE SQLCLRDb SET TRUSTWORTHY ON
CLR 整合程式碼存取安全性: SAFE
僅允許內部計算和本機資料存取。 安全是限制最嚴格的權限集合。 與組件所執行的程式碼安全權限無法存取外部系統資源，例如檔案、 網路、 環境變數或登錄。
再執行一次您好，世界
SELECT dbo.HelloWorld()
還是發生無法載入檔案或組件的錯誤，但這次是發生例外狀況於 HRESULT: 0x80FC80F1，沒有再出現”發生關於安全性的錯誤”
訊息 10314，層級 16，狀態 11，行 25
嘗試載入組件識別碼 65537 時，Microsoft .NET Framework 發生錯誤。伺服器可能資源不足，或者組件具有 PERMISSION_SET = EXTERNAL_ACCESS 或 UNSAFE 而不受信任。請再次執行查詢，或參閱文件集，以了解如何解決組件信任問題。如需有關此錯誤的詳細資訊:
System.IO.FileLoadException: 無法載入檔案或組件 'helloworld, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' 或其相依性的其中之一。 發生例外狀況於 HRESULT: 0x80FC80F1
檢查資料庫內的dbo sid是否與sys.server_principals 及sys.databases相符
--sys.server_principals 及sys.database
SELECT A.NAME ,OWNER_SID,B.NAME
FROM SYS.DATABASES A
JOIN SYS.SERVER_PRINCIPALS B
ON A.OWNER_SID = B.SID
WHERE A.NAME = 'SQLCLRDB'
--dbo sid
SELECT * FROM SQLCLRDB.sys.sysusers
資料庫內的dbo sid(淺黃)與SYS.SERVER_PRINCIPALS的sid(金色)不同。
修改dbowner給sa或其他習慣管理的sql login
ALTER AUTHORIZATION ON DATABASE::[SQLCLRDb] TO [sa]
或是
EXEC sp_changedbowner 'sa'
再檢查一次dbo sid是否與sys.server_principals 及sys.databases相符
再一次您好，世界
SELECT dbo.HelloWorld()
Hello World成功~
再一次透過dmv查詢已經載入的CLR
SELECT * from sys.dm_clr_loaded_assemblies
果然載入了
觀察執行SQLCLR的AppDomain狀態
SELECT * from sys.dm_clr_appdomains
因為已經開始使用了，CPU使用量(total_processor_time_ms)及記憶體使用也開始增加。
小結
- 碰過客戶很反對使用SQLCLR，原因是怕出現奇怪的系統異常。
- 如果無法避免要使用SQLCLR，盡量建立SAFE的組件。
- 今天先解決簡單的環境變異，正式環境突發性的異常先報案了，後續來請客戶先追蹤CLR記憶體使用。
感覺追蹤的路還很長..
參考
Unable to load CLR assembly intermittently
CREATE LOGIN statement, visit the following Microsoft Developer Network (MSDN) Web site:
sp_changedbowner stored procedure, visit the following MSDN Web site:
CREATE ASSEMBLY statement, visit the following MSDN Web site:
sys.dm_clr_appdomains (Transact-SQL)
