Finding weak passwords in SQL Server

This is just a quick security tip regarding weak passwords.  Identifying weak passwords in SQL Server is a good precautionary measure to prevent hacking and to ensure your SQL Servers are secure.  Newer versions of SQL Server are said to enforce standards regarding password complexity and enforcement, but that does not mean you cannot get around them...

Which passwords are weak?  That could be a combination of many things, but I always look for blank passwords, passwords matching their logins, and even just strings like 'password' or '1234'.  In this demo I am using PWDCOMPARE, which is one of MSFT's functions designed to identify weak passwords.

First, we'll create some logins:

USE [master] GO CREATE LOGIN SQLuser1 WITH PASSWORD='', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO CREATE LOGIN SQLuser2 WITH PASSWORD='SQLuser2', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO CREATE LOGIN SQLuser3 WITH PASSWORD='1234', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO CREATE LOGIN SQLuser4 WITH PASSWORD='abc', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO CREATE LOGIN SQLuser5 WITH PASSWORD='password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO

Use a table variable to hold a few of the more common offenders: -- input the different passwords you're watching for DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255)) INSERT INTO @WeakPwdList(WeakPwd) SELECT '' UNION ALL SELECT '1234' UNION ALL SELECT 'abc' UNION ALL SELECT 'password'

Now you can find them using PWDCOMPARE:

-- which pwds match login name SELECT [name] FROM sys.sql_logins WHERE PWDCOMPARE([name], password_hash) = 1

-- what about the others? SELECT t1.name,t1.type_desc,t1.is_policy_checked,t1.is_expiration_checked,REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password] FROM sys.sql_logins t1 INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1 OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1) WHERE t1.is_policy_checked = 0

My results...

It's just a small example, but any strong DBA knows that security starts at the basics, and passwords are the front door. PWDCOMPARE provides an easy, very low-impact method for identifying weak credentials that could become easy entry points for the uninvited.  I think this should be a part of every DBA's security toolkit.

More details:  PWDCOMPARE (Transact-SQL) - SQL Server | Microsoft Learn