SQL Server CLR Functions

Today, we had a requirement to do some pretty strange stuff in SQL which required us to call an encryption library in SQL server. This is something I had not done before, so I thought I would blog about it.

There are several steps involved:

1. Create the CLR DLL for the SQL function to use, and copy it to SQL Binn
2. Register the CLR DLL in SQL Server
3. Create a normal SQL function that uses the CLR DLL

The first part is straight forward enough, the following code gives an example:

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Text;
   4:  using Microsoft.SqlServer.Server;
   5:  using System.Data;
   6:  using System.Data.Sql;
   7:  using System.Data.SqlTypes;
   8:  using Encrypt;
   9:  
  10:  public class StoredProcedures
  11:  {
  12:  
  13:      [Microsoft.SqlServer.Server.SqlFunction()]
  14:      public static string Enc(SqlString password, SqlString encStringOut)
  15:      {
  16:          Encryption enc = new Encryption();
  17:          return enc.Encrypt(password.ToString(), encStringOut.ToString());;
  18:      }
  19:  
  20:      [Microsoft.SqlServer.Server.SqlFunction()]
  21:      public static string Dec(SqlString password, SqlString encStringOut)
  22:      {
  23:          Encryption enc = new Encryption();
  24:          return enc.Decrypt(password.ToString(), encStringOut.ToString()); ;
  25:      }
  26:  }

So that's easy enough. Compile this job done.

So next, we need to do the SQL server work. So firstly, I copied the SQLServerEncryption.Dll to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn directory of the SQL server machine.

I also copied the DLL generated (SQLServerEncryption.Dll) to the C:\ drive on the SQL server machine, as it makes the DLL registration code that needs to be run for SQL a bit easier.

So we’ve copied to \binn and C:\ so far, so now we need to register the DLL with SQL server. So let's look at that.

Firstly, we need to allow CLR types in the SQL server installation. Which is either done using the following SQL:

EXEC dbo.sp_configure ‘clr enabled’,1 RECONFIGURE WITH

Or if you have issues doing it that way, use the SQL Server Surface Area Configuration, use the “Surface Area Configuration For Features” link, and then CLR integration from the presented treeview. Once this is done, we can register the CLR DLL with SQL, as follows:

create assembly SQLServerEncryption from ‘c:SQLServerEncryption.dll’ WITH PERMISSION_SET = SAFE

Now that we've done that, all that's left to do is create a normal SQL server function that uses the CLR DLL. Which is simply done as follows:

ALTER FUNCTION [dbo].[ENCRYPT](@password [nvarchar](255), @encStringOut [nvarchar](255))
RETURNS [nvarchar](255) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerEncryption].[StoredProcedures].[Enc]

And that's it! You can now use the CLR Function as you like. For example:

dbo.ENCRYPT(‘xxxx’,’sb_SQL’)