Introduction
We all know that as security threats evolve, we must update our defenses to mitigate newer threats. Over the last few months, some customers have asked us to strengthen the way we secure passwords in SQL Server. The most often-cited reference by customers is to comply with NIST SP 800-63b.
Currently supported versions of SQL Server and Azure SQL DB use a SHA-512 hash with a 32-bit random and unique salt. It is statistically infeasible for an attacker to deduce the password knowing just the hash and the salt. It is considerably easier for an attacker to hunt for insecure storage of database connection strings that contain database credentials than it is to break the password verifier (also called a password authenticator) used by SQL Server and Azure SQL DB. But that’s a discussion for another day and is the main reason we highly recommend using Entra ID authentication rather than using uid/pwd-based connections because Entra ID authentication manages credentials and supports access policies.
Considering NIST SP 800-63b, we have added an iterator to the password verifier algorithm. Rather than just storing the SHA-512 hash of the password:
H = SHA512(salt, password)
In this update, we create an iterated hash:
H0 = SHA512(salt, 0, password);
for n=1 to 100,000
Hn = SHA512(salt, Hn-1, password)
The actual algorithm is RFC2898 often called a Password-based Key Derivation Function or PBKDF.
NIST SP 800-63b requires a minimum 10,000 iterations, but we chose an iteration count of 100,000 because the Microsoft SDL minimum is 100,000. The iteration means that an attacker attempting a brute-force attack using the password verifier is slowed down by x100,000. We want to stress that there are no known weaknesses in the current SQL Server authenticator implementation, we are adding this update at the request of finance customers to help achieve their compliance goals.
Specifics
Let's look at some details.
This feature is off by default, and it is only available in SQL Server 2022 CU12 and later. There are no plans to back-port this to older versions of the database engine.
Next, and this is critically important. This changes the on-disk format for the password verifier. This has serious repercussions because we cannot go back to the older version unless you reset users’ passwords or perform a full restore. This update only changes a password verifier to the new algorithm on password change. The database engine cannot update the password verifier automatically because the engine does not know the password.
So how do you enable this if you want to test it out?
First, make sure you’re using a non-production database. The code is production quality, but we want to limit its use until we get more coverage.
Second, make a backup of the database and test that it restores. Remember, there is no going back to the old authenticator other than a password change or a full restore. Reread this point one more time.
Third, using an account that is a sysadmin, you can enable this functionality using the following:
DBCC TRACEON (4671, -1)
DBCC TRACEON (4671)
You can check for this trace flag with:
DBCC TRACESTATUS(4671)
Below is a sample flow testing the functionality.
CREATE LOGIN JimBob WITH PASSWORD = '<insert strong pwd>'
DBCC TRACEON (4671,-1)
DBCC TRACEON (4671)
DBCC TRACESTATUS(4671)
CREATE LOGIN MaryJane WITH PASSWORD = '<insert strong pwd>'
GO
SELECT name, password_hash
FROM sys.sql_logins
WHERE name NOT LIKE '##MS%'
You will see output like this:
sa 0x020097E55B1EC90563A023D6785F4ADC--snip--33A34CB391510CE532B
JimBob 0x0200B378592D3BCFF9B2CD667380D66D--snip--78619048510C10C342E
MaryJane 0x0300D1FB26002DEE6615D02BD9F9F425--snip--0A9F559F3D16EF61A84
Notice the first byte of the hash for JimBob and sa is 0x02, this is the algorithm version number. If you change the password for a login after enabling the new algorithm, the version number changes to 0x03 and this is what we see for MaryJane. The use of a version number at the start of the hash output is a fantastic example of cryptographic agility. Always Encrypted in SQL Server and Azure SQL DB also supports cryptographic agility, as does Microsoft Office. Crypto-agility is a security best practice that allows applications to adopt new cryptographic algorithms over time without breaking existing applications. You can learn more about the topic here. This is especially important as we face a world of post-quantum cryptography.
The data size on disk is the same for v2 and v3 password verifiers because the salt and resulting hash are the same size.
Now, if we update JimBob’s password:
ALTER LOGIN JimBob WITH PASSWORD = '<insert strong pwd>'
We will see his password authenticator is something like this:
JimBob 0x0300A269192D2BCEF9A20016729FDEAD--snip--0AFFF124197B1065718
The new v3 algorithm is now applied to his account.
If you want to go back to using the old v2 algorithm, then you can turn the flag off:
DBCC TRACEOFF (4671,-1)
DBCC TRACEOFF (4671)
DBCC TRACESTATUS(4671)
However, this does not change any password authenticators. JimBob and MaryJane can still login and the SQL engine will use the v3 algorithm. However, if either of our two users changes their passwords, they will revert to v2 because the feature is turned off.
Miscellany
If you want to see what a password hash looks like, you can use PWDENCRYPT and you can compare a given hash with the hash of an existing password with PWDCOMPARE. The documentation indicates that HASHBYTES should be used rather than PWDENCRYPT, but this is not correct as HASHBYTES is a general-purpose hash function and not password-specific.
Finally, as noted, to change a password either to roll forward to v3 or roll back to v2, you can use ALTER
LOGIN xxxx WITH PASSWORD = 'yyyy', where xxxx is the login name and yyyy is the password, and you can add MUST_CHANGE if needed.
One last and crucial point, if you undo the update then you will have to change all passwords that use the v3 algorithm as the engine before this update cannot deal with the v3 data format.
Summary
Listening to our customers, we are excited to add this new security feature to SQL Server 2022 CU12 and later. If you want to test this, please understand the serious implications of making this change; the updated algorithm changes the on-disk format and there is no going back other than a full database restore or resetting the password for affected logins after turning off the traceflag.
We’d love to hear your feedback.
A big thanks to Steven Gott for all his hard making this happen!
