Calling unmanaged C/C++ DLL functions from SQL Server 2008

Question

I have a vast library of C/C++ functions, which needs to be called from SQL Server 2008. I have written a C# adapter class which loads these functions from Win32 DLL with DllImport and exposes them to .Net code. This works perfectly fine in most .Net applications.
Now, I was trying to use the same technique with SQL Server CLR. I create a set of CLR functions and stored procedures, which call the adapter class. This does not work as an attempts to load unmanaged DLL results in System.BadImageFormatException.
I can do this with extended stored procedures, but that method is deprecated and may be discontinued in any new release of SQL Server.
What would be the proper way of calling unmanaged functions from CLR stored procedure? My guess that this should be done out-of-process.

---

I am trying to make my stored proc call a Web service which exposes these functions. This sounds like a good idea, but so far I am having a problem deploying the SQLCLR assembly which makes Web service call. I cannot load System.ServiceModel.dll assembly version=3.0.0.0, which has dependency on System.Web.dll assembly version 2.0.0.0.

Loading System.Web assembly gives me the following error:

Assembly 'System.Web' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.

I have found the solution for the problem of deploying System.Web assembly. Instead of deploying it from C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll, it should be deployed from C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll. Then all other required assemblies get deployed too.

The list of assemblies in the order of deployment:

• C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll
• C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll
• C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll
• C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll
• C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll
• C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll

Answer 1

Interesing discussion here : MSDN - Unmanaged code in SQL CLR. I suspect it's due to how the DLL's are loaded by the engine. They present a series of options including hosting the code outside the sql server in another service and accessing the code using WCF or perhaps COM. The Final option is to perhaps recompile your code to pure managed C++ but this may not be an option for legacy code.

Understanding CLR Integration in SQL Server 2005 presents more information on how the process works.

To further restrict the code that is allowed to exist and execute inside SQL Server each assembly must be registered with a set of permissions. Three pre-defined sets are available to use; SAFE, EXTERNAL_ACCESS and UNSAFE ...

You should also review CLR Integration Security, and detemrine the trust levels need for the code you're executing and whether you will be able to access use the code within the CLR process anyway.