4

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?

Improve this question

2 Answers 2

Reset to default
2

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:

  1. 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.

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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];

  7. 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

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I have another problem now. So, I signed Newtonsoft.dll but it depends X.dll, I have signed X.dll and registered it successfully but while registering Newtonsoft.dll, it is still complaining about X.dll which it says X.dll is not registered. I'm assuming checksum or public key of X.dll which Newtonsoft referencing changes after signing it. Do you have any idea what I can do for that?
@OZ1903 Is X.dll a .NET Framework library? If so, are you sure you signed and loaded the correct one? Are you pulling from DLLs on the file system or doing what I recommended re: using VARBINARY literal / hex bytes? Pulling from the file system will automatically grab dependent DLLs from the same folder, which might not be the one(s) you are signing.
Yes it is a .Net Framework Library. I have snk file to sign all dlls so I have used that instead of certificate. So what I did is, I pulled X dll and newtonsoft into a folder, I signed them with the snk file, then I tried to register them with the asymmetric key which is in the database already (previously created for other clr assemblies). So, I was assuming asymmetric key would work as certificate, is that wrong assumption? I haven't tried VARBINARY yet. Can I do this with asymmetric key or should I strictly use Certificate? By the way, thanks for your great explanations
@OZ1903 No, asymmetric key = strong naming and that gets embedded within the assembly, so you can only have 1 strong name key. Certificates get appended to the end of the assemblies, and you can have multiple certificates. Hence, my instructions in my answer were very specific. But you can have both strong name and certificate signature, so you can still use the snk for existing stuff. But the framework DLL, if already given a strong name, won't work anymore if given a new strong name as other framework DLLs won't recognize it.
I've followed your solution verbatim and it works perfectly for .dlls I build. But I am unable to register .NET Framework assemblies. I have a dependency on System.Drawing and get an error: CREATE ASSEMBLY for assembly 'System.Drawing' failed because assembly 'System.Drawing' failed verification. Is there any trick to signing and using framework .dlls?
|
0

You can use certificates instead of asymmetric keys. See my blog-post here for more information.

/Niels

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.