SQLCLR Function and System.Runtime.Serialization In GAC

Question

I've built a SQLCLR function in C# that will deserialize JSON and return a table.

The problem I have is getting the correct assemblies within SQL Server 2012.

In order to utilize Newtonsoft's deserializer I've had to add the following assemblies to SQL Server:

System.ServiceModel.Internals.dll
SMDiagnostics.dll
System.Runtime.Serialization.dll
Newtonsoft.Json.dll

This has all gone as planned but when I try to run my function I get the following error:

System.IO.FileLoadException: Could not load file or assembly 'System.Runtime.Serialization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information. ---> System.IO.FileLoadException: Could not load file or assembly 'System.Runtime.Serialization, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

The server has .NET 4 installed and the DLL I have tried to add is from that. However the DLL shown within C:\Windows\assembly is version 3.0.0.

I used Powershell to update the DLL in the GAC, but that only updates the DLL located within C:\Windows\Microsoft.NET\assembly\GAC_MSIL.

How on earth do I get the GAC at C:\Windows\assembly (which I assume is the one SQL Server is comparing it to) to reflect the correct assembly?

Any help is greatly appreciated.

Unsegregated

Did you ever figure this out? It'd help a lot. – Alburnum

Yes I did, sorry never got round to updating post. Will do so now. – Unsegregated

Answer 1

Finally managed to get this working. Not sure how helpful this answer will be to others but I had to use a bit of a mix of dll's from various sources.

Basically I took all of the dll's from my local machine and copied them to the server and then tried combinations of references to the local files and files within the .Net framework.

In the end I had to reference 1 of the dll's from .Net on the server and the rest of them copied from my local machine.

This got it working and also then worked when moved to production server. Not the simplest of solutions but all I can advise anyone in a similar situation is just to try playing around with various combinations of references.

Unsegregated

Did you have to use unsafe permission? – Gobbler

Yes, don't think it's possible to use your own dll's any other way – Unsegregated

No you can, as long as you follow certain guidelines. Problem is Newtonsoft uses some non-readonly static variables, and locking primitives. I'm fixing up this code, and I've also removed references to System.Runtime.Serialozation, which it only uses for DataContract attributes, this I can live without. SQL Azure permits only safe user libraries. – Gobbler

@Gobbler Just curious if you ever got this working "correctly" in a SAFE Assembly. I started down the road of making all of those changes a few years ago but it got too messy. I would try again if I knew all of those static variable references could be removed and still have it work without "odd" behavior ;-) – Grange

@srutzky I didn't go very far unfortunately, as I switched over from JSON to XML since I also needed some transform support. Did you get it working? – Gobbler

Answer 2

Run this script in SQL and this should resolve the issue

ALTER ASSEMBLY [System.Runtime.Serialization] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll'

Buckingham

Thanks for this! I basically did: CREATE ASSEMBLY [System.Runtime.Serialization] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE GO – Bullroarer

Answer 3

(Sorry, didn't see this due to how it was tagged)

I would not recommend the approach taken in the accepted answer, as it should not be nearly that complicated.

SQL Server has it's own CLR host that is highly restricted. And it can only use one CLR version. So SQL Server 2005, 2008, and 2008 R2 are linked to CLR 2.0 and the .NET Framework versions linked to it (i.e. 2.0, 3.0, and 3.5), while SQL Server 2012 and newer are linked to CLR 4.0 and the .NET Framework versions linked to it (i.e. 4.0 and newer). So, since you are on 2012, I am not sure why it even mentioned 3.0 in the error message, but it isn't trying to use that one.

The error message also mentioned the 4.0 version, and that the problem was that the signatures did not match. One of the restrictions of the SQL Server CLR host is that any Assembly loaded into it, if also found in the GAC, must be the exact same version. The most likely cause of this issue is some combination of:

1. Somehow the server (i.e. the Windows OS) got a .NET Framework update and the GAC did not get refreshed. Not sure if you tried to reboot the server first before copying DLLs over, but that might have fixed it, or possibly using gacutil.exe to force the unload and reload of the current version of the DLL as found in C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319

2. Your development machine being at a different .NET Framework version than where the SQLCLR Assembly was being deployed to. It is best if these match. It also helps to make sure that in the .sqlproj file, in the section on "References", that the tag for the system DLLs do not have the "SpecificAttribute" property set.

3. This is a long-shot but has been an issue for at least two systems I helped with: the .NET Framework DLLs can be "corrupt", in which case you need to do a .NET Framework repair. You might be able to do this from Control Panel | Programs, or using Microsoft's .NET Framework Repair Tool.

Grange