SQL Server Assembly (SQL CLR) 還原數據庫后的問題

作者: Sai~ 來源: 博客園發布時間: 2011-01-20 23:54 閱讀: 1910 次推薦: 0 原文鏈接 [收藏]

摘要：今天我們將介紹在SQL Server Assembly還原數據庫中，如果備份的數據庫存在EXTERNAL_ACCESS 和UNSAFE的程序集,那么在還原的時候程序集會出現一些奇怪的錯誤。

　　前端時間給別人做遷移數據庫時候,遇到一些問題.大致是,如果備份的數據庫存在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 來實現的,因此只要簡單的修改數據庫所有者就好了....那么對等復制的問題也就解決了!!