How to fix assembly issue in SQL Server

7 Aug, 2021

How to fix assembly issue in SQL Server

Below steps can be followed to fix assembly issue in SQL Server.

Problem Statement:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65535. 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 'ABCAssembly.sql, Version=0.0.0.0,
Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

Cause of error:

Starting from SQL Server 2017, Microsoft has introduced new configuration option called as "clr strict security" to enhance the security of CLR
assemblies and this option is enabled by default which interprets all assemblies as "UNSAFE". When "clr strict security" option is enabled, any assemblies that are not signed will not load successfully. So if you are on SQL Server version (older than SQL Server 2017) having assemblies and planning to migrate to
to SQL Server 2017 or higher , then assemblies would not get loaded and you would see above error when using assemblies on SQL Server 2017.

Possible Resolution:

To avoid this issue, below are the available options.

To set the database trustworthy property to ON and change the owner of the database to a login that has the "unsafe assembly" permission enabled on the server. But this option is not recommended as per Microsoft.

alter database [DBNAME] set trustworthy on
go
use [DBNAME]
go
exec sp_changedbowner 'sa'
go

2. Disabling configuration option "clr strict security" which provided backward compatibility but it is not recommended by Microsoft.

You will have to recreate the assemblies with a signature of either a certificate or an asymmetric key that has a corresponding login with the UNSAFE ASSEMBLY permission on the server.
use sp_add_trusted_assembly to add assembly to the list of trusted assemblies as per Microsoft.

Note: configuration option "clr enabled" is disabled by default and this need to be enabled for newly created assemblies to work on SQL Server 2017 and later.

Steps to add trusted assemblies:

First we need to find the assembly name and its hash value and supply that to sp_add_trusted_assembly query below.

Query to find assembly name:

use [DBNAME]
go
select * from sys.assemblies where name not like 'microsoft%'

Query to find hash value:

use [DBNAME]
go
select * from sys.assembly_files where name not like 'microsoft%'

Query to add assembly to trusted assemblies of Server:

use master;
go
declare @clrName nvarchar(4000) = ' ABCAssembly.sql ' — put assembly name
declare @asmBin varbinary(max) = 0x4D5A90000300…; — put hash value
declare @hash varbinary(64);

select @hash = hashbytes('SHA2_512', @asmBin);

EXEC sys.sp_add_trusted_assembly @hash = @hash,
@description = @clrName;

once above query is completed fine. you can check added assembly using below command.

select * from sys.trusted_assemblies

Now, end user assembly query should also run fine.