Leveraging Storage Spaces Direct for SQL Server High Availability
Thursday, July 19, 2018 - click here to learn more
I always wanted to find a way to track all SQL Server logins which have either the same password or a blank password. It's a precautionary measure to avoid any hacking as well as ensuring our SQL Server environment is secure. During one of my assignments, I found a Microsoft link to this function PWDCOMPARE, which can be used to find logins that have blank passwords, common passwords or the same password as the login name. Here I will explain how to use this function to find these password problems.
Microsoft always suggests you use complex and strong passwords. Weak or blank passwords are an invitation for security breaches. We can use the PWDCOMPARE security function to find these types of logins and reset them with strong and complex passwords to avoid any type of security breach.
As per books online, PWDCOMPARE is the security function which basically hashes the password and compares the to hash to the existing password in the sys.syslogins table which is in the master database.
We will use this function to search for blank and weak passwords. We will pass two parameters 'text_password' and password_hash to run this function. It will return a "1" if the hash of the 'text_password' matches the password_hash parameter, and "0" if it does not match. We will put this function in a WHERE clause while accessing the logins from sys.sql_logins system catalog view.
Here is the process to find all such weak passwords.
First we will create some logins with the same password, blank passwords and common passwords. Common means the same password is used for multiple logins. Here you can see wee created two logins, One is "mssqltips" with the same password as the login name and another one is "mssqltips_1" with a blank password. Also I created a user for each respective login and assigned them db_owner access on a database.
--Creating a Login which has same password as its login name. Then creating a user with same name --and assigned it db_owner access. CREATE LOGIN mssqltips WITH PASSWORD='mssqltips' , CHECK_POLICY=OFF go use KPO_Master go CREATE USER mssqltips for login mssqltips go sp_addrolemember'db_owner',mssqltips go --Creating a Login without password. Then creating a user with same name and assigned it db_owner access. CREATE LOGIN mssqltips_1 WITH PASSWORD='' , CHECK_POLICY=OFF go use KPO_Master go CREATE USER mssqltips_1 for login mssqltips_1 go sp_addrolemember'db_owner',mssqltips_1 go
Now to test for common passwords, I will create another login, "mssqltips_2" with the same password as the "mssqltips" login.
--Creating a Login with common password. Then creating a user with same name and assigned it --db_owner access. CREATE LOGIN mssqltips_2 WITH PASSWORD='mssqltips' , CHECK_POLICY=OFF go use KPO_Master go CREATE USER mssqltips_2 for login mssqltips_2 go sp_addrolemember'db_owner',mssqltips_2 go
Now we can search for weak passwords on our SQL Server Instance.
Finding Common Passwords
Here we will get all logins which have common passwords. As the password is "mssqltips", we will pass this password into the PWDCOMPARE function to get all those logins. In the below screenshot, it's showing the two logins, mssqltips and mssqltips_2, have the same password. In this case, mssqltips is the password on this SQL Server Instance.
SELECT name,type_desc,create_date,modify_date,password_hash FROM sys.sql_logins WHERE PWDCOMPARE('mssqltips',password_hash)=1;
Finding Blank Passwords
Next we will find all logins that have blank passwords. As we are searching those logins, we will not pass any password into the security function PWDCOMPARE. Here you can see there is two logins which have a blank password which includes the one we created. This should be address immediately to make your instance more secure.
SELECT name,type_desc,create_date,modify_date,password_hash FROM sys.sql_logins WHERE PWDCOMPARE('',password_hash)=1;
Finding Passwords that are the same as the Login name
Now to find all logins that have the same password as their login name. This is a very common scenario where people just create the login with the same password as the login name. Here we will pass the login name as the password text.
SELECT name,type_desc,create_date,modify_date,password_hash FROM sys.sql_logins WHERE PWDCOMPARE(name,password_hash)=1;
We can see there are four logins where the password matches the login name.
Now that you have identified logins that have either blank passwords or weak passwords, you can reset them with strong and complex passwords to make your environment more secure.
- Read more articles on SQL Server Security
- SQL Server Login Properties to Enforce Password Policies and Expiration
- How to configure password enforcement options for standard SQL Server logins
Last Update: 2012-09-26
About the author
View all my tips