.net SQL Server 停止加载程序集

SQL Server stops loading assembly

提问by Harvey Kwok

We have developed an assembly for SQL Server 2008 R2.

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

The assembly has been working for a week. The managed stored proc inside the assembly was working fine for the whole week and then it stops working. We have been seeing this problem couple times. The way to make it work again is to restart the SQL Server.

大会已经工作了一个星期。程序集中的托管存储过程在整个星期内都运行良好，然后停止工作。我们已经多次看到这个问题。让它再次工作的方法是重新启动 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)

I have found different articles on the web.

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

This KBsuggested that I might have restored the database from another SQL Server, which I swear I didn't.

这个 KB建议我可能已经从另一个 SQL Server 恢复了数据库，我发誓我没有。

This blogsaid I might run into this if I installed .NET 3.5 on SQL Server 2005 but mine was SQL Server 2008 R2 and I did not install anything when this problem occurs.

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

The main point is that it can keep going for a period of time. It just stops working randomly. Then, if we restart the SQL Server, it will start working again. I have thought of my server was really running out of memory but now, I just see the problem again. SQL Server is using 300MB RAM only and my server has 16GB RAM. This sounds impossible that it's because I am running out of memory.

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

Now, I want to collect more information on this problem. Any log that I can turn on and look at? Any suggestion that help troubleshooting this problem is welcome.

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

I have run some SQL queries.

我已经运行了一些 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>

* UPDATE *

* 更新 *

On my SQL Server, I have created four databases. Each of them with the same assembly attached to it. Now, SQL Server refused to load the assembly and gave me the above error.

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

SELECT * from sys.dm_clr_appdomainsshows me at that point there was only one appdomain loaded and SELECT * from sys.dm_clr_loaded_assembliesshowed me there were no assemblies loaded at all.

SELECT * from sys.dm_clr_appdomains向我展示了当时只加载了一个应用程序域，并SELECT * from sys.dm_clr_loaded_assemblies告诉我根本没有加载任何程序集。

Then, I ran the same stored proc on the other three databases. It worked and successfully loaded up the assemblies and successfully ran the stored proc. After executing the stored proc. SELECT * from sys.dm_clr_appdomainsnow shows me there are only four appdomain loaded and SELECT * from sys.dm_clr_loaded_assembliesshowed me there are now three assemblies loaded.

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

This makes sense. Now, I hope if I run the stored proc again in the original database, it should get the assembly loaded as it were. Guess what. No, it doesn't. It still gives me the same error. It looks like this database is completely stuck. The only way to fix it is to reboot the SQL Server. I am hoping there is a flag/lock somewhere in the system table holding up this. I cannot find it. Any idea is welcome.

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

Now, my SQL Server is in the state that requiring me to reboot to make it work again.

现在，我的 SQL Server 处于需要我重新启动才能再次运行的状态。

* UPDATE (8/31/2011) *

* 更新 (8/31/2011) *

It sounds like it's related to the database owner of the database. This is kind of complicated. We have two sites and two AD forests. The SQL Server machine is joined to forest A but the database owner is from forest B. The connection between forest A and forest B is not that stable since they are in two different sites physically connected by WAN.

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

Once I change the database owner to a SQL Login (Non-Windows account), my stored proc is up running for couple weeks so far with no interruption.

一旦我将数据库所有者更改为 SQL 登录名（非 Windows 帐户），我的存储过程就已经运行了几个星期，没有中断。

I will accept the answer if anybody can explain it.

如果有人可以解释，我会接受答案。

回答by Remus Rusanu

Assemblies with EXTERNAL_ACCESS are, through some convoluted path, falling under the EXECUTE AS path. The problem appears when the 'dbo' cannot be mapped to a valid login. dbo's login is the login with the SID the owner_sidvalue in sys.databases. Unless an AUTHORIZATION clause was used in CREATE DATABASE the owner_sid is the login sid of the principal issuing the CREATE DATABASE statement. Most times this is the Windows SID of the user logged in and issuing the CREATE DATABASE. With this knowledge in hand one can easily envision the problems that may arise:

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

• copy database: CREATE DATABASE was issued on machine A by an user local to A (ie. MachineA\useror DomainA\user) then the database was copied to machine B (via backup/restore or via file copy). The owner_sid is preserved by file copy as well as by backup/restore, this on machine B the owner_sid is invalid. Everything requiring EXECUTE As fails, including loading assemblies from the database.
• tombstoned account. CREATE DATABASE was issued by an user that has left the company. The AD account is deleted and all of the sudden EXECUTE AS mysteriously fails, including loading assemblies.
• disconnected laptop. CREATE DATABASE was issues when the laptop was connected in the work network. At home you can log in using Windows cached credentials, but EXECUTE AS wants to connect to the unavailable AD and fails. Loading assemblies also fails. Problems mysteriously resolves itself next day at work, when you're again within reach of AD.
• spotty AD connectivity. The EXECUTE AS does not uses system cached credentials and connects to the AD every time. If the AD connectivity has issues (timeout, errors) those issues manifest as similar timeouts and errors in EXECUTE AS, including loading assemblies

