SQL Server 停止加载程序集

Question

我们为 SQL Server 2008 R2 开发了一个程序集。

大会已经工作一周了。程序集中的托管存储过程整整一周都工作正常，然后就停止工作了。我们已经多次看到这个问题了。让它再次工作的方法是重新启动SQL Server。

Msg 10314, Level 16, State 11, Line 4
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 
  System.IO.FileLoadException: Could not load file or assembly 'myAssembly, Version=2.0.0.490, Culture=neutral, PublicKeyToken=5963130873dd3a75' or one of its dependencies. Exception from HRESULT: 0x80FC0E21 System.IO.FileLoadException:
  at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
  at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
  at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
  at System.Reflection.Assembly.Load(String assemblyString)

我在网上找到了不同的文章。

此知识库表明我可能已从另一个 SQL Server 恢复了数据库，但我发誓我没有这样做。

这个博客说如果我在 SQL Server 2005 上安装 .NET 3.5，我可能会遇到这个问题，但我的是SQL Server 2008 R2，出现此问题时我没有安装任何东西。

最主要的是它可以持续一段时间。它只是随机停止工作。然后，如果我们重新启动 SQL Server，它将再次开始工作。我曾以为我的服务器确实内存不足，但现在，我又看到了这个问题。 SQL Server 仅使用 300MB RAM，而我的服务器有 16GB RAM。这听起来不可能，因为我的内存不足。

现在，我想收集有关这个问题的更多信息。我可以打开并查看任何日志吗？欢迎任何有助于解决此问题的建议。

我已经运行了一些 SQL 查询。

SELECT * from sys.dm_clr_properties
=============================================
directory   C:\Windows\Microsoft.NET\Framework64\v2.0.50727\
version v2.0.50727
state   CLR is initialized

。

SELECT * from sys.dm_clr_appdomains
======================================================
0x0000000087160240  3   mydatabase.dbo[runtime].2   2011-08-12 08:44:08.940 10  1   E_APPDOMAIN_SHARED  1   1

。

SELECT * from sys.dm_clr_tasks
======================================================
0x000000008185A080  0x00000000818562C8  0x0000000000000000  E_TASK_ATTACHED_TO_CLR  E_ABORT_NONE    E_TYPE_ADUNLOAD 0   0
0x00000000818CE080  0x00000000818CA2C8  0x0000000000000000  E_TASK_ATTACHED_TO_CLR  E_ABORT_NONE    E_TYPE_FINALIZER    0   0
0x0000000081AD4C30  0x000000000400D048  0x0000000000000000  E_TASK_ATTACHED_TO_CLR  E_ABORT_NONE    E_TYPE_USER 0   0

。

SELECT * from sys.dm_clr_loaded_assemblies
<returns nothing>

* 更新*

在我的 SQL Server 上，我创建了四个数据库。它们每个都附有相同的组件。现在，SQL Server 拒绝加载程序集并给出了上述错误。

SELECT * from sys.dm_clr_appdomains 显示当时只加载了一个应用程序域，而 SELECT * from sys.dm_clr_loaded_assemblies 显示根本没有加载任何程序集。

然后，我在其他三个数据库上运行相同的存储过程。它工作并成功加载程序集并成功运行存储过程。执行存储过程后。 SELECT * from sys.dm_clr_appdomains 现在显示仅加载了四个应用程序域，SELECT * from sys.dm_clr_loaded_assemblies 显示现在加载了三个程序集。

这是有道理的。现在，我希望如果我在原始数据库中再次运行存储过程，它应该按原样加载程序集。你猜怎么着。不，事实并非如此。它仍然给我同样的错误。看起来这个数据库完全卡住了。修复此问题的唯一方法是重新启动 SQL Server。我希望系统表中的某处有一个标志/锁来支持这一点。我找不到它。欢迎任何想法。

现在，我的 SQL Server 处于需要我重新启动才能使其再次工作的状态。

* UPDATE (8/31/2011) *

听起来好像与数据库的数据库所有者有关。这有点复杂。我们有两个站点和两个 AD 森林。 SQL Server 计算机已加入林 A，但数据库所有者来自林 B。林 A 和林 B 之间的连接不太稳定，因为它们位于通过 WAN 物理连接的两个不同站点。

一旦我将数据库所有者更改为 SQL 登录（非 Windows 帐户），我的存储过程到目前为止将运行几周，没有中断。

如果有人能解释的话我会接受答案。

Answer 1

