Detect Weak Passwords in SQL Server

Today I'll be providing you with a T-SQL script that 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

Why are there SQL logins with weak passwords?

Well, the short answer is: People are dumb and lazy, and usually don't consider the consequences of their impact on data security.

Oftentimes, many system administrators would rely on the supposed fact that their "internal network is protected from outside access", and consider that as "good enough" protection without considering the possibility that, once this first-level protection is breached anyway (and it CAN be breached despite all best intentions), then there should be additional barriers to make it as difficult as possible for any malicious actors to cause further damage.

Here's a Script

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

• Check_Passwords_SQL_Server_Generated.sql

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 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.

What should I do about it?

To change the password of a SQL login, you can use the ALTER LOGIN command. For example:

ALTER LOGIN [Mary5] WITH PASSWORD = '<enterStrongPasswordHere>';

IMPORTANT:

The ability to easily remember one's password should never be a consideration, especially nowadays when any person can use a free password management tool such as KeePass, RoboForm, BitWarden, and others to manage all of their strong passwords, without having to remember any of them.

In the long term, the best recommendation is to enable a strong password policy for all SQL logins, to reduce the likelihood of any weak passwords being created in the future.

To retrieve the list of enabled SQL logins without a strong password policy enabled, you may use the query below:

SELECT [name], RemediationCmd = N'ALTER LOGIN ' + QUOTENAME([name]) + ' WITH CHECK_POLICY = ON;'
FROM sys.sql_logins AS s
WHERE s.is_policy_checked = 0
AND s.is_disabled = 0
AND s.[name] NOT LIKE N'##MS[_]%##'

Use the "ALTER LOGIN" command with the "CHECK_POLICY = ON" option to enable the strong password policy for these logins. For example:

ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON

Once enabled, SQL Server will apply the same complexity and expiration policies used in the Windows security policy settings of the machine hosting the SQL Server service.

This policy can control factors such as password history, maximum password age, complexity requirements, and more.

These settings can be changed on the local Windows machine, or at the Domain controller.

This doesn't necessarily guarantee that common passwords wouldn't be used (for example, "P@ssw0rd" would normally pass the policy check even though it's common), but at least it reduces the likelihood of even weaker passwords.

See the additional resources below for more details.

Additional Resources

• Concepts > Protecting SQL Server Intellectual Property > Password policy | Microsoft Docs

• Concepts > Protecting SQL Server Intellectual Property > Strong passwords | Microsoft Docs

• Windows > Security > Threat protection > Password Policy | Microsoft Docs

• PWDCOMPARE (Transact-SQL) | Microsoft Docs

Is Database Security important for your organization?

If it is (and it should be), then you should consider running periodical checks for weak passwords of SQL logins using the script I provided above or a variation of it.

DBSmart - The Managed DBA Service from Madeira Data Solutions, includes automated periodical checks such as this one, among many others. Click here for more info if you're interested.

Also, for more Security-related Best Practices for SQL Server, check out the article: How to Protect SQL Server from Hackers and Penetration Tests.