Unable to load CLR assembly intermittently

  • Article

Recently, I worked with a customer on an CLR assembly loading issue.

Intermittently, they would receive the following error.

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. 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 'helloworld, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. Exception from HRESULT: 0x80FC80F1
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.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)

 

Our initial focus was on the database that has the CLR assembly.  Per KB https://support.microsoft.com/kb/918040, the database that has the CLR assembly and if the CLR assembly has external_access or unsafe permission set, SQL Server checks to ensure the dbo's sid is a valid sid in sys.server_principals and matches sys.databases.

We changed the database's owner to sa which is gauranteed to match.

But after that, customer continued to receive the error intermittently.    Thru debugging, we discovered that SQL Server also checks on the dbo's sid for database under which the query was compiled if the assembly is not loaded already.  As it turned out, customer had many databases.  they would write queries in those databases but also reference a centralized CLR resource database.   Some of the databases had mismatching or orphaned sid (resulting from restore).  If the CLR assembly isn't loaded, any queries referencing the CLR assembly from those databases will  raise above error as well.   Once assembly is loaded, the check won't be done.  Most of the other databases that use the CLR objects have valid sids for dbo.   So if it happens that a query references the CLR assembly runs first from those 'good' databases, it will trigger the CLR assembly to be loaded and everything will work.

Soltuion:
Ensure the dbo's sid on from every database matches sys.server_principals and sys.databases. We will update the KB mentioned above as well

A demo repro

 

1. This repro uses standard sql login for ease of demonstration

2. Configure your sql server to use both windows and sql authentication.

3. Create a standard login as CLRLogin and grant this login permission so that it can create database

4. Login as CLRLogin and Create a database named NonClrDB

5. Backup the database NonClrDB

6. Drop database NonClrDb and login ClrLogin

7. Re-create ClrLogin. This will generate a different seed

8. Restore database NonClrDb

9. Logon as yourself with enough permission to create another database called ClrDb and set trustworhty on

10. Use ClrDb and Run this script

CREATE ASSEMBLY [HelloWorld]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001DA06C4B0000000000000000E00002210B010800000800000006000000000000CE2600000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000007826000053000000004000009003000000000000000000000000000000000000006000000C000000002600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000D4060000002000000008000000020000000000000000000000000000200000602E72737263000000900300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000B0260000000000004800000002000500742000008C0500000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002001000000001000011007201000070730F00000A0A2B00062A1E02281000000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000D0010000237E00003C0200007802000023537472696E677300000000B40400001C00000023555300D0040000100000002347554944000000E0040000AC00000023426C6F620000000000000002000001471402000900000000FA01330016000001000000110000000200000002000000100000000C00000001000000010000000200000000000A0001000000000006003E0037000A00660051000600930081000600AA0081000600C70081000600E60081000600FF00810006001801810006003301810006004E01810006008601670106009A0181000600C601B3013700DA01000006000902E90106002902E9010A006202470200000000010000000000010001000100100019000000050001000100502000000000960070000A0001006C200000000086187B000F00010019007B00130021007B00130029007B00130031007B00130039007B00130041007B00130049007B00130051007B00130059007B00180061007B00130069007B001D0079007B00230081007B000F0089007B000F0011007B00130009007B000F002000730028002E002B0032002E00130042002E001B0042002E00230048002E000B0032002E00330057002E003B0042002E004B0042002E005B0078002E00630081002E006B008A002D000480000001000000680E1E760000000000007000000002000000000000000000000001002E0000000000020000000000000000000000010045000000000000000000003C4D6F64756C653E0048656C6C6F576F726C642E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670048656C6C6F576F726C64002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500000017480065006C006C006F00200057006F0072006C006400000000008895DD36598FB94681CF27B4C443A44E0008B77A5C561934E089040000110903200001042001010E04200101020520010111390420010108040100000004070111090F01000A48656C6C6F576F726C6400000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000001DA06C4B0000000002000000590000001C2600001C08000052534453907F1962D03D9843AD482AF957AE4FF801000000493A5C63617365735C636C722E6C6F61645C48656C6C6F576F726C645C48656C6C6F576F726C645C6F626A5C44656275675C48656C6C6F576F726C642E70646200000000A02600000000000000000000BE260000002000000000000000000000000000000000000000000000B026000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001001E76680E000001001E76680E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F0066007400000040000B000100460069006C0065004400650073006300720069007000740069006F006E0000000000480065006C006C006F0057006F0072006C0064000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003600380038002E00330030003200330038000000000040000F00010049006E007400650072006E0061006C004E0061006D0065000000480065006C006C006F0057006F0072006C0064002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310030000000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000480065006C006C006F0057006F0072006C0064002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D00650000000000480065006C006C006F0057006F0072006C0064000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003600380038002E00330030003200330038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003600380038002E00330030003200330038000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000D03600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = UNSAFE

 

 

go

 

CREATE FUNCTION [HelloWorld]

 

(

)

RETURNS nvarchar(4000)

AS

EXTERNAL NAME [HelloWorld].[UserDefinedFunctions].[HelloWorld]

 

go

 

11. Use ClrDb and run select dbo.HelloWorld() and ensure it works.

12. Now restart your server. Restarting is necessary because we want it to reload from database. Due to the step above, the CLR assembly is already loaded.

13. Then use nonclrdb and run select clrdb.dbo.HelloWorld(). This will result in error 10314. this is because the nonclrdb has mismatching sid

 

 

Msg 10314, Level 16, State 11, Line 1

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. 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 'helloworld, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. Exception from HRESULT: 0x80FC80F1

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

 

 

 

 

 

 

 

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support