Create assembly in SQL Server from C# dll

Question

I write c# code with these references:

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Net;

I build this in a dll file that I want to use to create a assembly in sql server:

exec sp_configure 'advanced options' , 1;
reconfigure

exec sp_configure 'clr enabled' , '1' 
go 
reconfigure

--step 1
USE master;
GO
CREATE ASYMMETRIC KEY CLRSign FROM EXECUTABLE FILE = 'C:\Temp\GetInfluxdbData.dll';
GO

--step 2
USE master;
GO
CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY CLRSign;
GO

--step 3
USE master;
GO
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRLogin;
GO

--step 4
USE EricTst;
GO
CREATE USER CLRLoginEric FOR LOGIN CLRLogin;
GO

--step 5
USE EricTST;
GO
CREATE ASSEMBLY SqlSelectDLL FROM 'C:\Temp\GetInfluxdbData.dll' WITH PERMISSION_SET = SAFE;
GO

I can run every step, except step 5 that gives me a error:

Msg 10301, Level 16, State 1, Line 76
Assembly 'GetInfluxdbData' references assembly 'system.runtime.serialization, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', 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(Het systeem kan het opgegeven bestand niet vinden.)). Please load the referenced assembly into the current database and retry your request.

Completion time: 2021-01-04T17:28:19.8314490+01:00

Do I have to add every assembly to sql server?

Answer 1

I finally find the solution, thanks for the help but the answers was not here.

Answer 2

Hello @Eric Bezemer

You need to add references (as SQL-Server is not aware of them) as follows and setup permissions after selecting the DLL's

See also

3rd party dll in SQL Server CLR in regards to permissions

Answer 3

Yes, if the assemblies needed for your assembly are not in SQL Server by default, you need to load them. And if you get warnings like this - it may be reason to reconsider your original plans.

You don't have to set the database trustworthy (and you shouldn't). You can use sp_add_trusted_assembly to add a hash for the assembly.

Then again, if you have to enter more assemblies, not originally intended to be part of SQL Server, I think you even can run into those that are explicitly block. And in such case, it is definitely time for plan B.

Answer 4

Hi

creating-an-assembly

Best Regards.
Please remember to mark the replies as answers if they help.