Consume 32 bit COM DLL in x64 Bit SQL Server 2005

Introduction

This article describes how to consume 32 bit COM component in 64 bit SQL Server 2005 instance via .NET CLR assembly and web service.

Background

Previously my applications were running on 32 bit SQL Server 2000 instance and we are using Rijndael.dll (32 bit DLL) to encrypt/decrypt strings through xp_OA created extended stored procedure. But when we migrated  to 64 bit SQL Server instance Rijndael was failing to load.

The reason was obvious, 32 bit DLL cannot be loaded in a 64 bit environment. We cannot leave this component stored in the data as encrypted by this component and algorithm of encryption/decryption, and we cannot reverse engineer the component.

And in CLR assembly (SQL Server project), we can't consume any external DLL except few that are part of the .NET Framework. You can't add a reference to any external assembly.

We can only get services of external components via asmx webservice and WCF services in CLR assembly.

So I have written one CLR assembly and one asmx web service.

To encrypt/decrypt strings in CLR assembly, we call web service and web service calls the COM component.

Configuration, Steps and Code Discussion  

First create a web service, add a reference to COM component on this and expose the web methods. Then create a New Database Project.

Set the assembly XML serialization to true, because this assembly will access webservice which is an external component. 

For making an external call, we should have the XML serialized assembly.

Now create a new SqlProcedure that will call the web service. 

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void EncryptString(string palinString)
    {
       //put webservice access code here     
    }

[Microsoft.SqlServer.Server.SqlProcedure]
   public static void DecryptString(string encryptedString)
   {
      //put webservice access code here
   }
}

Now build the Sqlserverproject.

Deployment  

• First we need to set our database CLR enabled, by executing:
  SQL

  EXEC sp_configure 'show advanced options' , '1';
  Go
  reconfigure;
  Go
  
  EXEC sp_configure 'clr enabled' , '1'
  Go
  reconfigure;
  Go

• Then we have to set our database TRUSTWORTHY
  SQL

  ALTER DATABASE <DB Name>
  SET TRUSTWORTHY ON

To deploy CLR assemblies, open SQL Server management studio, select the database where you would like and then run create Assembly commands.

First deploy SqlServerProject1.dll assembly. 

Create assembly SqlServerProject1 from C:\Documents and Settings\My Documents\Visual Studio 2008\Projects\CLRWebS\SqlServerProject1\bin\SqlServerProject1.dll.

WITH PERMISSION_SET = EXTERNAL

Then deploy Serialized assembly SqlServerProject1.XmlSerializers.dll.

Create assembly SqlServerProject1xml from C:\Documents and Settings\My Documents\Visual Studio 2008\Projects\CLRWebS\SqlServerProject1\bin\SqlServerProject1.XmlSerializers.dll.

WITH PERMISSION_SET = SAFE

Testing

After deploying the CLR, two stored procedures will be listed, and now you can easily test.

Future Enhancements

We can create a WCF service and host it as Windows service, so it will be more robust and result in reduced access time.