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.
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.