TSQL CREATE ASSEMBLY FROM varbinary breaks the class_name parameter

Question

I have migrated my on-premise SQL Server 2008 R2 databases to Azure SQL Server Managed Instance (SQL Server 2017). One database is an archive (read-only) database, one is the OLTP database, and the third is an utility database, where I keep generic functions, stored procedures, and maintenance scripts. Everything went very smoothly for all three DBs except for a CLR assembly in the utility DB. The assembly provides Regex functionality in TSQL code - very useful! I based it off of the Phil Factor code here. Originally it was loaded into an on-premise DB from the compiled DLL. It works like a champ there. But on SQL MI, I get the following error when running a SP that uses one of the CLR functions.

Msg 10314, Level 16, State 11, Procedure dbo.globalSearch, Line 22 [Batch Start Line 2]
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65541. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'regexsqlclr, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

I've tried solving the assembly trust issues using the steps in this MSDN post, specifically executing

sys.sp_add_trusted_assembly

which succeeds, but changes nothing. Then I thought that since it is suggesting that it cannot load from file, which seems to make sense since the file does not exist in SQL MI where I have no access to the file system, that I should try dropping and recreating from varbinary. I only said that it seems to make sense because the file also does not exist on any of my other on-premise servers other than the one I originally loaded it from, and it works perfectly on all of them. But, I'm willing to try anything! So using SSMS I scripted out the assembly as DROP and CREATE, which uses the FROM BINARY syntax, and scripted out all of the functions likewise. CREATE ASSEMBLY succeeds, so I'm thinking I'm on the right track. Then I try to create the first function and BAM, another error! This time the error reads

Msg 6505, Level 16, State 2, Procedure RegexIndex, Line 2
Could not find Type 'RegexSQLCLR.RegularExpressionFunctions' in assembly 'RegexFunctions'.

I've been Googling for hours trying to find a solution to that problem and have had zero luck. Even though that syntax for the class portion of the EXTERNAL NAME clause works perfectly for the assembly loaded from file. I verified that the varbinary scripted out by SSMS is identical to the binary of the original DLL. Someone on a Microsoft forum suggested that I make sure the DLL was compiled with the Any CPU option - it was. As a sanity check, I did the same procedure on one of the on-premise servers, i.e., DROP and CREATE ASSEMBLY FROM BINARY, and got the exact same result: I cannot load any of the CLR functions! I've tried every conceivable permutation of the class names that I can think of, to no avail. Here is the code for CREATE ASSEMBLY and CREATE FUNCTION

CREATE ASSEMBLY [RegexFunction]
AUTHORIZATION [dbo]
FROM 0x4D5A90000 *truncated_for_brevity*
WITH PERMISSION_SET = SAFE

CREATE FUNCTION RegExIndex
   (
    @Pattern NVARCHAR(4000),
    @Input NVARCHAR(MAX),
    @Options int
   )
RETURNS int
AS EXTERNAL NAME 
   RegexFunction.[RegexSQLCLR.RegularExpressionFunctions].RegExIndex
GO

RegexSQLCLR is the name of the original DLL and RegularExpressionFunctions is the name of the class. RegexSQLCLR is also the name specified in the name column of sys.assembly_files after using CREATE ASSEMBLY FROM BINARY; the full path to the original DLL is in the name column otherwise.

Answer 1

So, there are a series of issues here:

1. The first issue is that unsigned assemblies (or even signed assemblies that do not also have a matching, signature-based login that has been granted the UNSAFE ASSEMBLY permission) are no longer considered "SAFE" due to potential (but possibly unproven) security concerns. So, starting in SQL Server 2017, a new server level config setting called "CLR strict security" was introduced and forces all assemblies to meet the requirements of assemblies marked as UNSAFE. The PERMISSION_SET options of SAFE and EXTERNAL_ACCESS still work as before, this is just an issue for loading assemblies and executing SQLCLR objects from them. The problem you ran into was that SAFE assemblies never needed to be signed (though they could have been, such as all of the assemblies in my SQL# SQLCLR library, and there are benefits to signing them, even if they only contain SAFE code and will remain as PERMISSION_SET = SAFE) and so most were not signed, yet now they need to be. So, upon upgrading / migrating a DB from pre-2017 to SQL Server 2017 or newer, a security error is thrown.

One option is to simply disable "CLR strict security", or another option is to enable TRUSTWORTHY for the DB where the assembly lives. Both are less secure options. I'm not sure how "insecure" having "CLR strict security" disabled really is (in practice, it could be a huge overreaction on Microsoft's part), but clearly the preference is to keep that enabled. Enabling TRUSTWORTHY is definitely a very bad option: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining.

The mechanism that Microsoft released in order to address this situation is the "trusted assemblies" feature. Unfortunately, that feature is also a bad option: it was very much never needed (well, only needed for Azure SQL Database, which no longer supports SQLCLR, but the "trusted assemblies" code had already been written) and was only made visible because nobody understood that existing functionality already addressed this situation, and in a much better way. Please see: SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” - The Disappointment. That post goes into detail as to the problems with "trusted assemblies" and how you should fix the problem of existing, unsigned assemblies (i.e. create a certificate in the DB with the assembly, sign the assembly, create the same certificate in master from the public key only, create a login from the certificate in master, and finally grant that login the UNSAFE ASSEMBLY permission).

1. I've tried solving the assembly trust issues using ... sys.sp_add_trusted_assembly which succeeds, but changes nothing.

I'm not sure what value you used for the hash, but that stored procedure doesn't verify that the hash matches anything. It just loads the hash into an internal table to be read from later when an assembly is referenced. Then it will see if the assembly's hash value matches a value in that internal table of stored hashes. So it should pretty much always succeed (as long as you gave it a valid binary value that could be a SHA2_256 hash).

1. I thought that since it is suggesting that it cannot load from file, which seems to make sense since the file does not exist in SQL MI where I have no access to the file system

This is just a misinterpretation. You are correct that there is no file system access in Azure SQL Database Managed Instances, but the file system in this case is the internal table that is storing the assembly that was loaded via CREATE ASSEMBLY. The error is saying that the assembly cannot be loaded from the table into memory. And this was due to the security issue noted above in item # 1. So dropping and re-creating the assembly doesn't actually do anything.

1. Someone on a Microsoft forum suggested that I make sure the DLL was compiled with the Any CPU option

I have no idea why that even came up. Again, nothing was wrong with any of the code here.

1. AS EXTERNAL NAME RegexFunction.[RegexSQLCLR.RegularExpressionFunctions].RegExIndex

Not sure why RegexSQLCLR was put in there, but the format is (no namespace):

AssemblyName.ClassName.MethodName

or (with namespace):

AssemblyName.[NameSpaceName.ClassName].MethodName

1. Again, TRUSTWORTHY ON is a bad choice, and entirely unnecessary.