One of the key aspects of maintaining a secure SQL Server environment is ensuring that all logins have strong and complex passwords. Weak or blank passwords can leave your system vulnerable to hacking and security breaches. In this article, we will explore how to use the PWDCOMPARE function in SQL Server to identify and secure weak passwords.

Step 1: Creating Logins with Weak Passwords

Before we can identify weak passwords, let’s create some logins with different types of weak passwords. We will create logins with the same password as the login name, blank passwords, and common passwords.

CREATE LOGIN mssqltips WITH PASSWORD='mssqltips', CHECK_POLICY=OFF;
GO
USE KPO_Master;
GO
CREATE USER mssqltips FOR LOGIN mssqltips;
GO
EXEC sp_addrolemember 'db_owner', 'mssqltips';
GO

CREATE LOGIN mssqltips_1 WITH PASSWORD='', CHECK_POLICY=OFF;
GO
USE KPO_Master;
GO
CREATE USER mssqltips_1 FOR LOGIN mssqltips_1;
GO
EXEC sp_addrolemember 'db_owner', 'mssqltips_1';
GO

CREATE LOGIN mssqltips_2 WITH PASSWORD='mssqltips', CHECK_POLICY=OFF;
GO
USE KPO_Master;
GO
CREATE USER mssqltips_2 FOR LOGIN mssqltips_2;
GO
EXEC sp_addrolemember 'db_owner', 'mssqltips_2';
GO

Step 2: Identifying Common Passwords

Now that we have created logins with different weak passwords, let’s use the PWDCOMPARE function to identify common passwords. Common passwords are those that are used for multiple logins.

SELECT name, type_desc, create_date, modify_date, password_hash 
FROM sys.sql_logins 
WHERE PWDCOMPARE('mssqltips', password_hash) = 1;

The above query will return all logins that have the password ‘mssqltips’. In our case, it will return the logins ‘mssqltips’ and ‘mssqltips_2’.

Step 3: Finding Blank Passwords

Next, let’s identify logins that have blank passwords. We can do this by not passing any password into the PWDCOMPARE function.

SELECT name, type_desc, create_date, modify_date, password_hash 
FROM sys.sql_logins 
WHERE PWDCOMPARE('', password_hash) = 1;

The above query will return all logins that have a blank password. In our case, it will return the logins ‘mssqltips_1’ and any other logins with blank passwords.

Step 4: Identifying Passwords that Match the Login Name

Lastly, let’s find logins that have the same password as their login name. This is a common scenario where people create logins with the same password as the login name.

SELECT name, type_desc, create_date, modify_date, password_hash 
FROM sys.sql_logins 
WHERE PWDCOMPARE(name, password_hash) = 1;

The above query will return all logins where the password matches the login name.

Step 5: Securing Weak Passwords

Now that we have identified logins with weak passwords, it is important to reset them with strong and complex passwords. This will help ensure the security of your SQL Server environment.

By following these steps, you can easily identify and secure weak passwords in your SQL Server environment, making it more resistant to hacking and security breaches.