8

I need to hash (MD5) all the password in our Sql Server 2000 database. I can easily generate a C#/VB.NET program to convert (hash) all the passwords, but I was wondering (more for my education than for a real compelling need) if it was possible to calculate MD5 hash directly in T-SQL.
Thanks to anyone who will answer.

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
M.Turrini
  • 738
  • 1
  • 4
  • 19

6 Answers6

7

In 2005 and later, you can call the HashBytes() function. In 2000, the closest thing is pwdencrypt/pwdcompare, though those functions have their own pitfalls (read the comments of the link).

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Thank you very much for your kind reply. At the end I choose Daniel's answer because I was looking for MD5 hashing, but your one came really close and the suggestion about pwdencryt/pwdcompare made me learn something really interesting. – M.Turrini May 26 '09 at 15:22
3

It is using this code, but it is not native to the language.

http://www.codeproject.com/KB/database/xp_md5.aspx

Daniel A. White
  • 187,200
  • 47
  • 362
  • 445
  • Thank you very much. I hoped to find a way to do it directly with SQL statements, but this is good too! – M.Turrini May 26 '09 at 15:20
2

No, there is no native TSQL command to generate MD5 hash's in SQL Server 2000.

In 2005 and above you can use the HashBytes function: http://msdn.microsoft.com/en-us/library/ms174415.aspx

samjudson
  • 56,243
  • 7
  • 59
  • 69
1

Please see below example/solution using 2008 

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('md5', @HashThis);
GO
Eduardo Belmonte
  • 11
  • 1
0

There is nothing magical about md5, you can implement it as a pure tsql function if you want to. I'm not sure it would be fun in tsql, but there should be nothing preventing you from doing so :)

ahains
  • 1,912
  • 12
  • 10
-1

Just for the record:

UPDATE T_WHATEVER_YOUR_TABLE_NAME_IS 
    SET PREFIX_Hash = LOWER(SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', LOWER('a-string-with-utf8-encoded-international-text'))), 3, 32) )
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442