SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Encrypting passwords using HashBytes('MD5',@PW)

Encrypting passwords using HashBytes('MD5',@PW)

SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 31
We have been using a .NET assembly in our SQL Server 2008 database to perform the MD5Hash of passwords in some circumstances. ( Via a function ) This causes our server crew headaches, because it means that the right versions of .NET Frameworks have to be present in the database every time they do an upgrade or introduce a new server.

I decided to replace this assembly and function with a simpler one using the T-SQL HASHBYTES('MD5', @PW).

Initial testing was successfull I used the Wikipedia example:
MD5("The quick brown fox jumps over the lazy dog") = 9e107d9d372bb6826bd81d3542a419d6

which gave this same result in both the assembly based function and the HashBytes based function.

Then I decided to try it on a larger data set. I used the usernames from a usertable as the source of text to be hashed. In "most" cases both functions produced the same result.

Carefull analysis showed that it failed when the username contained any non-standard ASCII codes. e.g Æ Ø Å Ü Â etc
Reading the online documentation for CONVERT led me to these two sections of documentation:

"The binary data types are not for hexadecimal data but rather for bit patterns. Conversions and calculations of hexadecimal numbers stored as binary data can be unreliable."

... I found this disconcerting, and...

"Note Because Unicode data always uses an even number of bytes, use caution when converting binary or varbinary to or from Unicode supported data types. For example, this conversion does not return a hexadecimal value of 41, but of 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)"

Since I dont have access to the source code for the old assembly I cannot determine whether the author ( who has left the scene ) hs made a mistake or whether I need to do some more tricks for my use of HashBytes to return the same result as the assembly.

Here is my function:

CREATE FUNCTION [dbo].[fn_Md5Hash_TSQL](@S1 nvarchar(50))
RETURNS nvarchar(100)
@result varbinary(4000),
@S2 varchar(100),
@X nvarchar(100);

SET @S2 = CONVERT( VARCHAR(100), @S1 );
SET @result = HASHBYTES('MD5', @S2);
SET @X = LOWER(CAST('' AS XML).value('xs:hexBinary(sql:variable("@result"))', 'NVARCHAR(100)') )



SET @name = 'AA';
--SET @name = 'AÅ';

SELECT 'old', @name, [dbo].[fn_Md5Hash](@name);
SELECT 'new', @name, [dbo].[fn_Md5Hash_TSQL](@name);

old AA 3b98e2dffc6cb06a89dcb0d5c60a0206
new AA 3b98e2dffc6cb06a89dcb0d5c60a0206

( Identical )

And if the 'AÅ' variant is used:

old AÅ 7645be138eec3e3f19220555fb965501
new AÅ 6a8e7a8270458b4fd99c2d12f4bb74ca

( Different )

( I cannot explain why we have the hashed password as a string and not left as a varbinary, it is just happens to be that way in the database. This is not an issue for the Hashing problem though. )

Also I got different values for all password if I did not first convert the NVARCHAR parameter to a VARCHAR variable before hashing. This could be an indicator that something is awry in the assembly.

One solution is to just introduce the new function and wait for people to complain about login problems before clearing each password on an as-needed basis. Of the 40,000 users only a small amount (hopefully !!) will have used one of these characters in the password. However, this system is up 24/7 and the support department is not. In some situations a successfull login might have great importance to the user.



You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum