SQL Server数据库还原或分离附加后程序集遇到的SQL CLR问题

工作中遇到一个案例：备份还原过后或者对数据库分离&附加后（移动数据库文件），发现一些权限为EXTERNAL_ACCESS和UNSAFE程序集对应的CLR函数,在调用的时候会出现一些错误。下面特意用YoursqlDba备份还原到一个测试环境，然后调用CLR函数，就会遇到如下错误：

USE YourSQLDba;

Go

SELECT  *

FROM    [yUtl].[clr_GetFolderList]("C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA",

                                   "*.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的程序集，不会出现这个问题。

参考资料：

https://support.microsoft.com/zh-cn/help/918040/you-may-receive-an-error-message-when-you-try-to-run-an-existing-clr-o

Https://docs.microsoft.com/en-us/sql/relational-databases/security/trustworthy-database-property?view=sql-server-ver15