Use PWDCOMPARE() to Find SQL Logins With Weak Passwords

Not a day, week, or month goes by without news of yet another data breach.

And the breaches aren’t the result of some type of Mission Impossible heist. No, it’s often an unprotected S3 bucket, maybe some SQL Injection, or files left behind when relocating to a new office. Silly, fundamental mistakes made by people that should know better.

After decades of reviewing data breaches I have arrived at the following conclusion:

Data security is hard because people are dumb.

Don’t just take my word for it though. Do a quick search for “common password list” and you’ll see examples of passwords scraped from breaches. These are passwords often used by default to secure systems and data.

Chances are, these passwords are in your environment, right now.

Here’s what you can do to protect your data.

 

Use PWDCOMPARE() to Find SQL Logins With Weak Passwords

SQL Server ships with an internal system function, PWDCOMPARE(), that we can use to find SQL logins with weak passwords. We can combine this function, along with a list of weak passwords, and some PowerShell to do a quick check.

First, let’s build a list. I’ll store mine as a text file and it looks like this:

 

 

I can import that file as an array into PowerShell with one line of code:

$pwdList = Get-Content .\password_list.txt

And with just a few lines of code, we can build a query and execute against our instance of SQL Server:

foreach ($password in $pwdList) {
$SQLText = "SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('$password', password_hash) = 1;"
Invoke-Sqlcmd -Query $SQLText -ServerInstance $SQLServer
}

And we find that the ITSupport login has a weak password:

 

 

As Dark Helmet once said, “Now you see that evil will always triumph, because good is dumb.”

 

Preventing Weak Passwords for SQL Logins

One of the easiest things you can do is to enable the CHECK_POLICY for SQL logins. By default, enabling the CHECK_POLICY option will also force the password expiration by enabling the CHECK_EXPIRATION flag. In other words, you can have passwords for SQL logins expire as if they were windows logins, and you can enforce complex passwords.

However, even with those checks enabled, I would advise you still do a manual check for weak passwords. Do not assume that by enabling the password policy checks that you are secure. In fact, you should do the opposite. You should take a stance of assume compromise. This is a fundamental aspect of modern Cybersecurity practices.

As a side note, I also want to point out that Troy Hunt has collected the passwords from many data breaches, and he has made the passwords searchable. Do yourself a favor and take some of the passwords you’ve used throughout the web and see if they have been exposed at some point.

Summary

SQL Server offers system functions to help you search for weak passwords, as well as policies to enforce complex passwords and password expiration. You should adopt a stance of “assume compromise” and be proactive about checking the passwords in your environment to make certain they are not considered weak.

[Hey there, dear reader, if you liked this post about passwords and data security, then you might also like the full day training session I am delivering with Karen Lopez in two weeks at SQL Konferenz. The title is Advanced Data Protection: Security and Privacy in SQL Server, and you’ll learn more about how to protect your data at rest, in use, and in motion.]