• 复制数据库：CREATE DATABASE 由 A 本地用户（即MachineA\user或DomainA\user）在机器 A 上发出，然后数据库被复制到机器 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 中表现为类似的超时和错误，包括加载程序集

All these issues can be diagnosed by simply running: EXECUTE AS USER = 'dbo';in the context of the problem db. It it fails with an error then the cause of your assembly load problems is the EXECUTE AS context of dbo.

所有这些问题都可以通过简单地运行来诊断：EXECUTE AS USER = 'dbo';在问题 db. 如果它失败并出现错误，则程序集加载问题的原因是dbo.

The solution is trivial, simply force the owner_sidto a valid login. sais the usually the best candidate:

解决方案很简单，只需强制owner_sid登录有效即可。sa通常是最佳人选：

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

The funny thing is that the database may seem to be perfectly healthy; tables are available and you can run selects, updates, deletes, create and drop tables etc. Only certain components require EXECUTE AS:

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

• code signing requires the code to have an EXECUTE AS clause
• assembly validation
• explicit EXECUTE ASin T-SQL code
• Service Broker message delivery (including Query Notifications)

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

The latter is the most often seen culprit, as applications relying on SqlDependencyall of a sudden seem to stop working, or have random problems. This article explains how SqlDependencyultimately depends on EXECUTE AS: The Mysterious Notification

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

回答by Ehsan Mirsaeedi

I experienced it. it seems when you restore a database TRUSTWORTHY set to OFF. so my solution was to turn it on :

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

ALTER DATABASE [myDB] SET TRUSTWORTHY ON
GO

and after i turned it on, my triggers and stored procedures started to work like before.

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

回答by mahonya

Just in case someone comes across this problem, the solution that worked for me was:

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

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

followed by

其次是

ALTER DATABASE [mydb]  SET TRUSTWORTHY ON;

I am restoring my db with the Administrator account, and nothing else other than the combination of these two calls has worked for me.

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

Substitute [mydb] for [yourdatabasename]

将 [mydb] 替换为 [yourdatabasename]

回答by sweetfa

A combination of things were required in my case where I had copied a database from a different server and the user who created the database was not present on the new server.

在我从不同的服务器复制数据库并且创建数据库的用户不在新服务器上的情况下，需要综合考虑。

myDB is the database that I am trying to access validDbUser is the user name on the new database server that I wish to change the owner ship of the transplanted database to.

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

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

回答by Ian Grainger

We saw this error when trying to update spatial columns on a new server which was running SQL Server 2017.

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

Credit to the head of IT at our client company who found out that:

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

Sql 2017 introduced new trust rules for CLR (SQL 2012 wasn't a problem)... Even 'safe' CLR has to have been signed (which this dll isn't) or you have to force the trust as below:

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;

This fixed the issue for us.

这为我们解决了问题。

回答by Hugues

I have the same error and noticed what is wrong : Have a look at your assembly ID ! It is 65536 - mine is 65538

我有同样的错误，并注意到什么是错误的：看看你的程序集 ID！它是 65536 - 我的是 65538

It seems the assembly ID is coded on a 16bits integer. So, "server may be running out of resources" takes a logical sense.

似乎程序集 ID 编码为 16 位整数。因此，“服务器可能耗尽资源”是合乎逻辑的。

Microsoft bug, in my opinion. If you have find a better way than reboot or restart the service, please let me know! :)

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

回答by Satyabrata Biswal

I restored the DB from server to my local machine and ran into this error. Try the below two queries. For me, the first query worked:

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

--First Query

--第一次查询

ALTER DATABASE [database_name] SET TRUSTWORTHY ON;
GO

USE [database_name]
GO

EXEC sp_changedbowner 'sa'
GO

--Second Query -- Enabling CLR Integration if it is set to false

--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

-- Disabling CLR strict security, if it is set to true

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

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

回答by 100r

Problem in my case was that DB restore was executed with Windows Authentication on SQL Server! Droping DB, loging in with sa, restoring DB again and setting TRUSTWORTHY ON, solved my problem!

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

回答by regeter

This is weird. I had the same issue but I confirmed that the dbo account was valid via running a quick query: SELECT 'TEST' AS Test EXECUTE AS USER = 'dbo' I also verified that Trustworthy was set to True.

这很奇怪。我遇到了同样的问题，但我通过运行快速查询确认 dbo 帐户有效： SELECT 'TEST' AS Test EXECUTE AS USER = 'dbo' 我还验证了 Trustworthy 设置为 True。

What fixed it for my box was changing the "assembly owner" from dbo to my own user and afterwards back to dbo.

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

回答by brian

What namespaces are you referencing in the assembly? SQL Server only officially supports a handfulof the references that .net has available.

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

I've seen the exact same issue when referencing System.DirectoryServices (unsupported). We had a clr table valued function that would work great for a week or so and then, all of the sudden, would error. A redeploy or recycle of the service would temporarily fix the issue.

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

Make sure all of your namespace references are supported. Otherwise, you can potentially bring down the database.

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