I am developping an SQL Server Database Project in Visual Studio which is in fact a User Defined Function. In this project, I included Json.NET as a reference (using NuGet).
I managed to publish (and make work) my assembly and the UDF to my SQL Server instance by first turning the database TRUSTWORTHY ON (since my project is UNSAFE) and then running this:
CREATE ASSEMBLY [System.Runtime.Serialization]
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Runtime.Serialization.dll'
WITH PERMISSION_SET = UNSAFE;
Which turns out to be an assembly Json.NET depends on (if I don't do it, I get an error)
Meanwhile I read about how bad turning TRUSTWORTHY ON can be and I tried to go the asymmetric key way to avoid turning it on.
Before even signing my own assembly, I know I will have to create a key for System.Runtime.Serialization since my project depends on Json.Net which in turn depends on it, but when I run it, I get this:
USE [master];
GO
CREATE ASYMMETRIC KEY [SystemRuntimeSerializationKey]
AUTHORIZATION [dbo]
FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Runtime.Serialization.dll';
GO
Msg 15468, Level 16, State 5, Line 3
An error occurred during the generation of the asymmetric key.
Which does not help me much.
So is it possible to generate such a key for .NET framework assemblies, or does it exist a workaround other that turning TRUSTWORTHY ON ?
No, I have never found a way to accomplish this. The key used to sign the .NET Framework Assembly is internal / private to Microsoft. The options tried:
Extract Private Key / load into SQL Server: not possible, else the key wouldn't be "private". The signature / strong naming system is effective because outsiders cannot claim that they signed the code.
Add a signature: I tried adding a new one using the sn utility. Doesn't work due to:
Failed to re-sign the assembly -- Public key of assembly did not match signing public key.
But, even if this did work, that might not mean that loading it into SQL Server would work since it is not the same Assembly that you added as a resource to your project. Other .NET Framework DLLs, if there are any other dependencies, would only know of the original signature.
Remove current signature and add a new one: I tried using ILDASM to disassemble System.Runtime.Serialization.dll, and then add a new private key created from sn -k and then relink using ILASM, but that failed on the ILASM step (and I don't have time to investigate further).
But just like the option above, even if this did work, you would have to change the Json.NET project reference for System.Runtime.Serialization to be this new DLL, recompile it, then change your project reference to be the new DLL, and then recompile that. But this would only get you the ability to load the DLLs cleanly, it would not guarantee that they would work if they have external dependencies that are expecting the original Microsoft signature.
Essentially, if you are loading DLLs that you don't have control of in terms of the signature, then the only hope is to decompile using ILDASM and recompile using ILASM, specifying a new snk file, but that won't work if other Assemblies are linked to what is being recompiled. And certainly .NET Framework DLLs fall into this category.
Simply put: if you are loading unsupported .NET Framework DLLs, then you pretty much need to set TRUSTWORTHY ON.
BUT: Keep in mind that even if this did work to load everything via an Asymmetric Key or Certificate, that does not mean that you won't run into functional problems. There is a reason why these libraries have not been approved / validated. There is code in them doing things that could work in ways that you are not expecting, such as storing data to static fields. In Windows and Console apps this is not an issue as it is one use per App Domain. But SQLCLR employs a shared App Domain, so multiple SQL Server Sessions will share those static variables. It might be that the methods called by the Json.NET library don't use those unsafe things, but there is no way of knowing, and even if we did know, not much we can do about it now :-(.
One thing that I have been contemplating is tracing through the methods called in the unsupported .NET Framework DLL, and assuming that it is not doing anything unsafe, copying that code directly into the project. Theoretically that should work, as long as the calls into System.Runtime.Serialization do not call other unsupported DLLs or do "unsafe" things, etc. But, I haven't had time to test this out.