具有 EXTERNAL_ACCESS 的程序集通过一些复杂的路径落入 EXECUTE AS 路径下。当“dbo”无法映射到有效登录名时，就会出现此问题。 dbo 的登录名是 SID 为 中的 owner_sid 值的登录名sys.databases。除非在 CREATE DATABASE 中使用了 AUTHORIZATION 子句，否则owner_sid 是发出 CREATE DATABASE 语句的主体的登录 sid。大多数情况下，这是登录并发出 CREATE DATABASE 的用户的 Windows SID。有了这些知识，我们就可以很容易地预见到可能出现的问题：

• 复制数据库：CREATE DATABASE 是由 A 本地用户（即 MachineA\user 或 DomainA\ user），然后数据库被复制到机器 B（通过备份/恢复或通过文件复制）。 owner_sid 通过文件复制以及备份/恢复来保留，这在机器 B 上owner_sid 无效。所有需要 EXECUTE As 的操作都会失败，包括从数据库加载程序集。
• 墓碑帐户。 CREATE DATABASE 是由已离开公司的用户发出的。 AD 帐户被删除，EXECUTE AS 突然神秘地失败，包括加载程序集。
• 断开的笔记本电脑。当笔记本电脑连接到工作网络时，创建数据库会出现问题。在家里，您可以使用 Windows 缓存的凭据登录，但 EXECUTE AS 想要连接到不可用的 AD 并且失败。加载程序集也会失败。第二天上班时，当你再次陷入AD时，问题就会神秘地自行解决。
• AD 连接不稳定。 EXECUTE AS 不使用系统缓存的凭据，并且每次都会连接到 AD。如果 AD 连接存在问题（超时、错误），这些问题会在 EXECUTE AS 中表现为类似的超时和错误，包括加载程序集。

所有这些问题都可以通过简单运行来诊断：EXECUTE AS USER = 'dbo'; 在问题数据库的上下文中。如果它因错误而失败，则程序集加载问题的原因是 dbo 的 EXECUTE AS 上下文。

解决方案很简单，只需强制 owner_sid 进行有效登录即可。 sa 通常是最好的候选者：

ALTER AUTHORIZATION ON DATABASE::[<dbanme>] TO sa;

有趣的是，数据库可能看起来非常健康；表可用，您可以运行选择、更新、删除、创建和删除表等。只有某些组件需要 EXECUTE AS：

• 代码签名要求代码具有 EXECUTE AS 子句程序
• 集验证
• 显式 T-SQL 代码中的 EXECUTE AS
• Service Broker 消息传递（包括查询通知）

后者是最常见的罪魁祸首，因为应用程序依赖于 SqlDependency 全部突然似乎停止工作，或者出现随机问题。本文解释了 SqlDependency 最终如何依赖于 EXECUTE AS：神秘的通知

Answer 2

我经历过。似乎当您恢复数据库 TRUSTWORTHY 设置为 OFF 时。所以我的解决方案是打开它：

ALTER DATABASE [myDB] SET TRUSTWORTHY ON
GO

打开它后，我的触发器和存储过程开始像以前一样工作。

Answer 3

以防万一有人遇到这个问题，对我有用的解决方案是：

ALTER AUTHORIZATION ON DATABASE::[mydb] TO sa;

然后

ALTER DATABASE [mydb]  SET TRUSTWORTHY ON;

我使用管理员帐户恢复我的数据库，除了这两个调用的组合之外，没有其他方法对我有用。

将 [mydb] 替换为 [yourdatabasename]

Answer 4

在我的情况下，需要进行一些组合，其中我从不同的服务器复制了数据库，并且创建数据库的用户不在新服务器上。

myDB 是我尝试访问的数据库
validDbUser 是我希望将移植数据库的所有权更改为的新数据库服务器上的用户名。

USE myDB
GO
ALTER DATABASE [myDB] SET TRUSTWORTHY ON
GO
EXEC sp_changedbowner [validDbUser]

Answer 5

我将数据库从服务器恢复到本地计算机并遇到此错误。尝试以下两个查询。对我来说，第一个查询有效：

--First Query

ALTER DATABASE [database_name] SET TRUSTWORTHY ON;
GO

USE [database_name]
GO

EXEC sp_changedbowner 'sa'
GO

--Second Query
-- 如果设置为 false，则启用 CLR 集成

IF ((SELECT [value] FROM sys.configurations WHERE [name] = 'clr enabled') = 0)
BEGIN
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
END
GO

-- 如果设置为 true，则禁用 CLR 严格安全性

IF EXISTS(SELECT 1 FROM SYS.CONFIGURATIONS WHERE name = 'clr strict security' AND [value] = 1)
BEGIN
IF EXISTS(SELECT 1 FROM SYS.CONFIGURATIONS WHERE name = 'show advanced options' AND [value] = 0)
BEGIN
EXEC sp_configure 'show advanced options', 1
RECONFIGURE 
END

