Create md5 Hash-code using Assemblies in SQL Server 2005

Introduction

It’s very simple to create an md5 hash function in such languages like C# or VB.NET. But in some cases, it’s necessary to implement md5-hash algorithm on SQL Server. Unfortunately, SQL Server does not have hash function like md5 and writing T-SQL script is not a good idea too. Performance of such script will be very low.

Using the Code

One way to solve this problem is to create a .NET assembly and attach it to SQL Server instance. Ok, let’s do it step by step:

1. Create a new project in Visual Studio. Choose class library template, enter project name and click Ok.
2. Write function that implements md5 hash-code. For this purpose, you need to add assembly “System.Security.Cryptography” in your project. Listing below shows md5 function.

   using System.Security.Cryptography;
   using System.Text;
   using System.Data;
   using System.Data.SqlTypes;
   namespace Md5Hash
   {
     public class Md5Class
     {
       [Microsoft.SqlServer.Server.SqlProcedure]
       public static void HashString(SqlString value, out SqlString result)
       {
         string str = value.ToString().Trim();
         HashAlgorithm mhash = mhash = new MD5CryptoServiceProvider();
         byte[] bytValue = System.Text.Encoding.UTF8.GetBytes(str);
         byte[] bytHash = mhash.ComputeHash(bytValue);
         mhash.Clear();
   
         StringBuilder sBuilder = new StringBuilder();
         // Loop through each byte of the hashed data
         // and format each one as a hexadecimal string.
         for (int i = 0; i < bytHash.Length; i++)
         {
            sBuilder.Append(bytHash[i].ToString("x2"));
         }
         // Return the hexadecimal string.
         result = sBuilder.ToString();
       }
     }
   } 

   If you want to use your assembly methods over SQL Server stored procedures, you should follow the next rules:

   • Your methods must be implemented as public static methods on a class in your assembly
   • Your methods must be declared as void or return integer value (in my example, I declare method as void)
   • Number of parameters must be the same as in stored procedures
   • All parameters must be declared according to SQL Server data types (see MSDN article http://msdn.microsoft.com/en-us/library/ms131092(v=SQL.90).aspx)

   Let’s get back to our listing. We have a class called “MD5Hash”. This class has only one method “HashString” marked with attribute [Microsoft.SqlServer.Server.SqlProcedure]. By this attribute, we define that our method will be used like a stored procedure. Method “HashString” takes two arguments:

   • value – SqlString data type parameter which hash-code we need to return
   • result - SqlString pass-by-reference data type parameter which stored procedure returning through an OUTPUT argument

   Build our project.

3. Now we should create a stored procedure on the SQL Server side. For using assemblies in T-SQL, we must create assembly object in SQL Server database. The following example shows how to do it:

create assembly a_md5Hash
from '\\server\SolutionFolder\bin\Debug\MD5Hash.dll'
go 

This SQL-statement creates a SQL assembly based on our .NET class liberally. The next step is to create a stored procedure which will use our assembly:

create procedure dbo.sp_md5Hash (@value nvarchar(20), _
@return nvarchar(max) output) as _
external name a_md5hash.[Md5Hash.Md5Class].HashString  

Argument as external name assembly_name.class_name.method_name specifies the method of a .NET Framework assembly for a CLR stored procedure to reference. Parameters:

• class_name is a namespace and class name of our .NET library.
• method_name is a name of md5 class method in .NET library.
• assembly_name is a name of our SQL Server assembly object.
• Ok. We have done it! Let’s try to get hash-code over our stored procedure:

declare @res nvarchar(max) exec dbo.sp_md5Hash 'Hello World!', @res output select @res 

Well, I’ve got 86fb269d190d2c85f6e0468ceca42a20. Good luck!

History

• 7^th January, 2011: Initial post