https://sqlity.net/en/2460/sql-password-hash/
Introduction
I am often asked how SQL Server protects the passwords for SQL Logins. We know already that the passwords are hashed and that the hashed value can be queried using the sys.sql_logins catalog view. But, how is this value actually calculated? How do you get from a password to its hash?
SHA_512 with Salt
Since SQL Server 2012, passwords are stored using the SHA_512 hashing algorithm with a 32-bit salt.
SHA_512 is twelve years old and it is starting to show a few signs of ageing. All SHA algorithms are based on multiple rounds. While there has not been a successful attack against the full number of rounds (80), the number of rounds that can be broken is rising. Now, in a real application, you would always use all 80 rounds, and if you do, the algorithm is still secure. However, the rising number of broken rounds means that it likely will be broken within the next few years.
Calculating the Hash Value
The first step to calculate the hash value is to convert the password from NVARCHAR to VARBINARY. Afterwards SQL Server uses a CSPRNG to generate the 32-bit Salt and append it to the converted password. Of this new concatenated VARBINARY value SQL Server calculates the SHA_512 hash. The last step is to concatenate 0x0200, the salt and the calculated hash together to build the stored hash value.
The code below demonstrates this calculation:
DECLARE @pswd NVARCHAR(MAX) = 'APassword';
DECLARE @salt VARBINARY(4) = CRYPT_GEN_RANDOM(4);
DECLARE @hash VARBINARY(MAX);
SET @hash = 0x0200 + @salt + HASHBYTES('SHA2_512', CAST(@pswd AS VARBINARY(MAX)) + @salt);
SELECT @hash AS HashValue, PWDCOMPARE(@pswd,@hash) AS IsPasswordHash;
We can use the PWDCOMPARE function to confirm that our result is indeed a valid SQL Server password hash:
Other SQL Server Versions
SQL Server versions before SQL Server 2012 used a very similar algorithm. The only difference is that instead of SHA_512, SHA1 was used. The first two bytes of the hash value are a version indicator. For SHA1 hashes, this version number is set to 0x0100. If you upgrade a server from an old version to 2012 or later, the stored passwords are not automatically upgraded. Therefore the PWDCOMPARE function can actually read both versions.
Below is the adapted version of the code to calculate the hash value. It runs on all SQL Server versions from SQL Server 2005 onwards.
DECLARE @pswd NVARCHAR(MAX); SET @pswd = 'APassword';
DECLARE @salt VARBINARY(4); SET @salt = CAST(NEWID() AS VARBINARY(4));
DECLARE @hash VARBINARY(MAX);
SET @hash = 0x0100 + @salt +
HASHBYTES('SHA1', CAST(@pswd AS VARBINARY(MAX)) + @salt);
SELECT @hash AS HashValue, PWDCOMPARE(@pswd,@hash) AS IsPasswordHash;
Because the CRYPT_GEN_RANDOM function was introduced in SQL Server 2008, the above code uses NEWID as an ersatz function. However, that is not really an adequate replacement, so do not use it in your application.
SQL Server 2000
Even SQL Server 2000 used already the SHA1 function to hash the passwords. However, passwords were not case sensitive at that time. The actual hash value therefore contained two hashed versions of the password, one of the unchanged password and one of the all-caps version of the password:
SET @hash = 0x0100 + @salt +
HASHBYTES('SHA1', CAST(@pswd AS VARBINARY(MAX)) + @salt) +
HASHBYTES('SHA1', CAST(UPPER(@pswd) AS VARBINARY(MAX)) + @salt);
If you have a an old hash value like that, PWDCOMPARE can still work with it. However, it ignores the all caps version. That means that an upgrade from SQL 2000 to a new version potentially breaks passwords. If you are one of the few people that still have to work with SQL 2000, keep this in mind when you finally upgrade.
Summary
SQL Server stores the passwords for SQL logins as a salted hash value. For this, SQL Server versions 2012 and later use the SHA_512 algorithm and a 32-bit salt.