2

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?

Improve this question
5
  • I think you need to do EXEC sp_configure 'clr strict security', 0; RECONFIGURE; Commented Mar 17, 2020 at 11:55
  • That just gives me The configuration option 'clr strict security' does not exist, or it may be an advanced option.. Isn't that a security hack anyway? Commented Mar 17, 2020 at 11:59
  • Define "hack". What Microsoft calls "strict" security and what's actual security happen to be different things. There's a nice article on it here. Doing this deployment "properly" is involved. Commented Mar 17, 2020 at 12:01
  • 1
    There is an explanation on GitHub here. github.com/tSQLt-org/tSQLt/issues/25 Commented Mar 17, 2020 at 12:02
  • 1
    @Steve and Matt: 1) there is no need (well, not at the moment at least) to disable "clr strict security", and 2) if that is ever needed, it is an "advanced" option, so you would need to first execute EXEC sp_configure 'show advanced options', 1; RECONFIGURE; first (just for this option, not for "clr enabled", which is not an advanced option -- I know this wasn't mentioned by anyone, but it is a very common misunderstanding, so I am just pre-clarifying :-) ) Commented Mar 17, 2020 at 16:06

1 Answer 1

Reset to default
1

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

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

8 Comments

I was trying to run this script to register the assembly: CREATE ASSEMBLY CryptographicFunctions FROM 'C:\CLR Database Code\My Assembly.dll' WITH PERMISSION_SET = SAFE; weirdly, it started working after running these even though it has SAFE as the permission set: GRANT EXTERNAL ACCESS ASSEMBLY TO [My Windows User Name] GRANT UNSAFE ASSEMBLY TO [My Windows User Name]. I'm now battling how to create the scalar function to point to a static method in the assembly which has now been registered (I don't know whether I can call the assembly until I've done that).
I've also just seen that if I double-click on my assembly (which now appears under the "Assemblies" node), "Strong Name" now shows as "True".
@MattArnold Prior to SQL Server 2017 introducing the insanely bad idea of "clr strict security", SAFE assemblies didn't require any signature, EXTERNAL_ACCESS could be loaded if the signature-based login had either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY, and UNSAFE could be loaded only if the login had UNSAFE ASSEMBLY. Now, sadly, all permission set levels require UNSAFE ASSEMBLY. EXTERNAL ACCESS ASSEMBLY is now obsolete / doesn't do anything (and even prior to 2017, UNSAFE ASSEMBLY implied EXTERNAL ACCESS ASSEMBLY).
@MattArnold Correct, the "additional properties" in that dialog only inspect assemblies that have been loaded into SQL Server, not the DLL on the file system prior to being loaded. Is a bad UI design. I will report it to MS as it is very misleading.
@MattArnold I have updated my answer to include the link to the bug report I just field regarding those property fields not reflecting the DLL being imported. Please vote for that ticket. Thanks!
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.