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 string
s 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)
{
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void DecryptString(string encryptedString)
{
}
}
Now build the Sqlserverproject
.
Deployment
- First we need to set our database CLR enabled, by executing:
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
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.