[sql-server] How to decrypt a password from SQL server?

The SQL Server password hashing algorithm:

hashBytes = 0x0100 | fourByteSalt | SHA1(utf16EncodedPassword+fourByteSalt)

For example, to hash the password "correct horse battery staple". First we generate some random salt:

fourByteSalt = 0x9A664D79;

And then hash the password (encoded in UTF-16) along with the salt:

 SHA1("correct horse battery staple" + 0x9A66D79);
=SHA1(0x63006F007200720065006300740020006200610074007400650072007900200068006F00720073006500200073007400610070006C006500 0x9A66D79)
=0x6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3

The value stored in the syslogins table is the concatenation of:

[header] + [salt] + [hash]
0x0100 9A664D79 6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3

Which you can see in SQL Server:

SELECT 
   name, CAST(password AS varbinary(max)) AS PasswordHash
FROM sys.syslogins
WHERE name = 'sa'

name  PasswordHash
====  ======================================================
sa    0x01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3

• Version header: 0100
• Salt (four bytes): 9A664D79
• Hash: 6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3 (SHA-1 is 20 bytes; 160 bits)

Validation

You validate a password by performing the same hash:

• grab the salt from the saved PasswordHash: 0x9A664D79

and perform the hash again:

SHA1("correct horse battery staple" + 0x9A66D79);

which will come out to the same hash, and you know the password is correct.

What once was good, but now is weak

The hashing algorithm introduced with SQL Server 7, in 1999, was good for 1999.

• It is good that the password hash salted.
• It is good to append the salt to the password, rather than prepend it.

But today it is out-dated. It only runs the hash once, where it should run it a few thousand times, in order to thwart brute-force attacks.

In fact, Microsoft's Baseline Security Analyzer will, as part of it's checks, attempt to bruteforce passwords. If it guesses any, it reports the passwords as weak. And it does get some.

Brute Forcing

To help you test some passwords:

DECLARE @hash varbinary(max)
SET @hash = 0x01009A664D796EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3
--Header: 0x0100
--Salt:   0x9A664D79
--Hash:   0x6EDB2FA35E3B8FAB4DBA2FFB62F5426B67FE54A3

DECLARE @password nvarchar(max)
SET @password = 'password'

SELECT
    @password AS CandidatePassword,
    @hash AS PasswordHash,

    --Header
    0x0100
    +
    --Salt
    CONVERT(VARBINARY(4), SUBSTRING(CONVERT(NVARCHAR(MAX), @hash), 2, 2))
    +
    --SHA1 of Password + Salt
    HASHBYTES('SHA1', @password + SUBSTRING(CONVERT(NVARCHAR(MAX), @hash), 2, 2))

SQL Server 2012 and SHA-512

Starting with SQL Server 2012, Microsoft switched to using SHA-2 512-bit:

hashBytes = 0x0200 | fourByteSalt | SHA512(utf16EncodedPassword+fourByteSalt)

Changing the version prefix to 0x0200:

SELECT 
   name, CAST(password AS varbinary(max)) AS PasswordHash
FROM sys.syslogins

name  PasswordHash
----  --------------------------------
xkcd  0x02006A80BA229556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38

• Version: 0200 (SHA-2 256-bit)
• Salt: 6A80BA22
• Hash (64 bytes): 9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38

This means we hash the UTF-16 encoded password, with the salt suffix:

• SHA512("correct horse battery staple"+6A80BA22)
• SHA512(63006f0072007200650063007400200068006f0072007300650020006200610074007400650072007900200073007400610070006c006500 + 6A80BA22)
• 9556EB280AA7818FAF63A0DA8D6B7B120C6760F0EB0CB5BB320A961B04BD0836 0C0E8CC4C326220501147D6A9ABD2A006B33DEC99FCF1A822393FC66226B7D38