Two Things

Gaming and Programming.. maybe programming for some games, who knows. Either way, I'm a geek, right?

32 bit COM DLL in 64-bit SQL 2008+

Old VB6-written COM object that needs to be accessed in queries from a 64-bit SQL 2012 database.  There may be many ways to do this, or this might be the only way.. and involves some registry magic.

1. Write a wrapper in C#

A simple class to load up the object (assuming the DLL is registered on the server of course), using System.Activator to spin up an instance of the DLL and a bit of reflection to interact with it.  This is out of scope for this article but I can share if needed.

2. Compile the wrapper as a DLL using csc.

csc is inside your framework folder somewhere.  Create the DLL using the .cs file you wrote above.  Example:  "csc /target:library mywrapper.cs"

3. Add the assembly into SQL Server.

CREATE ASSEMBLY MyCOMObject FROM 'C:\somewhere\mywrapper.dll' WITH PERMISSION_SET = UNSAFE
GO

In this case, "UNSAFE" is SQL's version of "Full Trust".  The default is "SAFE", and "EXTERNAL_ACCESS" does not give enough permissions for this DLL to interface with other DLLs (our COM object) so "UNSAFE" is the proper level of trust for this.

4. Registry magic to make the assembly work.

1. run regedit
2. find the name of your COM object  --it should be at HKLM\Software\Classes\NameOfCOMObject
3. Copy the Clsid value, brackets and all.  e.g. {C869B444-52AB-3EEE-3332-555B021ABCD2}
4. Navigate to HKEY_CLASSES_ROOT\Wow6432Node\CLSID\YourClassIDFromStep3
5. Add a string value, named AppID with value of the ClassID
6. Navigate to  HKEY_CLASSES_ROOT\Wow6432Node\AppID\
7. Add a new Key named the same as the ClassID
8. Inside that new Key that you just added, add a string value named DllSurrogate with no value
9. Navigate to HKEY_LOCAL_MACHINE\Software\Classes\AppID\
10. Create a new Key named the same as the ClassID.  No additional values are needed.

5. Grant Permissions to local SQL accounts.

1. run dcomcnfg
2. Expand Component Services / Computers / right click My Computer and Properties
3. On the COM Security tab, Access Permissions > Edit Default
4. Add MSSQLSERVER and SQL Server Distributed Relay Client with LocalAccess, then OK
5. On the COM Security tab, Launch and Activation Permissions > Edit Default
6. Add MSSQLSERVER and SQL Server Distributed Relay Client with Local Launch and Activation, then OK

6. Create a Function and Test.

CREATE FUNCTION CLR_Test(@input nvarchar(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME MyCOMObject.NameSpace.FunctionName
GO

SELECT dbo.CLR_Test('whatever')

If you get an access denied error, check your Event Viewer / System Log to see which user account is accessing the COM object.  You will need to repeat step 5 to grant them the Component Services permissions.

7. Lastly

For each database that you register your assembly to, you need to turn on the TRUSTWORTHY flag.

ALTER database SET TRUSTWORTHY ON

You are encouraged to do your own due diligence to see what all of these options do and whether they work for you in your environment.  This is just how I solved it in my environment.  It was a pain but, hey, after the pain comes the pleasure, and being able to use an ancient COM library inside a SQL statement does feel good, even if it's cheating.