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 1st 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 thats 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 FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn 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 lets 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 weve done that, all thats 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 it you can now use the CLR Function as you like. For example
dbo.ENCRYPT('xxxx','sb_SQL')