Sql-server – Error 0x80131904 failed to load assembly id 65536 / HRESULT: 0x8013150A for microsoft.sqlserver.integrationservices.server

azure-sql-databasesql serversql-clrsql-server-2017

I am running SSIS Packages through an Agent job and getting error 0x80131904:

Message:

Executed as user: NT Service\SQLSERVERAGENT.
Microsoft (R) SQL Server Execute Package Utility
Version 14.0.3023.8 for 64-bit
Copyright (C) 2017 Microsoft. All rights reserved.

Started: 1:00:00 PM
Failed to execute IS server package because of error 0x80131904. Description:
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. 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 'microsoft.sqlserver.integrationservices.server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException:
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

Source: .Net SqlClient Data Provider
Started: 1:00:00 PM
Finished: 1:00:00 PM
Elapsed: 0.328 seconds.

The package execution failed.
The step failed.

I researched and found that I should enable CLR and set TRUSTWORTHY to ON for the DB. I did that and in addition checked the database owner account; it's enabled and not orphaned. Still getting the error. I would highly appreciate any help on this.

Best Answer

Have you tried this:

USE <DATABASE>;
EXEC sp_configure 'clr enabled' ,1
GO

RECONFIGURE
GO
EXEC sp_configure 'clr enabled'   -- make sure it took
GO

First Enable Trustworthy database property

ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON;
GO

And change the database owner to ‘sa’

USE [DatabaseName]
GO

EXEC sp_changedbowner 'sa'
GO

Related Solutions

SQL Server – SQLCLR Assembly Error with Multiple Simultaneous Queries

The type of error that you are getting (i.e. Collection was modified;) is most likely caused by sharing the collection across multiple threads. If the variable holding the collection is defined as static, then that variable is not thread-safe since all sessions share the same App Domain for all Assemblies in a particular Database that are owned by the same User.

And, judging by the class name of StringMetrics, the namespace of SimMetricsMetricUtilities, and the input parameter names, I am guessing that you are using the SQLCLR port recommended in the "News" section of the SimMetrics repository (please note: I am being intentionally vague in not linking to this "News" page or mentioning the title of the blog post containing this particular SQLCLR port since, in its current form, it should not be used due to incorrect usage of the SQLCLR API in a few areas). The main problem with that code (given just a quick review) is that it instantiates static classes for each algorithm. Hence you are sharing those collections across sessions, and even with the algorithm being quick, you are now calling the methods frequently enough to have those sessions (i.e. threads) stepping on each other. Lucky for you that Generic Lists throw these errors.

You might be able to fix this by:

getting rid of the static class constructor
getting rid of the static readonly variables
adding the following line just above the return in the QGramsDistance method in the StringMetrics class:
```
QGramsDistance _QGramsDistance = new QGramsDistance();
```
Yes, it needs to be instantiated with each execution of the SQLCLR UDF.
In the SqlFunction attribute, change IsPrecise to be false. Using the FLOAT datatype is specifically imprecise ;-).

Changes #1 and #2 only need to be done once to cover the functions/methods for all of the algorithms. Change #3 should be done across the rest of them using the class name that is appropriate for each particular method. And Change #4 should be done across all of those SqlFunction methods, and is the exact same change across all of them.

Sql-server – .NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

An error in the form of:

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id #####. 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 '{_assembly_name_}, Version=#.#.#.#, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxx' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

means that the Assembly, which is currently marked with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE is not allowed to make use of that permission level until the second part of the SQLCLR permissions setup is taken care of. That second part is to do one of the following:

The very much preferred approach
1. Sign the Assembly when compiling it (and give it a password!). This is sometimes referred to as giving it a Strong Name.
  
  If the Assembly isn't signed, and you don't have the source code to recompile it, and it doesn't have any other Assemblies that reference it, then you can still sign it by following the instructions in the first half of this blog post: http://ianpicknell.blogspot.com/2009/12/adding-strong-name-to-third-party.html
2. Create an Asymmetric Key in [master] from the DLL of that Assembly
3. Create a Login from that Asymmetric Key
4. Grant that Login one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY (the UNSAFE ASSEMBLY permission also allows Assemblies to be marked as EXTERNAL_ACCESS, so you don't need both permissions)
5. Create the Assembly in whichever Database(s) it should exist in
6. Do NOT set the TRUSTWORTHY property of the Database(s) containing this Assembly to ON. TRUSTWORTHY can remain as OFF.
The NOT preferred approach
1. Alter the Database(s) where the Assembly should exist to be TRUSTWORTHY ON
2. Make sure that the Login of the owner of the Database(s) containing this Assembly have one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY.
  
  If the owner of the Database(s) is sa, or any other Login that is in the sysadmin fixed server Role, then you don't need to worry about having one of these two permissions explicitly set as they are implied by the sysadmin Role.
3. Try to avoid this approach, if at all possible :-)

Best Answer

Related Solutions

SQL Server – SQLCLR Assembly Error with Multiple Simultaneous Queries

Sql-server – .NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

Related Question