Search code examples
sql-servert-sqlsecuritycode-signingsqlclr

How does one Register a C# CLR Assembly with SQL Server without using Security Hacks?

When I try and register a signed C# assembly in SQL Server via the Object Explorer in SSMS by right-clicking on the "Assemblies" node and selecting "New Assembly", then my signed DLL via "Browse", I receive the following error:

CREATE or ALTER ASSEMBLY for assembly 'My Assembly' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

Even though I've tried signing the assembly both through a "Signing" project-properties-generated PFX file and through an sn.exe-generated SNK file (linked through hacking the project file's AssemblyOriginatorKeyFile node), both result in the DLL still being reported as not having a "Strong Name" by SQL Server in the "New Assembly" dialog:

New Assembly Dialog

I've tried this with both a Visual-Studio-2019-generated Class Library project in both .NET 4.8 and .NET 4.0 and a Visual-Studio-2010-generated SQL CLR Database Project all to the same end.

I understand the error message suggests a security hack to workaround the problem (which I have also tried to no avail anyway) but I'm trying to avoid these as the database server is public-facing. How do I fix this properly?

Solution

  • Assuming that the DLL truly is signed, then you are likely missing the second step of the preferred method noted in the first part of the error message, specifically:

    that has a corresponding login with UNSAFE ASSEMBLY permission.

    That part is critical to this working properly. Prior to loading the assembly into SQL Server, you need to do the following:

    1. create an asymmetric key in the [master] database from the DLL
    2. create a login from that asymmetric key
    3. grant the new login the UNSAFE ASSEMBLY instance-level permission

    Then you can load any assembly into any database so long as it has been signed by that same strong-name-key / pfx file (which can be several if you have multiple projects in your solution).

    Even though I've tried signing the assembly both through ..., both result in the DLL still being reported as not having a "Strong Name" by SQL Server in the "New Assembly" dialog:

    Correct. This is due to either poor UI design, or a bug. When creating a new assembly, the "Additional properties:" fields do not reflect the values of the file indicated in the "Path to assembly:" field. Most likely this is just the "assembly" dialog for all assembly-related stuff, and it works just fine for assemblies that are already loaded into SQL Server, but not for what you are attempting to load. If the intention was to peek into the file to indicate the current values of whatever is found at the path specified in the "Path to assembly:" field, then it's a bug (but I suspect it's the former). I have reported this to Microsoft here:

    SSMS: "Additional properties" in "New Assembly" dialog is misleading as it doesn't describe DLL being imported

    For info on working with SQLCLR in general, please visit SQLCLR Info