No, an Asymmetric Key won't work, most likely because strong-naming assemblies changed in .NET 4.5 or somewhere around there, to now be Enhanced Strong Naming, which is not supported by SQL Server's CLR host (not without resigning it, which won't work here).
The key is to use Certificates. Create a Certificate in
master from the DLL, then create the Login from that Certificate, and finally grant the
UNSAFE ASSEMBLY permission to that Login.
It should be that simple (assuming, of course, that the DLL is pure-MSIL and not mixed, because only pure-MSIL assemblies can be loaded into SQL Server).
- You shouldn't have to add / drop more than the main assembly, assuming that they are referenced by the main one. When adding assemblies via DLL (as opposed to a hex bytes /
VARBINARYliteral), SQL Server will grab any referenced assemblies that are in the same folder. It will set all of those auto-added assemblies to "visible = 0". Assemblies that are set to "visible = 0" that are referenced by a main assembly are automatically dropped if the main assembly is dropped.
- You don't need to grant both
EXTERNAL ACCESS ASSEMBLYpermissions to the signature-based login. The
UNSAFE ASSEMBLYpermission assumes the
EXTERNAL ACCESS ASSEMBLYpermission such that you can set assemblies to either
PERMISSION_SETif you have the
- If using SQL Server 2017 or newer, there is no need to use the "trusted assemblies" feature. Please see my post here as to why and what should be done instead: SQLCLR vs. SQL Server 2017, Part 1: "CLR strict security" - The Problem. Of course, in this particular case it is a moot point since the proper approach is using the Certificate method as described above.
Please note that in other answers to similar questions, whether answered by myself or others, the answer was always that
TRUSTWORTHY had to be enabled. This was due to misinformation provided by the Microsoft documentation that I did not realize until more recently. I am working on correcting both my previous answers and that documentation.