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:
- empty
- identical to login name
- sorted list of numbers with or without a skip character (for example: “102030” is a sorted numbers list “123” with a skip character “0”)
- reversed sorted list of numbers with or without a skip character
- replicated text (for example: “1111”, “blablabla”, “qweqwe”, etc.)
- keyboard combinations (for example: “1qazxsw2”, “qwertyuiop”, “741258”, etc.)
- permutation of one or more common words and keyboard combinations (for example: “asd123”, “qwe456”, “147258”, etc.)
- common words used as passwords – based on publicly available weak password lists (for example: “password”, “dragon”, “monkey”, etc.)
The list of commonly used weak passwords is based on the following resources:
- https://nordpass.com/most-common-passwords-list/
- https://github.com/danielmiessler/SecLists/tree/master/Passwords
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:
- @IncludeModifiers – when this is set to “1“, additional permutations will be added to the passwords list, replacing ‘a’ with ‘@’, ‘3’ with ‘#’, ‘s’ with ‘$’, ‘o’ with ‘*’, etc…
- @BringThePain – set this to “1” in order to add 3-part password permutations. For example, not just “asd” + “1234” = “asd1234”, but also “asd” + “1234” + “qwerty” = “asd1234qwerty”. Enabling this mode on weak servers can potentially have a performance impact.
- @OutputPassword – set this to “1” in order to also see the passwords themselves that were successfully compromised.
- @GenerateOnly – set this to “1” in order to only calculate and generate the passwords, without actually comparing them against the server logins. This can be useful for debugging and benchmarking.
The script will output for each compromised login:
- the reason for the compromise (password identical to login name / empty password / weak password).
- the server roles that the login is a member of.
- which databases the login has access to and any database role memberships that they may have.
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 Enabled | Bring The Pain Enabled | # of Distinct Passwords | Runtime on an 8-core machine |
|---|---|---|---|
| ❌ | ❌ | ~21K | < 1 second |
| ✅ | ❌ | ~61K | < 1 second |
| ❌ | ✅ | ~1.3M | 3 seconds |
| ✅ | ✅ | ~4.5M | 30 seconds |
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:
- Have a machine in your production that’s more powerful than a common personal laptop 😉
- Run this test not too often and during low-traffic hours. Maybe once every few weeks or so.
- 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: