5

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.

6

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.

  • Thank you for the thorough response. I have already opened a support call with Microsoft to hopefully find the version of the DLL they have on the Managed Instance. I have also begun looking into rewriting the CLR to just not use System.Net.Http.dll – JamesP Aug 9 at 22:26
  • @JamesP Ok. What are you using System.Net.Http for? If it's for web services stuff, you should just use System.Net.HttpWebRequest and HttpWebResponse. You will have to do a little extra coding to construct and parse the XML of the web request, but that library is fully supported. – Solomon Rutzky Aug 9 at 22:38
  • Correct, web services stuff. I'll have a look into the dmvs and try and nail down the DLL version in the Managed Instance, but failing that I think I can get everything done with System.Net.HttpWebRequest – JamesP Aug 9 at 22:49
  • @JamesP Also, two questions. 1) what does this return when executed on the Managed Instance: SELECT [name], [file_version], [product_version], [language] FROM sys.dm_os_loaded_modules olm WHERE olm.[name] LIKE N'%mscoreei.dll%';. And 2) does xp_regread work on a Managed Instance? – Solomon Rutzky Aug 9 at 22:51
  • @JamesP Ok. Regardless of what MS comes back with, I am still curious about those two questions as I do not have a Managed Instance account to test with. I wish they would make it easier to do simple testing :-( – Solomon Rutzky Aug 9 at 23:12
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:

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.

  • 1
    Thanks for the response. I am certain that non of the restrictions you have listed are the cause. The CLR procedure in question posts a message to an Azure storage queue, nothing more. It uses only Microsoft own libraries and works perfectly on a local instance. I have another CLR that uses System.Net.Http.dll which errors in the same way on Azure Managed Instance, so it does seem to be specific to that dll. – JamesP Aug 9 at 19:05
  • 2
    Ok, well, I have no other advice for you other than to contact support at Microsoft. – Aaron Bertrand Aug 9 at 19:15

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.