EXEC sp_configure 'clr strict security', 0
RECONFIGURE 
END
GO
GO

Answer 6

当我们尝试在运行 SQL Server 2017 的新服务器上更新空间列时，我们看到了此错误。

感谢我们客户公司的 IT 主管，他发现：

Sql 2017 为 CLR 引入了新的信任规则（SQL 2012 不是
问题）...即使是“安全”的 CLR 也必须经过签名（这个 dll
不是）或者您必须强制信任，如下所示：

DECLARE @clrName nvarchar(4000) = 'sqlspatialtools, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil'

DECLARE @asmBin varbinary(max) = 'PUT THE BINARY STRING HERE (GET FROM SCRIPTING CREATE TO FOR THE EXISTING ASSEMBLY'

DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES('SHA2_512', @asmBin);

EXEC sys.sp_add_trusted_assembly @hash = @hash, @description = @clrName;

这为我们解决了问题。

Answer 7

我有同样的错误并注意到出了什么问题：
查看您的程序集 ID！
它是 65536 - 我的是 65538

看来程序集 ID 是用 16 位整数编码的。
因此，“服务器可能耗尽资源”具有逻辑意义。

在我看来，微软的错误。
如果您找到比重新启动或重新启动服务更好的方法，请告诉我！ :)

Answer 8

就我而言（SQL 2019 CU10），由于自 SQL 2017 以来引入的 CLR 严格安全功能，发生了错误。您可以在此处阅读有关此内容的更多信息：CLR 严格安全

简而言之，解决方案是禁用该功能，以便可以加载未签名的程序集。

exec sp_configure 'clr strict security', 0
go
reconfigure
go

另外，禁用该功能后我必须重新启动 sql server 引擎。由于某种原因，更改并未立即生效。

我知道更好的解决方案是对程序集进行签名，但在我的情况下这是不可能的，这个解决方案让我有时间要求开发人员提供签名的程序集。

Answer 9

此消息可能与新实例上 SQL 用户帐户的权限有关。

1. 确保服务器上安装了 SQL Server Management Studio。
2. 使用 Windows 用户帐户或 SQL 用户登录到新实例 (Servername\Acctivate)。
3. 对于存在以下问题的数据库运行以下脚本......

使用 ;

EXEC sp_configure 'clrenabled' ,1

GO

USE

GO

EXEC sp_changedbowner 'sa'

USE

GO

ALTER DATABASE SET TRUSTWORTHY ON;

你完成了...！

Answer 10

我的问题是数据库恢复是在 SQL Server 上使用 Windows 身份验证执行的！删除数据库，使用 sa 登录，再次恢复数据库并设置 TRUSTWORTHY ON，解决了我的问题！

Answer 11

您在程序集中引用了哪些命名空间？ SQL Server 仅正式支持少数 .net 提供的参考资料。

我在引用 System.DirectoryServices （不受支持）时看到了完全相同的问题。我们有一个 clr 表值函数，可以在一周左右的时间内正常工作，然后突然出错。重新部署或回收服务可以暂时解决该问题。

确保所有命名空间引用都受支持。否则，您可能会关闭数据库。

Answer 12

这很奇怪。我遇到了同样的问题，但我通过运行快速查询确认 dbo 帐户有效：
选择“测试”作为测试
作为用户执行 = 'dbo'
我还验证了 Trustworthy 已设置为 True。

修复我的盒子的方法是将“程序集所有者”从 dbo 更改为我自己的用户，然后再更改回 dbo。

Answer 13

我发现了同样的问题。就我而言，CLR 程序集是针对 x86 cpu 编译的。在我将 cpu 更改为任何 CPU 后，此问题得到解决，因为我的 SQL 服务器是 64 位。

我希望这有用。

Answer 14

这对我有用。请注意，直到我将数据库所有者更改为“sa”后，它才起作用。

use MyDatabaseName
go

-- Ref. https://stackoverflow.com/questions/6145697/asp-net-clr-not-enabled
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

ALTER DATABASE MyDatabaseName SET TRUSTWORTHY ON;



-- Ref. https://stackoverflow.com/questions/7034969/sql-server-stops-loading-assembly
EXEC sp_changedbowner 'sa'
GO

Answer 15

我怀疑您没有在程序集中处理 SqlConnection 和 SqlCommand 实例，这就是它耗尽资源的原因。要么就是内存泄漏，你能发布代码吗？