Creating custom assembly in SQL Server throwing assembly not found in database error

Question

I am trying to create an assembly for a third party dll (developed in .net) in SQL Server 2008 for CLR procedure project,

create assembly [`XXXX.XXX.XXX.dll`]  
authorization dbo  
from 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\XXXX.XXX.XXX.dll'  
with permission_set = unsafe

But I'm getting this error while executing the above script:

Assembly 'XXX.XXX.XXX.XXX' references assembly 'XXX.XXX.XXXXX', version=0.0.0.0, culture=neutral, publickeytoken=null.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

I appreciate your help in solving this issue

Solution 1

You can add Assemblies to your database which is used by you CLR.

I have added for example System.Messaging.dll to SQL Server 2014 as follows

CREATE ASSEMBLY [System.Messaging]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE;
GO 

It will add the neccessary other referenced assemblies linked to that one. After that, I added Newtonsoft.Json.dll like this:

CREATE ASSEMBLY [Newtonsoft.Json]
FROM 'C:\Users\RiaanDL\Downloads\Json60r3\Bin\Net45\Newtonsoft.Json.dll'
WITH PERMISSION_SET = UNSAFE;
GO

Just remember to set your SQLCLR Permission level as UNSAFE in Project Configuration.

Solution 2

Assemblies in SQL Server cannot reference assemblies from the GAC unless they are on the 'blessed' list. That list is as follows:

• Mscorlib.dll
• System.Data.dll
• System.dll
• System.Xml.dll
• System.Security.dll
• System.Web.Services.dll
• System.Data.SqlXml.dll
• System.Transactions.dll
• System.Configuration.dll
• Microsoft.VisualBasic.dll
• Microsoft.VisualC.dll
• CustomMarshalers.dll
• System.Data.OracleClient.dll

Any other assemblies need to be created within SQL Server using the CREATE ASSEMBLY syntax - even those from the .NET Framework itself that are not on the list above.

Solution 3

I know this question has been asked long back but I would like to share my findings and solutions to it so that it could help someone.

In order to create assembly be it custom or native assembly from .NET Framework, you need set TRUSTWORTHY ON for the database.

ALTER DATABASE [DATABASE NAME] SET TRUSTWORTHY ON;

After this, you may execute the below script for creating an assembly. In my example, I'm trying to create assembly for "System.Net.Http"

CREATE ASSEMBLY [System.Net.Http]
FROM
   'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'
WITH PERMISSION_SET = UNSAFE;

For your case, you need to point your customer assembly (dll) location correctly to make this work.

Note: There are only few supported assembly which can be found here. If anything other than specified assembly are used then you will get an Warning as shown below. In my example, when I tried to create assembly for [System.Net.Http], I got below Warning.

The Microsoft .NET Framework assembly 'system.net.http, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Hope this helps.

Comments

[Ardalan Shahgholi]

Thanks a lot. This part should have been written by gold (SQLCLR Permission level as UNSAFE in Project Configuration)