Category Archives: SQL

Who Wants Me To Write A Book

Recently someone left me the following message on my new article (http://www.codeproject.com/KB/smart/GeoPlaces.aspx)

Hi Sacha,
I’m sure this has been said before but you really should write a book imho.
I’d buy it …
I’m not going to vote a 5 because I’d rather vote with my cash frankly!!
Oh, alright then … Wink
<stick>
Gold Star
</stick>
Seriously, write a book, but don’t do the predictable thing of picking a single technology. I like your whole approach and passion. This article would make a killer chapter in said book for instance …
Best Regards,

Jammer : 05/04/2009

Now this is not the 1st time someone has suggested that I write a book, I was just wondering how many people would actually like me to write a book. If you would could you leave a comment here, so that I can tell who would buy the book, and how many folk think it would be a good idea.

The sort of thing that I would try and cover is what I normally cover, which would be how to work with a mixture of technologies all used together, So this may include WCF/WPF/WF/LINQ/Custom controls/ASP .NET Silverlight all working together. Typically this would be to solve some problem that I make up, where the problem show cases how to use the technology really.

Let me know thanks

PS : I am running out of time to say thanks to all of you that have encouraged me to do this. I am going to look into it. I just wanted to say a big thanks to you all.

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