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:

-- 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

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...

Or, you can use this to quickly find passwords matching their logins:

-- which pwds match login name

SELECT [name] FROM sys.sql_logins WHERE PWDCOMPARE([name], password_hash) = 1

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