Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to identify fragile passwords, empty or equal to username

Views: 2.176 views
Reading Time: 4 minutes

Hello people,
Good night!

In this post, I will demonstrate how to identify fragile passwords, empty or equal to the username in SQL Server. This is especially useful for database administrators to avoid attacks due to carelessness of users in choosing their passwords.


To perform this check, we will use the PWDCOMPARE function, which has been present in SQL Server since 2008, but which is unfortunately marked deprecated and will be discontinued in some future version of SQL Server (although I have not identified any other alternative). This function is quite simple, where you enter the password you would like to test, the comparison hash, and the function returns a boolean value (0 or 1) if the password you tried matches the hash you entered.

Although this function is public, that is, any user of the instance can use it, it does not pose a threat to the security of the database, since the password_hash column of the sys.sql_logins view is only visible to users with CONTROL SERVER privilege in the instance, which I imagine very few users, all of them, DBA's. Otherwise, it would be very easy for a malicious user to perform brute force attacks to try to guess passwords, but this is not possible due to this permission restriction (although this is possible for a DBA user).

Another important point to make is that this technique only applies to users with SQL Server authentication. Active Directory Authentication (Windows AD) users have the password_hash column value NULL, even for sysadmin users.

Identifying the user password hash (password_hash)

An important step in identifying these fragile passwords is to figure out the hash of the user's password. We will need this hash to use in the PWDCOMPARE function and identify the user's current password.

To retrieve this user hash is quite simple, just use one of the 2 queries below:


Identifying Fragile Passwords

Now that we've identified how to recover password_hash, let's look at how fragile our users' passwords are in the bank. For this, I will create a table of weak passwords that I will try, and then I will test each password on each user to find which password has matched.

Password Table Creation Script:
With the script below, I will create a table with the passwords that I will use to try to identify the user's current password. Feel free to change this script and add your password attempts.

In this script you can easily create a list of all possibilities and make a brute force attack if you are a user with DBA privilege, have lost a user's password and really need to find out a certain password (because it's much easier simply change it, if applicable).

And now, we try to identify the fragile passwords in the instance:


In the report above, all users where the password could be identified are shown, with their respective passwords found.

Ideally, in the next password settings, you activate the option “Enforce password policy” ( to ensure your passwords are strong and secure.


I hope you enjoyed the post and even more.