SQL Server stops loading assembly

Question

We have developed an assembly for 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.

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 KB suggested that I might have restored the database from another SQL Server, which I swear I didn't.

This blog said 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.

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.

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.

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.

SELECT * from sys.dm_clr_appdomains shows me at that point there was only one appdomain loaded and SELECT * from sys.dm_clr_loaded_assemblies showed me there were no assemblies loaded at all.

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_appdomains now shows me there are only four appdomain loaded and SELECT * from sys.dm_clr_loaded_assemblies showed me there are now three assemblies loaded.

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.

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

* UPDATE (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.

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.

I will accept the answer if anybody can explain it.

Solution 1

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_sid value 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:

• copy database: CREATE DATABASE was issued on machine A by an user local to A (ie. MachineA\user or 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

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.

The solution is trivial, simply force the owner_sid to a valid login. sa is the usually the best candidate:

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:

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

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

Solution 2

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

ALTER DATABASE [myDB] SET TRUSTWORTHY ON
GO

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

Solution 3

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]

Solution 4

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.

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

Solution 5

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

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

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

View more solutions

Comments

[Harvey Kwok]

Nah, I did not register any unsupported assembly to our SQL Server. There is one and only one assembly registered by me. That's the assembly I created for my stored proc. The security level for that assembly was set to "EXTERNAL_ACCESS".

[Harvey Kwok]

Thanks for your suggestion and sorry, it's basically impossible to post the code here. We have around 40 stored proc and the code there is not simple at all. I hear you. It's possible that we may have some leak and therefore got that running out of resources error. We have made our MemToLeave setting (the memory SQL server used for .NET, linked server and some other stuff) to 1GB. At the time it fails, I checked the memory, SQL Server didn't use up 1GB memory at all. We concluded that it's related to the bad link and we are using a remote forest domain user as the database owner.

[Harvey Kwok]

I think you are the one who give the best answer. I will try out EXECUTE AS USER = 'dbo' next time I run into the problem. I believe this is the issue.

[Paul Hutchinson]

EXECUTE AS USER = 'dbo'; Works perfectly well for me. However your suggested cure: ALTER AUTHORIZATION ON DATABASE::[<dbanme>] TO sa; Worked like a charm - so thanks :)

[Remus Rusanu]

@PaulHutchinson: hm... that is interesting and kind of ruins my understanding of what's going on. Good to know it helped you, but I'll need to do some future digg to understand your case...

[Paul Hutchinson]

@RemusRusanu The Server was going awfully slowly yesterday, I gave it a restart this morning - and this error cropped up. My guess is that something got corrupted during the restart (happened before on this server). It's an old beast and the client is moving to a new solution soon.

[mbx]

Sounds like a guess, not like an answer. Have you found any other hints to prove your theory?

[Auguste Van Nieuwenhuyzen]

Microsoft apparently recommends you don't do this. docs.microsoft.com/en-us/sql/database-engine/configure-windo‌​ws/…. And you should add permissions for each assembly as per my answer below (though credit for finding it doesn't go to me!)

[Animesh]

thanks @RemusRusanu "ALTER AUTHORIZATION ON DATABASE::[<dbanme>] TO sa;" works like charm..

[Eellor]

No, it's not true. Run EXEC sp_help 'sys.assemblies' You'll find assembly_id column is a 4 byte int. Instead, if you select from sys.assemblies, you'll find all user defined assemblies have ids bigger than 65535. An educated guess is SQL Server has reserved ids up to 65535 for system assemblies.

[Greg]

Thanks. This broke for us when we tried restoring a database on 2017.

[Marcos Meli]

You save us the day !! Thanks dude !!

[Zeek2]

I got this error when I tried to execute the above: "The configuration option 'clr strict security' does not exist, or it may be an advanced option."

[Zeek2]

However, changing the database owner to "sa" just did the trick for me :) [Note: that prior to that I had enabled clr & set the database to trustworthy.

[Anthony Griggs]

+1 for restart the server. I had tried this method from other suggestions above but restarting the server is what actually kicked it in.