CLR Stored Procedure on Azure SQL Managed Instance error on execution: "Assembly in host store has a different signature than assembly in GAC"

Question

I have a CLR stored procedure which executes correctly when deployed to local SQL Server instances from SQL Server 2012 - 2017. I can successfully deploy to an Azure SQL Managed Instance but when I execute the procedure I get the following error:

Could not load file or assembly 'System.Net.Http, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050).

I have tried playing around with the project references but to no avail - the only version of the System.Net.Http.dll that can be deployed to the Managed Instance is one that errors on execution.

Answer 1

One of the requirements / nuances of working with SQLCLR is that any assembly loaded into both the GAC and SQL Server must be the exact same version (i.e. down to the patch level, not just Major.Minor.*). So, your local instances might all be using 4.7.2 (or whatever), but if the Managed Instance is using 4.7, 4.7.1, 4.8, or anything else, then you will get that error. You would even get this error if one of your local instances was on a server running a different version of the .NET Framework than the one that you grabbed the System.Net.Http.DLL from. Of course, if all of these different instances you mentioned are running on the same physical server, then there is only one OS involved anyway, so of course they all work correctly ;-).

You will need to find out what specific version is being used on the Managed Instance and use that in your Azure install script. You shouldn't need to use the same version locally since the reference to a similar version that has the same signatures should work.

Now, how to find the exact Framework version on Managed Instance? The following query seems to be the best way to get that info:

SELECT olm.[name], olm.[file_version]
FROM   sys.dm_os_loaded_modules olm
WHERE  olm.[name] LIKE N'%mscoreei.dll%';

O.P. replied back that the query above returned a version of 4.7:2623.0 on the
Managed Instance, and a version of 4.7:3190.0 on the local (on prem) system.
 
This is the source of the "Assembly in host store has a different signature than assembly in GAC" error

So, in order for this to work, you need to find that specific version of that DLL and package it up to load into the Managed Instance.

P.S. This issue is one of the downsides to using unsupported .NET Framework libraries. They are going to be in the GAC (usually, right?) and if a Windows update (locally, or whatever they do to the host system of the Managed Instances) updates the version then your SQLCLR project stops working. And, if the new unsupported Framework DLL gets converted to a mixed mode assembly (both managed and unmanaged code), then you won't be able to load the new version into SQL Server and will need to re-code your project to not use that unsupported Framework assembly.

P.P.S. What are you using System.Net.Http for? If it's for web services stuff, then you should just use the System.Net.HttpWebRequest and HttpWebResponse classes. You will have to do a little extra coding to construct and parse the XML of the web request, but those classes exist in a library that's fully supported.

O.P. replied back with: "Correct, web services stuff."

Later, O.P. replied back with: "I've managed to successful deploy and execute a basic SQLCLR
procedure on Managed Instance using just HttpWebRequest and HttpWebResponse,
I think it'll be best to go down that line until Microsoft extend the list of natively supported libraries.."

P.P.P.S. You might want to take a look at SQL# (a SQLCLR library that I wrote), as there is a stored procedure that handles this, based on HttpWebRequest. It handles most (if not all) internally managed HTTP headers, allows for sending custom headers (including userid/password, if necessary), sending POST / GET data, and even some other stuff that most people usually leave out. Just a thought, though for full disclosure, while there is a Free version, INET_GetWebPages is only in the Full (paid for) version.

Answer 2

From Azure SQL Database managed instance T-SQL differences from SQL Server:

A managed instance can't access file shares and Windows folders, so the following constraints apply:

• Only CREATE ASSEMBLY FROM BINARY is supported. See CREATE ASSEMBLY FROM BINARY.

• CREATE ASSEMBLY FROM FILE isn't supported. See CREATE ASSEMBLY FROM FILE.

• ALTER ASSEMBLY can't reference files. See ALTER ASSEMBLY.

And later:

CLR modules and linked servers sometimes can't reference a local IP address

CLR modules placed in a managed instance and linked servers or distributed queries that reference a current instance sometimes can't resolve the IP of a local instance. This error is a transient issue.

Workaround: Use context connections in a CLR module if possible.

One of these restrictions might be what is blocking you; if not, it would be helpful to add more info to your question about what your CLR procedure does, and how you have confirmed each of these restrictions doesn't apply. A search for +CLR +"Managed Instance" yields exactly two results, so if nothing here applies, I suggest engaging Microsoft Support.