When ‘Enforce Password Policy’ for SQL Server logins is turned off, you can make your password anything you want! Hooray! No picky criteria and trying to remember yet another convoluted password!
Not the best idea, especially if you need to have a more secured server.
Recently I was performing a security audit for a client. One of the many things I had to check was the enforcement of password policies for any SQL Server created accounts.
You know, that policy that says you must have some combination of 6 or more characters, upper and lower case, a number, and special characters, etc.
These policies are controlled by the server policy settings and were something easy to check. The actual passwords and that they were safe, not so much.
One of the scripts I use during these audits is this one:
select
@@SERVERNAME as servername,
name,
create_date,
is_policy_checked,
is_disabled,
PWDCOMPARE(name, password_hash) as UsernameAsPassword
FROM sys.sql_logins
WHERE is_policy_checked = 0
ORDER BY name
Quick results
This script produces a simple results set that tells me a lot of good information I can quickly evaluate.
- The server the script was run on (great if running against multiple servers)
- Login name
- Create date for the login
- If the ‘enforce password policy’ checkbox is marked
- If the login is disabled
- If the username was used as the password
How much does this matter?
Ready for it?
Wait for it…
It depends!
Yes, you may be required to adhere to certain regulations and guidelines that mean anything that turns up here with the policy not checked is an issue. In general using the login name as the password is frowned upon, by just about everyone I know.
Anyway you look at it, knowing what is going on in your system is huge when trying to protect it.
I hope you find this little script a helpful tool in learning more about what is potentially going on in your SQL Server environment.