Aaron Bertrand (twitter|blog) recently blogged asking for help to fix a big security loophole. I read the post then, but it was only this morning that I finally got around to REALLY looking at it, thinking about it, and testing it out for myself.
Aaron can do a much better job at describing the issue than I can, so I urge you to read his blog post on the topic, or his Stack Exchange answer on the topic. Then, go up-vote the Connect item he filed.
I’ll do my best to give a quick summary of the issue here, if you don’t want to read Aaron’s full version:
Microsoft’s documentation explains that sys.logins.is_policy_checked means “Password policy is checked.” Bill Clinton was right, “It depends upon what the meaning of the word ‘is’ is.”
This use of “is” in this terse definition could have two meanings:
- As a descriptor of what has happened: “The password policy has been checked for this login.”
- As an instruction of what SQL Server should do in the future: “The password policy will be checked for this login.”
(If you aren’t already hung-up thinking about Bill Clinton, I probably just gave you a flashback to a grammar school teacher grilling you on conjugations of the verb “to be.”)
As it turns out, the correct meaning of this column is actually #2: “The password policy will be checked for this login.” Most people (myself included) wrongly expect this column to have the other meaning.
Further, there is no DMV that describes #1: “The password policy has been checked for this login.” If an auditor asks you to prove that all SQL logins meet your password policy, there is no way to prove that.
Yes, I can use processes to manage my own environment to a point where I am confident that the passwords are strong. However, if I were an auditor, I would demand proof the DBA wasn’t skirting the rules by altering the flag before an audit (and flipping it back later).
So, I join in Aaron’s call-to-action. Go up-vote the Connect item and get this security loophole fixed.