Click here to Skip to main content
15,608,989 members
Articles / Database Development / SQL Server
Posted 17 Jun 2009

Tagged as


22 bookmarked

SQL Server CLR Functions

Rate me:
Please Sign up or sign in to vote.
5.00/5 (11 votes)
17 Jun 2009CPOL1 min read
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;
10:  public class StoredProcedures
11:  {
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:      }
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))
EXTERNAL NAME [SQLServerEncryption].[StoredProcedures].[Enc]

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



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

Written By
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.


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

  • Microsoft C# MVP 2016
  • Codeproject MVP 2016
  • Microsoft C# MVP 2015
  • Codeproject MVP 2015
  • 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

GeneralMy vote of 5 Pin
Member 1507871611-Jul-22 10:42
Member 1507871611-Jul-22 10:42 
QuestionThank you for the explanation Pin
ednrg15-Aug-12 3:12
ednrg15-Aug-12 3:12 
AnswerRe: Thank you for the explanation Pin
Sacha Barber15-Aug-12 4:05
Sacha Barber15-Aug-12 4:05 
GeneralMy vote of 5 Pin
mustafabayer28-May-12 3:37
mustafabayer28-May-12 3:37 
GeneralPlease give some time Pin
Member 477333628-Apr-11 18:58
Member 477333628-Apr-11 18:58 
GeneralA comment on SQL CLR Functions... Pin
Lennart i Helsingborg12-Aug-09 2:00
Lennart i Helsingborg12-Aug-09 2:00 
GeneralExcellent Pin
Abhishek Sur6-Aug-09 9:32
professionalAbhishek Sur6-Aug-09 9:32 

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

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