Accessing COM in SQL CLR

Question

Hope you all having a good day!

I need your recommendations for something I've been thinking about these last three days. I have a COM component written in an unmanaged platform. The component has a method that returns a sort of some sensitive data and I need to store the value as soon as I get it.

What I need is to call a UDF to access the COM object and get the value. I've tried this so far and I'm getting this exception:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetRate": System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {D039A99F-5D45-42C7-A53C-507913D8C6D6} failed due to the following error: 80040154.
System.Runtime.InteropServices.COMException:
at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck)
at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache)
at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache)
at System.Activator.CreateInstance(Type type, Boolean nonPublic)
at UserDefinedFunctions.GetRate(SqlString Source_Currency_Name, SqlString Destination_Currency_Name, SqlMoney Amount, SqlBoolean Mode)

It seems that the function can't see the registered COM component (80040154). The clr assembly is registered as unrestricted. I tried to call the UDF with 'sa' or in Windows Integrated mode. There is no difference.

This is the code for initializing the COM component and the code is working fine outside SQL:

Activator.CreateInstance(Type.GetTypeFromCLSID(new Guid("D039A99F-5D45-42C7-A53C-507913D8C6D6"), true))

Is there anyway to make this working guys? One way or another I need a UDF call this COM object or at least access some windows service with .NET remoting through this UDF. Every suggestion guide me through this way would be appreciated.

Thank you all.

Answer 1

In general I would not recommend using SQL CLR objects as a bridge between COM and SQL. There are a lot of potential caveats there starting from security and ending with very cumbersome release procedures, requiring physical access to the production sql box which may or may not easily available.

I have also noticed that you're using Activator.CreateInstance and then supplying the clsid to it. In your original post you imply that it is a COM clsid. I'm not sure that Activator working from AppDomain created in SQL CLR can actually find the object for com clsid.

The way I would try it:

• Create managed proxy for your com
• make sure it's signed and placed into gac
• Try accessing that managed proxy from SQL CLR

But I seriously doubt it will work. Any remoting solution WCF/WebServices/Remoting or even SQL Broker seems like a better bet.

In short it is hard to tell anything more without seeing the actual .net and sql codez.

Answer 2

I'm guessing you're importing your CLR assembly into SQL Server? If so, this could be an platform/architecture issue. What platform are your targeting when you build your DLL?

Make sure that you're building your DLL against the correct platform (ie. x86, x64).

I would suggest you modify your project's platform from "Any CPU" to "X86" in Project Properties, Build/Platform's Target in Visual Studio.

Then use "DROP ASSEMBLY", followed by "CREATE ASSEMBLY", to re-import that correctly built dll.

You may also want to ensure that you've made the following change:

1. Locate your COM object GUID under HKey_Classes_Root/Wow6432Node/CLSID.
2. Once located, add a new REG_SZ (string) value. The name should be AppID and the data should be the same COM object GUID you have just searched for.
3. Add a new key under HKey_Classes_Root/Wow6432Node/AppID. The new key should be called the same as the COM object GUID.
4. Under the new key you just added, add a new REG_SZ (string) value, and call it DllSurrogate. Leave the value empty.
5. Create a new key under HKey_Local_Machine/Software/Classes/AppID, if it doesn't already exist.
6. Again, the new key should be called the same as the COM object's GUID. No values are necessary to be added under this key.

Another solution would be to use SQL clr invoke a WCF service. See my guide at the following for how to do this:

http://www.codeproject.com/Articles/21149/Invoking-a-WCF-Service-from-a-CLR-Trigger