top of page

Detect Weak Passwords in SQL Server

Updated: Dec 25, 2022

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:


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:

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

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.

4 comments

4件のコメント


Barak G.
Barak G.
2022年1月03日

PWDENCRYPT did work for me on the 3 simplified passwords - and any with symbols are not included here.

YET;

What you write about it is interesting.

If I'll be in charge again of a large environment (many continously changing logins) / numerous amount of servers, I will probably export the hash itself from this script into a list. Will bring the fastest solution using a straight-forward comparison.

いいね!
Eitan Blumin
Eitan Blumin
2022年1月03日
返信先

Barak, I thought of this idea myself while developing this script.


I can tell you from experience that it was proven to be not reliable. The password hashes are non-deterministic and therefore end up being different even though the actual passwords are the same. Therefore, it's not reliable to directly compare hashes to hashes.


However, it is 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 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…

いいね!

Barak G.
Barak G.
2022年1月03日

Great script ‼ Love it for any toolbox.


One objection:

I ran it on my laptop that has 4-5 logins, and it took some 4 secs. I had some servers with over 200 logins, and although it's not a performace script, it's very simple to avoid.

You generate the hash of each password for every login. This can be dropped to one pass if we use the deprectade PWDENCRYPT instead. Runtime dropped to 470 ms and should keep steady for any amout of logins.

Left the original code for the ocasion where this deprecated function is actually removed:


SELECT DISTINCT 'Weak Password' AS Deviation, s.sid, s.principal_id, RTRIM(s.name) AS [LoginName], d.generatedPwd

--FROM #pwd d

--INNER JOIN sys.sql_logins s ON PWDCOMPARE(d.generatedPwd, s.[password_hash]) =…


いいね!
Eitan Blumin
Eitan Blumin
2022年1月03日
返信先

Hi Barak,

I understand your concerns.


However, PWDENCRYPT is a non-deterministic function and is, therefore, an unreliable method of testing the password hashes.


I'm sorry, but it wouldn't actually be able to detect weak passwords this way.


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


In order to reduce the performance overhead on production environments, you should:

  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.

いいね!

STAY IN TOUCH

Get New posts delivered straight to your inbox

Thank you for subscribing!

bottom of page