SQLCLR - How can I register referenced assemblies of my CLR assembly without setting TRUSTWORTHY ON?

I'm having issue registering referenced assemblies that I used in my SQL CLR assembly. For example I have SQL CLR assembly named Something.dll. This Something.dll references Newtonsoft.dll.

I sign Something.dll then create asymmetric key to register it with PERMISSION_SET = EXTERNAL_ACCESS. However, I can't sign referenced assembly so SQL Server does not let me register it with UNSAFE or EXTERNAL_ACCESS permissions without setting TRUSTWORTHY ON.

Is there any way to register Newtonsoft.dll without setting TRUSTWORTHY ON?

Solution

You can sign the referenced Assembly(ies) with a Certificate that you create, load that Certificate into SQL Server before you attempt to create the Assembly in SQL Server, create a Login from the Certificate, and grant that Login the desired permission, either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY. And in fact, using this same Certificate for your own Assembly will allow it to be loaded from a VARBINARY literal / hex bytes string instead of requiring the DLL on the file system, which means this solution will work just fine in SQL Server 2017 and newer.

The following steps should do it:

Create the Certificate via command prompt:
```
MAKECERT -r -pe -n "CN=some name,O=your company,C=US" ^
-e "12/31/2099" -sv private_key_file.pvk ^
public_key_file.cer
```
You will be prompted via modal pop-up to enter in a password. You should get 2 prompts. Use the same password both times.

Combine the public key and private key files into a single PFX file via command prompt:

PVK2PFX -pvk private_key_file.pvk -pi password ^
-spc public_key_file.cer ^
-pfx signing_certificate.pfx

Sign the DLL(s) via command prompt:

signtool.EXE sign /v /p password ^
/f signing_certificate.pfx ^
NewtonSoft.dll

signtool.EXE sign /v /p password ^
/f signing_certificate.pfx ^
MyProject.dll

Convert the public key into a VARBINARY literal so that it can be created from a SQL script with no external dependency on the file system, via command prompt (BinaryFormatter is an open source utility I wrote, available on GitHub, that converts the binary file into the hex bytes string, including wrapping at 80 characters per line so you don't have one stupid-long line):
```
BinaryFormatter.exe public_key_file.cer ^
create_certificate_script.sql 40
```
Convert each DLL into a VARBINARY literal so that it can be created from a SQL script with no external dependency on the file system, via command prompt:
```
BinaryFormatter.exe Newtonsoft.DLL ^
create_Newtonsoft_script.sql 40

BinaryFormatter.exe MyProject.DLL ^
create_MyProject_script.sql 40
```

In SQL Server, create the Certificate, the associated Login, and grant it the required permission:

USE [master];

CREATE CERTIFICATE [MySqlclrStuff]
FROM BINARY = 0x{contents_of_create_certificate_script.sql};

CREATE LOGIN [MySqlclrStuff]
FROM CERTIFICATE [MySqlclrStuff];

GRANT UNSAFE ASSEMBLY TO [MySqlclrStuff];

In SQL Server, in your target DB (not in master), create the two Assemblies:

USE [SomeDB];

CREATE ASSEMBLY [Newtonsoft]
FROM 0x{contents_of_create_Newtonsoft_script.sql}
WITH PERMISSION_SET = UNSAFE;

CREATE ASSEMBLY [MyProject]
FROM 0x{contents_of_create_MyProject_script.sql}
WITH PERMISSION_SET = UNSAFE;

If you want to know how to automate this within Visual Studio / SSDT, please see the following blog post of mine detailing how to do that:

SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2