Mfcttrf

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.

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? That depends. It might be in a DMV (not the clr_info one as that only has the CLR version, not the Framework version), and/or might be discoverable via the registry if xp_regread is usable in a Managed Instance. Is it? I will update in a moment with whether or not the DMV I am thinking of has enough info to get the Framework version from.

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.

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.