SQL CLR Assembly Error 80131051 when late binding to a registered C# COM .dll
- by Shanubus
I must have hit an unusual one, because I can't find any reference to this specific failing anywhere...
Scenario: I have a legacy SQL function used to transform(encrypt) data. This function is called from within many stored procedures used by multiple applications. I say this, because the obvious answer of 'just call it from your code' is not really an option (or at least one I'd prefer not explore).
The legacy function used sp_OA with an ActiveX dll on SQL2000 to perform its work. The new function is targeted at SQL2008 x64. I am ditching the sp_OA call in favor of CLR assembly; and am getting rid of the ActiveX dll and using a COM+ .dll (3rd party) to perform the same work. This 3rd party COM+ is required to be used based on spec given to me, so can't get rid of this piece either.
Problem: After multiple attempts at getting this to work I have eliminated the following approaches
1) Create a Sql Assembly to call the local COM+ directly -- Can't do this as it requires a reference to System.EnterpriseServices. Including this requires that a whole slew of unsupported assemblies be registered which I don't want. The COM+ requires it's methods to be accessed via an Interface, so my attempts at late binding to it directly have not been successful (late binding would allow me to drop the unsupported references).
2) Create a Sql Assembly which references a C# class library that then calls the COM+. -- Same issue as #1; since the referenced dll uses System.EnterpriseServices and will be added as a dependency when referenced in the Sql Assembly, again trying to load all the unsupported libraries
3) Create a Sql Assembly which late binds to an ActiveX COM dll that calls the COM+. -- Worked in my dev environment, but can't go to x64 in production with ActiveX dll's written in VB6 (not to mention I hate backtracking anyway)... again failure...
I am now onto an approach that is almost working, with of course one last hangup.
I now have
-a Sql Assembly that late binds to a C# COM dll, eliminating the need for including System.EnterpriseServices and eliminating the need to reference the C# COM in the SqlAssembly itself. The C# COM does reference System.EnterpriseServices to call the COM+, but since I am late binding to it from the SqlAssembly, I bypass the need for Sql to actually load them as referenced assemblies.
Works in debugger..
Works on my dev box when the SqlAssembly dll is referenced in a test console app and called directly
Installs to Sql2008 just fine
Executing the actual UDF works, but returns no data due to a failure reporting from the late bound dll!
So the SqlAssembly is instanciated just fine. It actually fails on it's late binding to the C# COM, which is working from a test console app on the same machine. It appears to be a difference in behavior based on whether called from within the SQL UDF or not.
Since it is working on the same box from my console app, I am assuming it's on the SQL side. My steps to install were.
--Install the COM+ dll and ensure it can be called successfully (as from with in the console app)
--Register the C# COM dll (which calls the COM+) and get it to the GAC (again proofed to be working from console app)
--Create my Assymetric Key
CREATE ASYMMETRIC KEY SqlCryptoKey FROM EXECUTABLE FILE = 'D:\SqlEx.dll'
CREATE LOGIN SqlExLogin FROM ASYMMETRIC KEY SqlExKey
GRANT UNSAFE ASSEMBLY TO SqlExLogin
GO
--Add the assembly
CREATE ASSEMBLY SqlEx
FROM 'D:\SqlEx.dll'
WITH PERMISSION_SET = UNSAFE;
GO
--Create the function
CREATE FUNCTION dbo.f_SqlEx(
@clearText [nvarchar](512)
)
RETURNS nvarchar(512) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME SqlEx.[SqlEx.SqlEx].Ex
GO
With all that done, I can now call my function
SELECT dbo.f_SqlEx('test')
But get this error in the event log...
Retrieving the COM class factory for
component with CLSID
{F69D6320-5884-323F-936A-7657946604BE}
failed due to the following error:
80131051.
I can't really provide direct code examples, due to internal security implications; but all the code itself seems to work, I am suspecting perms or something of the like... I just find it odd that I can't find any reference to error 80131051. If someone out there believe some 'indirect' code samples will help, I will be happy to provide.
Any assistance is appreciated.
