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.

Answer 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

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

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

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

Answer 5

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

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

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:

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.

Answer 6

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

Answer 7

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

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

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

Answer 8

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!

Answer 9

This message is likely related to permissions to the SQL user account on the new instance.

1. Ensure that SQL Server Management Studio is installed on the server.
2. Login into the new instance (Servername\Acctivate) using a windows user account or a SQL User.
3. Run the following script below FOR THE DATABASE HAVING ISSUES WITH….

USE ;

EXEC sp_configure 'clr enabled' ,1

GO

USE

GO

EXEC sp_changedbowner 'sa'

USE

GO

ALTER DATABASE SET TRUSTWORTHY ON;

YOU ARE DONE...!

Answer 10

In my case (SQL 2019 CU10) the error was happening because of the CLR strict security feature introduced since SQL 2017. You can read more about this here: CLR strict security

In short the solution is to disable that feature so non-signed assemblies can be loaded.

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

Also, I had to restart the sql server engine after disabling the feature. For some reason the change did not take effect immediately.

I understand that a better solution is to sign the assembly but in my case that was not possible and this solution gave me time to ask the developers to provide signed assemblys.

Answer 11

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.

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

Answer 12

I found the same issue. In my case the CLR assembly was compiled for x86 cpu. After I changed the cpu to ANY CPU this issue was resolved since my SQL server is 64 bits.

I hope this useful.

Answer 13

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

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.

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

Answer 14

I suspect you are not disposing your SqlConnection and SqlCommand instances inside your assembly, which is why it's running out of resources. Either that or it has a memory leak, can you post the code?