A little while ago I came upon a situation where I needed to put a CRL procedure into SQL Server 2005. Now I’ve been using SQL Server 2005 since the day it went RTM, and this is the first occasion that I’ve needed to put a CLR procedure into SQL. Could I have done what I needed to without a CRL procedure? Yeah probably, but the .NET code was written, and had been working.
My code is written in C# as that’s what the .NET guys here use. There is one method within the C# class called HashPassword. It takes a text string and makes a SHA1 Hash of it which we then store. We had to make a couple of changes to the C# code to make it work within SQL Server.
The original code looked like this.
using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Text;
namespace Rapid.Database.Security{
{
public class User
public static void HashPassword(String password, out String hash)
{
SHA1 sha1 = new SHA1CryptoServiceProvider();hash =
BitConverter.ToString(sha1.ComputeHash(UnicodeEncoding.Default.GetBytes(password.ToLower()))).Replace("-", "");
}
}
}
The changed C# code looks like this.
using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using Microsoft.SqlServer.Server;
namespace Rapid.Database.Security
{
public class User
{
[SqlProcedure(Name=“HashPassword”)]
public static void HashPassword(String password, out String hash)
{
SHA1 sha1 = new SHA1CryptoServiceProvider();hash =
BitConverter.ToString(sha1.ComputeHash(UnicodeEncoding.Default.GetBytes(password.ToLower()))).Replace("-", "");
}
}
}
Once these changes are made and the DLL recompiled we can attach the DLL to the SQL Server database as an assembly. This is done with the CREATE ASSEMBLY command. I have to use the EXTERNAL_ACCESS flag instead of the SAFE flag because my dll requires the use of the System.IO assembly which can’t be run as SAFE under SQL Server 2005.
CREATE ASSEMBLY [Rapid.Database.Security]AUTHORIZATION [dbo]
FROM ‘D:Rapid.Database.Security.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
We can now create our procedure which needs to have one input and one output parameter to match the number of parameters within the C# procedure. This is done with the CREATE PROCEDURE command just like creating any normal procedure.
CREATE PROCEDURE [dbo].[HashPassword]
@Password [nvarchar](50),
@PasswordHash [nvarchar](50) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Rapid.Database.Security].[Rapid.Database.Security.User].[HashPassword]
GO
The three part name of the C# method are:
- Assembly name which SQL Server knows about (basically the name of the DLL without the file extension).
- The full name to the class, in our case the namespace then the class. Our namespace is Rapid.Database.Security with the name of the class after that.
- The name of the method within the C# class.
Don’t forget to enable CLR within SQL Server using the sp_configure before trying to use the CLR procedure.
At this point I am able to run the procedure and pass in text and get back the hash.
declare @PasswordHash varchar(50)
exec HashPassword @password=‘test’, @passwordHash=@PasswordHash OUTPUT
SELECT @PasswordHash
The value of this is: A94A8FE5CCB19BA61C4C0873D391E987982FBBD3
I hope that you find this useful. It took me and a .NET developer a couple of hours to get all this working correctly and loaded into the SQL Server correctly. I know that I’ll be referring back to this if I ever need to attach a CLR method into SQL again.
Do keep in mind that when we were doing this, we created this as a separate DLL for the SQL Server as to get the original DLL into the SQL Server, SQL wanted us to load up a bunch of other assemblies that some of the other methods required.
Denny