Loading a CRL assembly in SQL Server 2005: The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

Question

I've written a C# DLL which I need to call from a stored procedure.

I've got errors through ALL the steps I did, and managed to solve them. But now I'm so tired of errors that I had to resort here and ask. I can't solve this one. Every little step I made in installing this .DLL into SQL Server, I got tons of errors to solve.

Here's the C# code of the program (compiled as Class Library to get the DLL):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Xml;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace WebServiceVatEuropa
{
    public class WebServiceVatEuropaClass
    {
        #region "Default Constructor"
        public WebServiceVatEuropaClass()
        {

        }
        #endregion

        [SqlProcedure]
        public static void check(string country, string vatNum)
        {
            bool valid;
            string name;
            string address;
            checkVatService vatchecker = new checkVatService();
            vatchecker.checkVat(ref country, ref vatNum, out valid, out name, out address);
        }
    }
}

Which is using another free library provided at the european vat checking webservice (Link to webservice) and uses their library (Library code) (this code is long and complex, you can just read mine I guess.

Well. I create the stored procedure (I had many problems here, many errors and so on, but now it works) with this code in SQL Server Management Studio:

CREATE PROCEDURE VAT_CHECKER
@Naz_codi nvarchar(2),
@vatNum nvarchar(max)
AS EXTERNAL NAME WebServiceVatEuropaClass.[WebServiceVatEuropa.WebServiceVatEuropaClass].[check]
GO

And then in the db I have:

1. enabled the clr with the code from msn

   sp_configure 'show advanced options', 1;
   GO
   RECONFIGURE;
   GO
   sp_configure 'clr enabled', 1;
   GO
   RECONFIGURE;
   GO
   

2. changed the db owner to sa

3. registered the assembly with this code

   CREATE ASSEMBLY [WebServiceVatEuropaClass] --AUTHORIZATION [mydb\administrator]
   FROM 'C:\Upload\WebServiceVatEuropa.dll'
   --WITH PERMISSION_SET = SAFE
   WITH PERMISSION_SET = UNSAFE
   --WITH PERMISSION_SET = EXTERNAL_ACCESS
   

Then when I launch

EXEC VAT_CHECKER 'IT','10050721009'

I get this error:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65545. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. 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 'webservicevateuropa, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

System.IO.FileLoadException:
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

What it could be? I can't find anything to fix it...

Answer 1

The error message basically tells you all - since your assembly is reaching out and accesses an external web service, you must configure it as EXTERNAL_ACCESS.

You're not showing just how you're creating the assembly in your SQL Server - but basically, you need to use something like:

CREATE ASSEMBLY assembly_name
FROM ...(your assembly).....
WITH PERMISSION_SET = { EXTERNAL_ACCESS }

Did you do it like this? If not: please try again with the EXTERNAL_ACCESS permission set!

See the MSDN documentation on Code Access Security for more details

Answer 2

So, at long last, I managed to solve the problem (and I couldn't believe it).

Here's what I did:

-The code remained mostly the same as above.

-I was trying to deploy the dll on a SQL server 2005 on a remote desktop machine, but I was compiling it locally with Visual Studio 2010. Maybe this was an issue, OR MAYBE NOT. But I switched to a Visual Studio 2008 copy installed on the remote desktop machine. I read on some sites that different target architecture compilation settings in Visual Studio can generate issues similar to mine.

-As I said, at every step I made in this task of deploying this assembly to an instance of SQL Server, I got an error. And this time it was no different. After I compiled it on the remote desktop machine, it gave another error about XmlSerialization.

NOTE: The particular reason because I NEEDED the XmlSerializers was because I was consuming a webservice which used Xml and C# Xml objects to manipulate data, therefore Xml was involved in the program. I don't know whether you need XmlSerializers in other situations or not, but in this particular case I needed it otherwise SQL server would give me exoteric errors.

The solution for this particular issue is explained here:

Xml Serialization issues

I solved by generating this extra dll by changing the compilation settings like shown in the article.

-This ends what I had to do in Visual Studio.

Now it starts the SQL Part:

• I had to create an assembly for both my own DLL, and for the XmlSerialization one.

  CREATE ASSEMBLY [WebServiceVatEuropaClass] --AUTHORIZATION [mydb\administrator]
  FROM 'C:\Upload\WebServiceVatEuropa.dll'
  --WITH PERMISSION_SET = SAFE
  WITH PERMISSION_SET = UNSAFE
  --WITH PERMISSION_SET = EXTERNAL_ACCESS
  
  
  
  CREATE ASSEMBLY [WebServiceVatEuropaClassXmlSerializers] --AUTHORIZATION [mydb\administrator]
  FROM 'C:\Upload\WebServiceVatEuropa.XmlSerializers.dll'
  --WITH PERMISSION_SET = SAFE
  WITH PERMISSION_SET = UNSAFE
  --WITH PERMISSION_SET = EXTERNAL_ACCESS
  

NOTE: Probably since the problem was not permissions (though Sql server was giving me totally misleading SAFE/EXTERNAL permission error messages) it may be sufficient to set the assembly permission as EXTERNAL instead of UNSAFE. I say this just because you might not want to allow UNSAFE permission if not strictly needed, as this whole process is pretty unsecure (if I documented myself well by reading on the internet) and even with asymmetric keys someone could replace your assemblies

then I created the Stored procedure for calling (same code as above in the question) the assembly and this time when I called the EXEC, IT WORKED!

Hope this will be useful for someone else having the same problem.