Click here to Skip to main content
13,350,861 members (52,679 online)
Click here to Skip to main content
Add your own
alternative version


6 bookmarked
Posted 7 Jan 2011

Create md5 Hash-code using Assemblies in SQL Server 2005

, 7 Jan 2011
Rate this:
Please Sign up or sign in to vote.
Create md5 hash-code using assemblies in SQL Server 2005


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
        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);
          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++)
          // 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

    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:
  4. create assembly a_md5Hash
    from '\\server\SolutionFolder\bin\Debug\MD5Hash.dll'

    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!


  • 7th January, 2011: Initial post


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


About the Author

Denis Strukachev
Software Developer
Russian Federation Russian Federation
I have 5 years of experience developing enterprise software, starting from Delphi and moving towards C# on Windows platforms.

Visit my blog

My Facebook Profile here

You may also be interested in...


Comments and Discussions

GeneralMy vote of 1 Pin
Nathan J Bolstad10-Jan-11 11:51
memberNathan J Bolstad10-Jan-11 11:51 
Posting articles about features that are already included in a product just makes like more difficult for those who are just trying to get (good) answers to existing problems.

Reinventing a custom piece of code Vs code that is being executed by thousands of solutions (i.e. Hashbytes) is also poor form.

The only people who should be commenting and publishing articles on computer/computational security topics are *Experts*.

Whilst this seems harsh...just wait until you have 15 years of experience, and had to fix some *terrible* implementations by developers who supposedly *has* experience...bit in fact are ignorant.
GeneralRe: My vote of 1 Pin
regeter13-Jun-11 7:06
memberregeter13-Jun-11 7:06 
GeneralHashBytes Pin
Richard Deeming10-Jan-11 8:01
memberRichard Deeming10-Jan-11 8:01 
GeneralThanks for the article Pin
Keith Vinson10-Jan-11 4:56
memberKeith Vinson10-Jan-11 4:56 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180111.1 | Last Updated 7 Jan 2011
Article Copyright 2011 by Denis Strukachev
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid