Today I had a problem with Encryption. I wanted to encrypt some data and then compare to the thing that user entered. It’s so easy to do it in .NET environment using System.Security.Cryptography but the problem was, the company had been developed an application using Visual Basic 6 and there was no way to change it.

That company had been developed an encryption algorithm to use in their own products and I didn’t know the structure. I called the old software’s developer and he told me that “I cannot change my structure!”. “The only thing I can do for you is to pass you passwords which users entered”, he said. After lots of challenges, He accepted to add some codes to his application in order to save another password is another database field. So I decided to create a User-Defined Function in order to encrypt my data using MD5 algorithm.

As a matter of fact, I was gotten so far from SQL Server programming since the release of LINQ and after this long time, I got back to the SQL Server T-SQL programming.

In this post, I’m going to tell you how to encrypt data using Microsoft SQL Server.

Encrypting data in SQL Server is much easier than .NET, in my opinion. The HashBytes function will enables you to do so!

Note: HashBytes function is only available in Microsoft SQL Server 2005 and 2008.

Here is the structure:

HashBytes ( <algorithm>, { @input | 'input' } )

And here is the sample:

DECLARE @HashThis nvarchar(4000);

SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');

SELECT HashBytes('SHA1', @HashThis);

You can also convert it to HEX. To do this you need to make a use of a UDF in the master database named “fn_varbintohexstr” witch do it for you!

This is one of those functions that look simple but you cannot believe how much time I spent in order find it out :-); because I couldn’t find it even in MSDN!

So the code will change a little:

DECLARE @HashThis nvarchar(4000);

SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');

SELECT master.dbo.fn_varbintohexstr(HashBytes('SHA1', @HashThis));