Site icon Eitan Blumin's blog

Detect Weak Passwords in SQL Server

This is a T-SQL script that I’m cross-publishing with the official Madeira Data Solutions blog.

This script generates various permutations and variations of common and weak passwords and uses the PWDCOMPARE function to check whether there are any enabled SQL logins that have these passwords.

The types of weak passwords being checked are:

The list of commonly used weak passwords is based on the following resources:

Here’s the script

To retrieve the full list of SQL logins with weak passwords, run this script from our Madeira Toolbox available on GitHub:

Script parameters:

The script will output for each compromised login:

Use the information from this script to assess the scope of the danger, and communicate it to the relevant system administrator.

Time and count estimations on a machine with 8 cores

Modifiers EnabledBring The Pain Enabled# of Distinct PasswordsRuntime on an 8-core machine
~21K< 1 second
~61K< 1 second
~1.3M3 seconds
~4.5M30 seconds
The execution times above are only for the generation of the passwords themselves, not including the checking of server logins

The total execution time may vary based on the number of logins to check.

Problematic Script Performance?

PWDCOMPARE is a very inefficient method of testing passwords, but unfortunately, it’s the ONLY reliable method in SQL Server.

One could suggest first encrypting all the generated passwords using the encryption function PWDENCRYPT, and then very efficiently comparing password hashes to password hashes.

However, the function PWDENCRYPT is non-deterministic which means that the password hashes may end up being different on every use, even though the actual passwords are the same. It’s one-way encryption (except, maybe, in older SQL Server versions that used older encryption algorithms). Therefore, it’s not reliable to directly compare hashes to hashes.

Using PWDCOMPARE is the only reliable method of comparing password hashes.

Here are some suggestions to reduce the performance overhead on production environments:

  1. Have a machine in your production that’s more powerful than a common personal laptop 😉
  2. Run this test not too often and during low-traffic hours. Maybe once every few weeks or so.
  3. Export the password hashes from sys.sql_logins to someplace else and use PWDCOMPARE to test them off-site, to avoid affecting the production server.

It is indeed possible to export the password hashes from sys.sql_logins and then test them off-site using PWDCOMPARE. So, that could be a sort of “middle ground” where you won’t directly affect your production environment.

NOTE: SQL Server improves its password hashing mechanism with every new version. So, the “off-site” server where you’d be testing the password hashes must be of equal or newer SQL Server version than your production server.

More Info

Why would there be weak passwords in the first place? What to do once you detect logins with weak passwords? For all this and more, please check out the full blog post at the official Madeira Data Solutions site:

Detect Weak Passwords in SQL Server

Exit mobile version