Auditing SQL Server Password Strength using PWDCOMPARE

Introduction

As a DBA, one of your responsibilities is to ensure that every password on the system is strong. We know already that SQL Server stores the passwords of SQL logins as salted hash values. That however means that we cannot just go through the passwords and look at them to see if they follow best practices. What we can do however, is to try a simple brute-force attack against the passwords. That is exactly what a hacker would do, so it makes sense to regularly test the current system strength against that attack.

Password Policies

SQL Server allows us to "enforce" that all passwords follow the Windows password complexity requirements. However, it is easy for elevated users to circumvent this requirement. What is worse, looking at the information that the catalog views provide, you might get the impression that the password for a given SQL login follows those requirements while it actually is not.

The only way to make sure that all passwords in SQL Server are strong is to try to attack them regularly.

The PWDCOMPARE Function

Instead of having to write a (power-)shell script that tries one password after the other, SQL Server is nice enough to give us access to a built-in function that can compare a clear-text password to a hash value. This function is the PWDCOMPARE function. To use it you can just pass in the password you would like to compare too followed by the password hash. Remember, to get to the password hash value, you can for example use the sys.sql_logins catalog view:

Now we can just pass that value directly to the PWDCOMPARE function as second parameter:

And if the guessed password matches the actual one, you will get this output:

Brute Force Cracking

A Brute Force attack generally involves testing all possible combinations. However, in our case we are not actually interested in the password. We just want to know if the password withstands a reasonable attack. Given enough resources and time, you can crack any password. The hope is that if you make your passwords strong enough, that the opponent will give up and move on to an easier target. For that reason, I recommend using a word list instead.

Word List Cracking

The main difference between Brute-Force cracking and Word List cracking is that a word list greatly reduces the number of tries, and therefore required resources, by skipping unlikely (read: strong) passwords. Word lists can be downloaded from the internet. Before you use a wordlist, you probably want to apply common alterations to it, like replacing characters with common substitutes, e.g. "@" for "a", and concatenate up to three or four of the words together.

Once you have a wordlist that you like, store it in a table. Then you can just use a simple join to test all combinations:

If a weak password is found, it will be part of the result set:

Other Use-Cases

There are rumors that PWDCOMPARE is an undocumented function and should not be used. That information is wrong as this PWDCOMPARE entry in BOL clearly demonstrates: dd822792.

SQL Server even provides a PWDENCRYPT function that can be used to calculate a hash value from a given clear-text password. That means, you could take those two functions and use them to store your own application passwords in a secure way. However, BOL discourages from that. While PWDENCRYPT is not officially deprecated, its BOL entry says that it will be soon: " PWDENCRYPT is an older function and might not be supported in a future release of SQL Server."

Summary

SQL Server stores SQL login passwords as salted hash values. Therefore, we cannot directly check if they follow password strength requirements. However, we can use the PWDCOMPARE function to try to crack them. I encourage you to try this regularly, as only that way security holes base on weak passwords will be discovered.