SQL Server Assembly還原資料庫後的問題

前端時間給別人做遷移資料庫時候,遇到一些問題.大致是,如果備份的資料庫存在EXTERNAL_ACCESS 和UNSAFE的程式集,那麼在還原的時候程式集會出現一些奇怪的錯誤:

訊息 10314，層級 16，狀態 11，第 1 行

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

System.IO.FileLoadException: 未能負載檔案或程式集“testclr, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null”或它的某一個依賴項。異常來自 HRESULT:0x80FC80F1
System.IO.FileLoadException: 

在 System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) 

在 System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) 

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

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

至於產生的原因大概是,在備份資料庫的時候,在機器A,那麼資料庫的擁有者是A\Administrator(如果用windows登入建立),那麼但是我們還原到伺服器B,那麼擁有者可能是B\Administrator,那麼SQL CLR的安全性會認為該程式集不可靠.

例如:

我首先建立一個簡單的SQL CLR 預存程序:

 

  
using System;using System.Data;  
  
using System.Data.SqlClient;  
  
using System.Data.SqlTypes;  
  
using Microsoft.SqlServer.Server;    
  
public partial class StoredProcedures{   
  
 [Microsoft.SqlServer.Server.SqlProcedure]      
  
public static void Test()    {        
  
// 在此處放置代碼    }};編譯建立程式集.  
  
 
 

在機器A上的資料庫上執行:

 

  
CREATE DATABASE test;   
  
USE test;   
  
ALTER DATABASE test SET TRUSTWORTHY ON;   
  
CREATE ASSEMBLY [TestCLR] FROM 'E:\Documents\Visual Studio 2010\Projects\TestCLR\TestCLR\bin\Release\TestCLR.dll' 
  
WITH PERMISSION_SET = EXTERNAL_ACCESS;  
  
--SAFE;  
  
CREATE PROC dbo.usp_testASEXTERNAL NAME [TestCLR].StoredProcedures.Test;   
  
EXEC dbo.usp_test;   
  
USE master;   
  
BACKUP DATABASE test TO DISK = 'c:\test.bak' WITH FORMAT;  
  
 
 

將c:\test.bak copy 到機器B上,然後執行:

USE [master];--還原資料庫RESTORE DATABASE test FROM DISK = 'c:\test.bak' WITH RECOVERY,MOVE 'test' TO 'E:\data\test.mdf',MOVE 'test_log' TO 'E:\data\test.ldf',REPLACE;

--如果沒有啟用CLR,開啟EXEC sp_configure 'clr enabled',1RECONFIGURE WITH OVERRIDE; USE test;

--查看程式集,是存在的.SELECT * FROM sys.assemblies;SELECT * FROM sys.assembly_files;

--還原之後的資料庫TRUSTWORTHY 都是OFF的,需要重新設定ALTER DATABASE test SET TRUSTWORTHY ON; USE test;

--執行預存程序EXEC dbo.usp_test;

但是一執行就報錯了.

解決方案:

在還原資料庫之後,我們可以將資料庫的OWNER設定成SA.

exec sp_changedbowner 'sa'

再調用預存程序就是成功的.

可以查看:KB http://support.microsoft.com/kb/918040

後來經過一些整理,發現當SQL CLR 存在EXTERNAL_ACCESS或者是UNSAFE的程式集的時候,SQL Server會檢查DBO的SID在sys.databases 和sys.server_principals是否一致.

因此我們可能未必一定要修改成sa 的,只要所有者的SID在sys.databases和sys.server_principals 是一致的,就不出問題.

我們在SSMS裡面右鍵資料庫屬性->找到檔案選項卡->發現在所有者(是空的,還原以後原來的SID,資料庫擁有者在當前的sys.server_principals不匹配的),我們可以在 [...] 裡面選擇一個,具有建立CREATE ASSEMLY 許可權的所有者就好,我選擇了B\Administrator,然後測試 CLR 預存程序,沒問題,

引深:

在SQL Server 複製裡面也存在類似的問題,就是我們做 "點對點複寫" 的時候,會出現DBO不存在,以及sp_replcmd 不存在類似的錯誤.其實也是因為點對點複寫初始化訂閱是通過 RESTORE 來實現的,因此只要簡單的修改資料庫擁有者 就好了....那麼點對點複寫的問題也就解決了!!

編輯精選】