1

We have some old code running in Sql Server 2008 servers, and we're looking to upgrade to Sql Server 2019. The old clr code is really old (like .net framework 2.0 old), so I knew I'd have to rebuild the assemblies for the new server. We did a backup/restore from the old systems to the new system, and while all the assemblies were there, they throw errors on execution.

I ran into the "CLR strict security" posts and the "CREATE or ALTER ASSEMBLY for assembly XXX 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..." message.

I started on the first assembly in the first db. I changed the framework to 4.6.1 and signed it. I first tried ALTER ASSEMBLY and it said it couldn't ALTER because of the signature difference. So I dropped all the references to that assembly, then dropped the assembly, and did a CREATE ASSEMBLY with the new code. And it worked. Maybe it shouldn't have but it did.

So I started slogging through the next assembly in the next database. Did the same process (update framework, sign it, rebuild, drop all references, drop assembly, create assembly). Only next time I get the "CREATE or ALTER ASSEMBLY for assembly XXX 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..." message.

I ran

sp_configure

SELECT * FROM sys.trusted_assemblies
SELECT * FROM sys.assemblies

in both databases. Both show "clr strict security" run_value as 1, both show no entries in trusted_assemblies.

I'm realizing my "just sign the assembly" understanding wasn't sufficient but I'm puzzled why the methodology worked on the first db and failed in the second db.

I generated the snk files fresh for each assembly and I haven't associated any logins with them.

How did "just sign the assembly" manage to work in the first try and not in the second?

In the first database, under the output of sys.assemblies, I see the public key token of the new build on the assembly and I see SAFE_ACCESS in the permission_set_desc and the new install dates, but I can't figure out why that sufficed in the first db and not in the second.

Thanks

1 Answer 1

4

First, there is/was no need to recompile the assemblies. It doesn't matter that they were linked to CLR 2.0. SQL Server 2012 and newer (at least through 2019) is linked to only CLR 4.0 and hence will use the highest level of .NET Framework installed on that server in the 4.x series. This works due to backwards compatibility in the Framework APIs.

Second, this is actually quite easy to fix by simply signing the assemblies in-place. Simply stated:

  1. create a certificate in the DB with the assembly
  2. sign the assembly
  3. create a certificate in [master] from the current DB's certificates public key
  4. create a login from that certificate
  5. grant the login the UNSAFE ASSEMBLY permission

Nothing external to SQL Server is required. I have an example of it in my answer to the following question (here on DBA.SE):

Error Msg 10314, Level 16, State 11 with SAFE assembly after upgrade to SQL Server 2017

And in the following post:

SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment (Msg 10314)

Third, I'm not entirely sure why your actions worked in the first case but not the second as it shouldn't have worked in the first case. If you didn't create an asymmetric key in [master] from the assembly (or the .snk file use to sign/strongly-name that assembly), then the only way to successfully load the assembly would be to enable the TRUSTWORTHY database property. Is it possible that you did that sometime between getting the original error (which wouldn't happen if TRUSTWORTHY were enabled) and your final attempt at CREATE ASSEMBLY?

15
  • Thank you for your response. I'll read your other articles, but the upshot is that I'm puzzled why the first one worked. The second one failing does seem like what I should have expected. When we did the backup/restore to the new server, attempts to call those CLR functions produced a System.IO.FileLoadException we assumed was related to the framework version. Thank you for the TRUSTWORTHY suggestion. I hadn't thought to look at that. The first db is, indeed, set with it on. Oct 28, 2021 at 21:31
  • 1
    @user1664043 You're welcome. Yes, with TRUSTWORTHY set to ON, that effectively side-steps the usual requirements. It must have gotten turned on at some point after you noticed the initial error. If you follow the steps I mentioned here, and even explicitly provide in the linked answer, then you can (and should) set TRUSTWORTHY to OFF. Oct 28, 2021 at 22:13
  • Still better to recompile, especially given that 2.0 and 4.0 even use different mscoree execution engines, and there are some fairly major differences in the Framework libraries Oct 29, 2021 at 0:35
  • So I've been working through your articles and pulling on some threads. I thought I would use a dev sql server box, create a cert and back it up, and then try to make a .pfx file from them so that I could use signtool.exe and sign our assemblies with them. I thought that I could make the CREATE CERTIFICATE FROM EXE statement work that way when we were installing the clr assemblies around. But I'm running into an issue with pvk2pvx.exe using the .cer and .pvk files created by BACKUP CERTIFICATE. The dialog box pops up with the .pfx option disabled. Is that just a rabbit hole? Oct 29, 2021 at 21:54
  • 1
    @Charlieface The second link indirectly mentions it with: "In SQL Server 2012, SQLCLR now loads .NET 4.0 code rather than .NET 2.0". Other articles have mentioned the static linking to a single version, but I can't find any right now. Regardless, if CLR 2.0 were available, then the following problem would never happen: web.archive.org/web/20150318063829/http://blogs.msdn.com/b/… Nov 24, 2021 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.