Does sys.sql_logins.is_policy_checked mean that the policy has been checked?

Question

When I look in sys.sql_logins, I see a column called is_policy_checked. Can I trust that my password policy has been checked for all of the logins where this column value is 1?

Answer 1

##No. While the documentation currently has the following arguably ambiguous statement about what this flag means:

Password policy is checked.

What it really means, and should say, is that the flag serves two purposes:

1. The password policy might have been checked, but only if (a) the password policy was enabled at the time the password was last set, and (b) the password was specified in plain text (not with a hash). 2. The password policy will be checked the next time the policy is set, but only if (a) the password policy is enabled at that time, and (b) the password is specified in plain text (not with a hash).

(And note that "the policy" also refers to enforcing expiration and the fact that the user must change the password on next login, but since the complexity is typically the focus of auditing operations, I'm going to focus only on that aspect.)

The is_policy_checked bit is set to 1 if CHECK_POLICY = ON during a CREATE LOGIN or ALTER LOGIN event, even if the policy isn't checked at the time. As you can probably gather from above, this check does not happen in these scenarios:

• The password is specified using the HASHED keyword (a very common tactic when migrating logins between servers or copying logins to log shipped / mirrored / AG secondaries). It is obviously not possible to check the complexity of the password if you don't have the pre-hashed value.
• The local password complexity policy is not enabled at the time the event occurs.
• Not covered in my proposed rewording above, but you can ALTER LOGIN without setting a new password, and still change the flag (thanks to @AMtwo for illustrating this). I suspect this may have been done by clever people trying to fool an auditor.

These problems are all easy to demonstrate.

Since most people I've talked to about this have always assumed that is_policy_checked actually means that the current password meets the current password policy, I think it's important that something changes here so that users have the right expectations and understand that this flag does not necessarily mean all is well. At the very least, the documentation should be updated to reflect reality, somewhat like I have pointed out above. But there are other things that can be done, too.

• A warning could be raised if CHECK_POLICY = ON is specified but the policy can't, in fact, be checked (either because the password is specified with a hash, or because the password policy has been disabled, or because the command is a simple attempt to bypass or set the flag, e.g. ALTER LOGIN blat WITH CHECK_POLICY = ON;).
• CHECK_POLICY could be deprecated, in favor of ACTIVELY_CHECK_POLICY and perhaps CHECK_POLICY_ON_NEXT_CHANGE. The columns in sys.sql_logins should be policy_has_been_checked and policy_will_be_checked. I am not married to these names, but they are a lot more accurate than the current wording.
• If I choose ACTIVELY_CHECK_POLICY = ON and the policy can't be checked during the execution of the command, I should receive an error message and the flag should not be set to 1 (or even the login creation or password change should not succeed).
• I don't think it makes sense in this case to continue with the current behavior, where I can specify that I want the policy to be checked, but even if it can't, the password is allowed and the login is created/altered (this is bad, IMHO, regardless of the state of the flag after the fact - but at least if it were set to 0, such bypasses could be identified).

Today there is no reliable way - without manually changing their passwords to something you know is secure - to audit your SQL logins and be confident that they all meet your complexity policy. In this day and age of ever-increasing data, more and more data breaches, and the obvious need to secure systems tighter and tighter, this is a problem that needs to be addressed. I've blogged about this and created a Connect item about it:

• Please help me fix a big security loophole
• CHECK_POLICY is a no-op with CREATE LOGIN ... PASSWORD ... HASHED

I encourage you to vote on the Connect item and, more importantly, be sure you're not auditing your systems with false perceptions about how this DDL option and metadata work.

Please don't brush this aside as a "non-issue" because you are perfectly comfortable with how it works and already know that the flag can't be trusted - you are not the user I'm worried about; it's everyone else.