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 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')
Advertisements

13 thoughts on “SQL Server CLR Functions

  1. Ram says:

    Hey sacha,
    Thanks for the good article, i have been lookin for this solution long time… thanks man.. 🙂

    Ram

  2. sacha says:

    no problem

  3. mukesh says:

    may god bless u & u r very success.next time now chance so u new project competation u success

  4. Jonathan says:

    I may not understand your scenario, but did you consider the built-in encryption functions (in SQL 2005)?

    http://msdn2.microsoft.com/en-us/library/ms174361.aspx

  5. sacha says:

    Yeah our requirements cant use SQL Server 2005 functions. So I wrote a Dll, this was more about how to host CLR Dlls in SQL server 2005

  6. Paul says:

    Where can I find the code for Encrypt class that is referenced in using Encrypt;

    Thanks

  7. sacha says:

    You will not be able to. This is private code. But this article shows you the SQL CLR functions, so thats all that you should be taking from this.

    The Encrypt part is not important

  8. Khanh Phan says:

    Thank about your solution, but, there’s a big problem.

    I’m finding a best solution for secure data from sql server, because my clients want to protect their private information, and the encrypting function must be implement by their algorithm.

    Your solution basically resolves this problem, but, with large data type (such as NTEXT, TEXT), your solution can not do.

    So, can you give me another way to process NTEXT/TEXT data?

    Thanks a lot

  9. Bikash says:

    How are u referencing using encrypt…When I am adding a reference to my CLR project in Microsoft VS 2008, it only gives the list of certain dll’s.
    Actually, I want to add a dll which I have with some code. I want the functions of the dll to be used here in this. But I don’t know how to add the reference..Beside, it is not allowing by just adding with “using” clause. Require help!!!

  10. sacha says:

    I did pretty much what I stated above and it just worked for me.

  11. Thank you for your guidance…

  12. Jim Murphy says:

    Sacha – Thanks for the nice info about using CLR in SQL Server. Helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: