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

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...

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