Is there an equivalent to SHA1() in MS-SQL?

Question

Converting a couple stored procedures from MySQL to Microsoft SQL server. Everything is going well, except one procedure used the MySQL SHA1() function. I cannot seem to find an equivalent to this in MS-SQL.

Does anyone know a valid equivalent for SHA1() on MS-SQL?

If this is for password storage, I feel compelled to mention that simply hashing the raw string is not good enough in most cases. Here's why: http://www.md5decrypter.co.uk/sha1-decrypt.aspx ... among many others... There are tons of rainbow tables out there and reversing an un-salted password is pretty trivial these days because of it. — dodexahedron, Aug 31 '12 at 01:59

score 52 · Accepted Answer · answered Oct 08 '08 at 19:31

52

SQL Server 2005 and later has the HashBytes() function.

answered Oct 08 '08 at 19:31

Joel Coehoorn

399,467
113
570
794

10

Looks perfect, answered while I took a leak. I love stackoverflow. Thanks! – GEOCHET Oct 08 '08 at 19:34
1

Now if they'd just get support for a 256-bit algo instead of only 160 – Joel Coehoorn Oct 08 '08 at 19:41

score 31 · Answer 2 · answered Aug 31 '12 at 01:44

If you want to get a SHA1 hash exactly as MySQL would generate it (i.e. as a varchar), you can combine HashBytes with sys.fn_varbintohexsubstring. E.g.

SELECT sys.fn_varbintohexsubstring(0, HashBytes('SHA1', 'password'), 1, 0)

See http://accessrichard.blogspot.co.nz/2010/12/sql-server-and-net-equivalent-to-php.html for more details.

score 3 · Answer 3 · answered Jan 28 '21 at 09:06

MSSQL Server

HASHBYTES('SHA1', CAST('abcd@#' as nvarchar(max)))
CONVERT(VARCHAR(MAX), HASHBYTES('SHA1', CAST('abcd@#' as nvarchar(max))) , 2)

/* result */
0x77DD873DBAB2D81786AB9AE6EA91B1F59980E48C  
77DD873DBAB2D81786AB9AE6EA91B1F59980E48C

C#

using (SHA1Managed sha1 = new SHA1Managed())
{
    string input = "abcd@#";
    var hash = sha1.ComputeHash(Encoding.Unicode.GetBytes(input));
    var sb = new StringBuilder(hash.Length * 2);
    
    foreach (byte b in hash)
    {
        sb.Append(b.ToString("X2")); // can be "x2" if you want lowercase
    }
    return sb.ToString();
}
//result "77DD873DBAB2D81786AB9AE6EA91B1F59980E48C"

score 2 · Answer 4 · answered Oct 08 '08 at 19:34

2

From google groups - A Possibility

answered Oct 08 '08 at 19:34

LeppyR64

5,251
2
30
35

score 1 · Answer 5 · answered Feb 07 '11 at 13:02

1

You may also want to check out http://www.stev.org/post/2011/01/30/MS-SQL-SHASum-Support.aspx you should be able to modify it to produce anything you want. Though some c# coding may be required.

answered Feb 07 '11 at 13:02

James

11
1

score 0 · Answer 6 · answered Oct 08 '08 at 19:34

0

I don't believe there's native support, but you may want to check this out...

http://blogs.msdn.com/sqlcat/archive/2005/09/16/469257.aspx

answered Oct 08 '08 at 19:34

theraccoonbear

4,283
3
33
41

Is there an equivalent to SHA1() in MS-SQL?

6 Answers6

Linked