Sql Server 2019, migrating clr assemblies, clr strict security

Question

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

Answer 1

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?