SQL Server資料庫還原或分離附加後程式集遇到的SQL CLR問題

工作中遇到一個案例：備份還原過後或者對資料庫分離&附加後（移動資料庫文件），發現一些許可權為EXTERNAL_ACCESS和UNSAFE程式集對應的CLR函數,在調用的時候會出現一些錯誤。下麵特意用YourSQLDba備份還原到一個測試環境，然後調用CLR函數，就會遇到如下錯誤：

USE YourSQLDba;

GO

SELECT  *

FROM    [yUtl].[clr_GetFolderList]('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\',

                                   '*.mdf');

Msg 10314, Level 16, State 11, Line 19

在嘗試載入程式集 ID 65537 時 Microsoft .NET Framework 出錯。伺服器可能資源不足，或者不信任該程式集。請重新運行查詢，或檢查有關的文檔瞭解如何解決程式集信任問題。有關此錯誤的詳細信息:

System.IO.FileLoadException: Could not load file or assembly 'yoursqldba_clrfileop, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException:

   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)

   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   at System.Reflection.Assembly.Load(String assemblyString)

檢查發現assembly_id=65537的程式集為YourSqlDba_ClrFileOp

USE YourSQLDba;

GO

SELECT a.name 

      ,a.principal_id

      ,a.assembly_id 

      ,a.clr_name

      ,a.permission_set_desc 

      ,a.is_visible 

      ,a.create_date 

      ,a.modify_date

FROM  sys.assemblies AS a 

出現這種錯誤，一般是EXTERNAL_ACCESS 和UNSAFE的程式集，錯誤出現的具體原因有兩種:

1: 資料庫Owner的SID變化了。無論你是備份還原還是分離附加操作，都要確保資料庫所有者SID在資料庫屬性中顯示的內容與源資料庫元數據中記錄的內容之間匹配。如果使用備份/還原，則資料庫所有者SID可能不匹配（取決於你操作時使用的賬號），這將阻止CLR代碼運行。

2: 資料庫的TRUSTWORTHY屬性值變化了。 資料庫屬性TRUSTWORTHY用於指明SQL Server實例是否信任該資料庫以及其中的內容。 預設情況下，此設置為 OFF，但是可以使用ALTER DATABASE語句將其設置為ON.

此屬性可用於減少附加資料庫所帶來的某些隱患，該資料庫包含下列對象之一：

·         帶有 EXTERNAL_ACCESS 或 UNSAFE 許可權設置的有害程式集。 有關詳細信息，請參閱 CLR Integration Security。

·         所定義的、作為高特權用戶執行的有害模塊。 有關詳細信息，請參閱 EXECUTE AS 子句 (Transact-SQL)。

這兩種情況均要求具有特定程度的許可權，並且在已附加到SQL Server實例的資料庫的上下文中使用這兩種情況時，應採取相應的機制保護這兩種情況。 但是，如果資料庫離線，則對資料庫文件具有訪問許可權的用戶可能會將其附加到其選擇的SQL Server實例，並將有害內容添加到資料庫中。 在SQL Server中分離和附加資料庫時，將對限制訪問資料庫文件的數據和日誌文件設置某些許可權。

因為不能立即信任附加到SQL Server實例的資料庫，所以不允許資料庫訪問超出資料庫範圍的資源，直到資料庫已顯式標記為可信。 因此，如果備份或分離TRUSTWORTHY選項設置為 ON 的資料庫並將該資料庫附加或還原到同一個或另一個 SQL Server 實例後，則附加/還原完成後 TRUSTWORTHY 屬性將設置為 OFF。 此外，旨在訪問資料庫以外資源的模塊和帶有 EXTERNAL_ACCESS 或 UNSAFE 許可權設置的程式集還需要其他條件才能成功運行。

下麵檢查資料庫的屬性TRUSTWORTHY（is_trustworth_on）， 如下所示

SELECT  database_id ,

        name ,

        is_trustworthy_on

FROM    sys.databases

WHERE name='YourSQLDba';

但是你對比源資料庫的屬性TRUSTWORTHY，就會發現資料庫還原後，TRUSTWORTHY屬性會變化（is_trustworth_on從1變為了0）.

設置YourSQLDba的屬性TRUSTWORTHY為ON

USE master;

GO

ALTER DATABASE YourSQLDba SET TRUSTWORTHY ON;

還原資料庫時，由於可能使用不同賬號，那麼就會出現資料庫的owner出現變化的情況。當然，也有可能使用相同的賬號操作，不會出現db_owner變化的情況。下麵這種情況，就是源資料庫的db_owner為sa，但是我使用域賬號做了還原操作。資料庫的db_owner變成了一個域賬號。

USE [YourSQLDba];

GO         

EXEC sp_changedbowner 'sa';

GO

將資料庫的db_owner修改為sa,此時問題解決。當然也可以先修改db_owner，然後設置資料庫的trustworth屬性。分離附加資料庫也會導致TRUSTWORTHY屬性變化，還有可能導致資料庫db_owner變化（這個取決於你操作時使用的賬號），另外。這種錯誤只對許可權為EXTERNAL_ACCESS 和UNSAFE的程式集出現，對於SAFE_ACCESS的程式集，不會出現這個問題。