Security of PWDCOMPARE and SQL Hashing

Few people are as concerned about security as they should be, and fewer still are proficient in it. Even if you are, while you might be able to tackle the major security flaws, there’s always going to be something new that can get you, unless you implement an unhackable system – turned off and unplugged.

In SQL 2008, Microsoft added an interesting new function PWDCOMPARE. The idea was to provide DBAs with the ability to check if existing passwords were too simple, then require them to be changed. I was immediately concerned after reading about this. Researching the topic for this post actually alleviated most of my concerns, but there are a few things to know.

Function Explanation
PWDCOMPARE works by comparing a provided plain-text password against a password hash, both of which are provided as parameters to the function. If the result returns a value of 1, the password matches, while a value of 0 means the password is incorrect. You have to type the password exactly to get a match, that doesn’t seem so dangerous, right?

,[Pwd] = 'App!3'
,[IsMatch] = PWDCOMPARE('App!3',password_hash)
FROM sys.sql_logins

All users have access to query sys.sql_logins. Also, by default, they can see their own login as well as the SA account. We all (should) know that best practice is to disable and rename the SA account. (Just knowing it’s the right thing to do isn’t enough though, you should also be doing it!) Renaming the account doesn’t help here; users can still see the account with principal_id = 1, regardless of what it’s called and whether or not it’s disabled.

Now let me walk you through my original concern.

Curious User George logs in and performs a query on sys.sql_logins. He notices that the SA account is visible and someone accidentally left it enabled. Curious User George happens also to be Malicious User George. He sees a password_hash column in his query results. Here’s where SQL redeems some security though. If a user does not have the permission CONTROL SERVER, this field is left NULL. George cannot even see his own password hash.


When George attempts to use PWDCOMPARE, the result is also NULL. Crisis averted, basic users cannot decrypt passwords this way.


However, all those elevated users that should not exist, but do, in small companies? They have the power. They already have all the power, so this happens to be one of the smallest concerns.

MSDN explicitly states that “The PWDCOMPARE function is not a threat against the strength of password hashes because the same test could be performed by trying to log in using the password provided as the first parameter.”

However, if you try to login with a bad password, you should get an alert. I’m assuming you are auditing failed logins? If you try to do it repeatedly, you’ll be locked out of the account. Again, assuming routine security practices are in effect. If you compare a billion passwords using PWDCOMPARE, no alerts are fired, no one gets locked out. Even if it takes forever. How is that the same thing?

I created a temp table in six seconds that contained a million randomly generated passwords. It took 12-15 minutes to compare seven million rows and return the one password that matched. This really isn’t a viable method due to the time it takes for anything other than comparing some common passwords. But then again, all it took was a process running for a fraction of my day on a small development box. A targeted list will have significantly better results than using random GUIDs like in my below example.

Create a temp table with a million random passwords (no not reasonable passwords, just passwords)

FROM sys.all_columns a
CROSS JOIN sys.all_columns b
SELECT TOP 1000000
INTO #Password

Find weak passwords; provide script to force users to change
This script will provide you with scripts that will force users with discovered passwords to change their password when next logging in. It’s still up to you to educate them on better password policies though.

	,[IsMatch] = PWDCOMPARE(Pass,password_hash)
	,[ForceChange] = 'ALTER LOGIN ' + QUOTENAME(name) + '
FROM sys.sql_logins
	CROSS JOIN #Password
WHERE PWDCOMPARE(Pass,password_hash)=1

Hashing Concerns
My friend, integgroll, is a Penetration Tester. As suggestive as that title is, he is essentially an Ethical Hacker. When I asked him about cracking passwords, he provided me with a lot of interesting information. There are quality, free programs out there that will force a password in less time and with less effort than using PWDCOMPARE, and without using SQL Server to even test against. Such a test/attack is especially effective if you use a Mask Attack (targeted list) instead of just a simple Brute Force Attack (every possible combination).

SQL Server uses the twelve-year-old encryption method of SHA-512, which is slowly being broken through collision attacks. SQL appends a 32-bit salt to complicate the hashed password. However, my friend pointed out that salting does not mean that passwords are suddenly unable to be cracked, it just takes more time. With a salted hash, he estimated it would only increase the time required to force the password by around 20%. Not too bad when most users follow a password pattern and you can potentially test 414 million combinations a second if you have a quality graphics card to boost Hashcat’s performance.

With sufficient permission, one could viably steal any hashed password this way. Just another reason to stick to Windows Logins. Windows logins do not display at all when using sys.sql_logins. They only return by using sys.syslogins, which still has a password field, but this is left NULL for windows accounts, keeping the hash secure (at least from SQL).

I have yet to rationalize why we need to see a password hash at all. So long as there are password complexity requirements, I’d rather keep the chances for stealing and cracking passwords to a minimum, meaning that I’d rather not be able to see a plain-text or even a hashed password of any user.

EDIT: It was pointed out to me, and then I immediately remembered I’ve done it many times in the past, that password hashes let us script out logins without knowing the original password. I didn’t think about that while posting this, probably because I was so caught up in the disturbingly simple methods of password cracking.

The most important thing you can do here is improve password strength. Decrypting a hashed password is disturbingly easy and quick, but each digit you add to a password exponentially increases the cracking time required.


  • Implement a minimum password
    • Eight digits is too short for a reasonably secure password, more is better
  • Do not have a maximum length (or at least make it huge)
    • If users want a 75 digit password, let them. Their password will be more secure than your admin account.
  • Allow special characters and numbers
    • This substantially increases possibilities and thus increases cracking time, anytime I see policies which still only allow alpha character, I get angry or depressed – depending on how much energy I have.
  • Do not advertise your password policy on failed login or password creation
    • Displaying the policy is convenient for users, but it also lets hackers know how they can structure their Mask Attack
    • Controversial and may not really help vs. a determined Mask Attacker


  • Every digit added to your password makes it exponentially harder to crack
  • Do not follow common patterns!
    • Timothy1950 is a common password pattern. Most passwords only contain a capital letter for the first value. Mix things up, don’t end with a year. Even changing the password to 19Tim50othy makes a Mask Attack a lot more time-consuming.
  • Do not share passwords between accounts
    • If one is cracked, hackers normally test other popular websites
    • Even if you follow a pattern, they now have a very powerful Mask Attack to use against you – changing a number or two is easier than guessing 15 characters.

1 thought on “Security of PWDCOMPARE and SQL Hashing

  1. Pingback: SQL New Blogger Challenge Digest – Week 4 | The Rest is Just Code

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s