Click here to Skip to main content
Click here to Skip to main content

SQL Server CLR Functions

By , 17 Jun 2009
Rate this:
Please Sign up or sign in to vote.

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 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 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’)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Sacha Barber
Software Developer (Senior)
United Kingdom United Kingdom
I currently hold the following qualifications (amongst others, I also studied Music Technology and Electronics, for my sins)
 
- MSc (Passed with distinctions), in Information Technology for E-Commerce
- BSc Hons (1st class) in Computer Science & Artificial Intelligence
 
Both of these at Sussex University UK.
 
Award(s)

I am lucky enough to have won a few awards for Zany Crazy code articles over the years

  • Microsoft C# MVP 2014
  • Codeproject MVP 2014
  • Microsoft C# MVP 2013
  • Codeproject MVP 2013
  • Microsoft C# MVP 2012
  • Codeproject MVP 2012
  • Microsoft C# MVP 2011
  • Codeproject MVP 2011
  • Microsoft C# MVP 2010
  • Codeproject MVP 2010
  • Microsoft C# MVP 2009
  • Codeproject MVP 2009
  • Microsoft C# MVP 2008
  • Codeproject MVP 2008
  • And numerous codeproject awards which you can see over at my blog

Comments and Discussions

 
QuestionThank you for the explanation Pinmemberednrg15-Aug-12 3:12 
AnswerRe: Thank you for the explanation PinmvpSacha Barber15-Aug-12 4:05 
GeneralMy vote of 5 Pinmembermustafabayer28-May-12 3:37 
GeneralPlease give some time PinmemberMember 477333628-Apr-11 18:58 
GeneralA comment on SQL CLR Functions... PinmemberLennart i Göteborg12-Aug-09 2:00 
GeneralExcellent PinmemberAbhishek Sur6-Aug-09 9:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 17 Jun 2009
Article Copyright 2009 by Sacha Barber
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid