In a previous post we’ve looked at offline methods of cracking SQL Server login passwords as a means of auditing the password strength. But what if, due to security concerns or other limitations, you can’t take the hashes off of a SQL Server instance? This is where online cracking comes in.

About online password cracking

Online password cracking is fairly similar to the previously discussed process, but instead of taking the hashes to another system and do the cracking there, this process uses the original system itself for testing password candidates until a matching one is found.

This has the advantage of not needing to copy login names and password hashes to another system, but it’s limited by the instance’s resources and does not benefit from the added performance that specialized tools like JTR and hashcat bring to the table.
So this is a good fit for smaller password lists – between 200k and 2 million password candidates.

How SQL Server checks passwords

In SQL Server passwords can be checked against existing hashes by using the PWDCOMPARE function.

Building on the T-SQL from the previous post, I’ll add a column that uses PWDCOMPARE to validate the clear text password against the generated hash.

Validating the password against the generated hash using PWDCOMPARE

PWDCOMPARE can also be used to compare a password against the password_hash column of the sys.sql_logins system catalog view.

Here’s a quick demo:

Generating a large number of password candidates

I have written this script for generating and testing password candidates based on:

  • the comma-separated terms provided for the @BaseWordsList parameter
  • instance information (database names, instance name, login names) if @UseInstInfo is set to 1
  • 17 common words (season names, words and keyboard walks) – hard-coded in the script

These are combined with symbols and years (a 12 year range generated automatically based on the current date), and case variations are also applied.
This generates password candidates like “Contoso2017!”, “contoso22”, “<<Contoso>>”, “Fall2022##”, etc.

Note that the number of terms provided via @BaseWordsList, and, if @UseInstInfo = 1, the number of databases and SQL logins on the instance, directly impacts the number of generated password candidates.
As a quick example, running the script with @BaseWordsList = N'Contoso, administrator, fakeproject, VladDBA' and @UseInstInfo = 0 generates 195615 password candidates.

Granted the resulting list of candidates is not an exhaustive one, but it’s based on patterns I have seen being used by users, developers and vendors alike.

Cracking SQL login passwords

The second part of the script uses PWDCOMPARE and checks the generated passwords against the hashes found in sys.sql_logins’ password_hash column. It also checks if identified logins are members of dangerous fixed server roles.
For reference, this is how that part of the script looks:

For testing purposes, I’ve created the following logins:

And then I’m executing the script with only two custom terms – contoso and fakeproject.

Screenshot from SSMS of QuickSQLPassAudit.sql cracking weak sql login passwords
Identifying weak passwords with the QuickSQLPassAudit.sql script

Note that the QuickSQLPassAudit.sql script relies on the STRING_SPLIT function, so it only works on SQL Server 2016 and newer.


If you are constrained by either your environment or internal policies, yet still want to conduct an audit to identify weak passwords and update them, QuickSQLPassAudit.sql can help identify some potentially vulnerable passwords using the SQL Server instance itself to do all the work.