I've recently come into an environment where a lot of databases logins do not have the enforce_password_policy
flag enabled.
An upcoming audit is necessitating the verification of these logins' passwords.
I used the following query to obtain a list of logins and whether the flags are on or off.
select
@@SERVERNAME as servername,
name,
IS_SRVROLEMEMBER('sysadmin', name) as SYSADMIN,
type_desc,
create_date,
is_policy_checked,
is_disabled,
password_hash,
PWDCOMPARE(name, password_hash) as UsernameAsPassword
FROM sys.sql_logins
However, this doesn't tell me if the passwords actually adhere to the password policy, as the flag is only relevant when creating a user.
Is there a known way to test existing users for password policy compliance?
I have no access to the old passwords, and I would prefer a method that doesn't require them.
Best Answer
This may not be popular among your users, but I believe the only way you can know for sure is to force a password change for every SQL login with
CHECK_POLICY = ON
. This will generate a set ofALTER LOGIN
commands with blank passwords, you can update the query giving them all a common password or manually update each one with individual passwords - just make sure they meet your policy. Of course you need to be sure that the password policy is as complex as you expect, and that it is enabled (Control Panel > Administrative Tools > Local Security Policy > Account Policies > Password Policy > Password must meet complexity requirements).Steve Jones wrote about this a while back. Note that - due to what I uncovered below - you can't rely on
is_policy_checked = 1
to mean that the password actually meets your current policy, since the login could have been created with a hashed password (in which case the plain text password can't be checked) or while the local complexity policy was disabled (which still leads tois_policy_checked = 1
).Another approach I thought would work would be to try to create a copy of every login with their current
password_hash
and withCHECK_POLICY = ON
, and make a note of every one that fails. However, this can not work - even withCHECK_POLICY = ON
, it doesn't perform any validation of an already-hashed password. I'll include the code for posterity - but, by design, the policy simply can't be checked.Personally, I think this is a bug. If the syntax allows me to create a login using a hashed password, and I can stipulate that that password must meet my complexity policy, it should generate an error or warning that the policy was not, in fact, checked.
UPDATE: I filed a bug against this behavior.