SentryOne Team Blog (blogs.sentryone.com)

Please help me fix a big security loophole

Last week, a user on dba.stackexchange.com had a problem – they wanted to check whether their existing passwords met their complexity policies. We were not talking about some RegEx validation they whipped up on their own; just the built-in rule (from your domain or, for local workstations, Control Panel > Administrative Tools > Local Security Policy > Account Policies > Password Policy > Password must meet complexity requirements). The rules for this are irrelevant to this discussion, but for completeness, the defaults are as follows:

  • Not contain the user's account name or parts of the user's full name that exceed two consecutive characters
  • Be at least six characters in length
  • Contain characters from three of the following four categories:
    • English uppercase characters (A through Z)
    • English lowercase characters (a through z)
    • Base 10 digits (0 through 9)
    • Non-alphabetic characters (for example, !, $, #, %)

Anyway, back to the user's problem. My initial thought was that you could simply script out any logins from sys.sql_logins where is_policy_checked = 0, using new login names with the original hashed passwords and CHECK_POLICY = ON. If you try to create that login and it fails, then you know that the original password was not complex enough.

But of course my logic was flawed – if you hash the password first, the policy can't be checked. How on earth could you check a hashed password against a complexity policy? You'd have to know the plain text, pre-hashed password. So that made me wonder: Why does login DDL support the use of the option CHECK_POLICY = ON with a hashed password?

In my opinion, this is a major security flaw. But the fact that the login gets created without so much as a warning is not even the worst part – what's even worse is that the metadata implies that it passed the policy check, when I know that it couldn't have!

A simple example

Let's walk through a basic, contrived scenario. If I have a local complexity policy enabled, then if I try to create this login, it should fail:

CREATE LOGIN demo1 WITH PASSWORD = N'simple', CHECK_POLICY = ON;

The error I would get:

Msg 15118, Level 16, State 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

Now, if I create this login without checking the policy, I can see what the hashed password will become:

CREATE LOGIN demo1 WITH PASSWORD = N'simple', CHECK_POLICY = OFF;
GO
SELECT password_hash FROM sys.sql_logins WHERE name = N'demo1';

Result (please ignore carriage returns):

0x02009C00D4E7EFF079267CC45794CA09088C491A897034
B8B53A33DE55B7A915409AA19A8112BCD81CBCB813A959E
11AA02F3EDEB2D2F436C15186BFDD844E076B09D4985B48

Now, I could bypass the policy this way in general, and I gather that many people probably do do this intentionally in order to have simple passwords (at least temporarily). However I bet that a lot of people also inadvertently do the following (for example when migrating logins): create a login with a hashed password, and expect CHECK_POLICY = ON to prevent any simple passwords from being used. Nothing could be further from the truth. If I take the binary value above and feed it directly into a CREATE LOGIN command, it will allow me to create a login with this simple password, even if I tell it to check the policy (again, please ignore carriage returns, which are here solely for readability):

CREATE LOGIN demo2 WITH PASSWORD =
0x02009C00D4E7EFF079267CC45794CA09088C491A897034
B8B53A33DE55B7A915409AA19A8112BCD81CBCB813A959E
11AA02F3EDEB2D2F436C15186BFDD844E076B09D4985B48
 HASHED, CHECK_POLICY = ON;

Worse yet, if I audit the sys.sql_logins catalog view to identify any logins that were created without checking the policy, the metadata assures me that this login has been validated – so if I'm trying to clean up my instance and remove/reset any insecure passwords, I'm not going to succeed.

SELECT name, is_policy_checked, password_hash
  FROM sys.sql_logins
  WHERE name LIKE N'demo[12]';

Results:

name    is_policy_checked  password_hash
------  -----------------  --------------
demo1   0                  0x02009C00D... 
demo2   1                  0x02009C00D...

Since I can't reverse engineer the password to check, and since I obviously can't trust the is_policy_checked flag, and since I can't review a log anywhere of which logins were created directly with a hashed password, I must make the assumption that *all* of the passwords on the system are insecure. The suggestion I ultimately had for this poor user was that the only way to ensure all of their logins had passwords that passed the complexity policy was to set strong passwords for all of them using MUST CHANGE, and of course you still must assume that anyone with sufficient privileges could just later override this with CHECK_POLICY = OFF or by using DDL to create a new, simpler, hashed password. So you either have to trust your users or you don't, and you have to break them temporarily to even get the chance.

Summary & Call To Action

Since many of you might use the password_hash value for a variety of reasons when creating, altering or migrating logins, it is possible that you have distributed weak passwords without knowing it. And if you relied on the existing is_policy_checked value, or made assumptions about how a password came to be, it will be impossible for you to tell which passwords actually meet the rules you thought were in place.

In my opinion, if I use HASHED in a create or alter login statement, I shouldn't also be able to say CHECK_POLICY = ON. Ideally, this combination of options would yield an error message. As a fallback, I'd settle for a warning message (but not one that could be suppressed by SET ANSI_WARNINGS OFF), but at a bare minimum, the metadata needs to reflect reality. There is no way that the is_policy_checked flag should be set to 1 when, in fact, it's not even remotely possible for the password to have been checked against the policy in the first place. If the metadata is really meant to indicate that the policy will be checked in the future, then both the DDL option and the column in sys.sql_logins should be named differently.

Your mission, should you choose to accept it, is to up-vote the Connect item I created about this issue, and let Microsoft know that you're serious about security and being able to rely on the metadata to tell you the truth:

Thanks in advance!

Comments ( 8 )

          • Greg Low says:

            An allied issue would be related to when an additional flag is cleared. When SQL Server is picking up account policy from the local machine, potentially via a domain membership, any change to that would also then have to trigger any existing SQL logins as not having been checked.

          • Aaron Bertrand says:

            Greg, sure, I believe there a bunch of things that need to be thought through, and I wasn't trying to provide an actual implementation spec. I just think the current functionality is misleading to all of the folks out there who don't realize the dual purpose of the existing option / flag.

          • Greg Low says:

            The flag is not called has_policy_been_checked. It simply indicates that this login will be checked if changed through standard mechanisms. You can't check a hashed password and it's necessary to be able to use one to transfer logins.

            Perhaps a better request would be for an additional flag that indicates whether or not policy has been checked.

          • Aaron Bertrand says:

            Greg, come on, be fair. The flag is arguably in past tense and is certainly not named will_be_policy_checked either. Ambiguity cuts both ways. If you read the whole Connect item you'll see that I asked for multiple things, including better documentation (well, any would be useful) as well as an additional flag and DDL option to distinguish the two very separate facts.

          • Cody says:

            You could ask to have the documentation updated, but I don't believe this is a special case any different from any other flag in the system. Knowing what they do through logic and experimentation and blog posts is why we are professional DBAs…

            Banks don't accept "very soon" just as they don't accept SQL logins, poor passwords on those logins, passwords not being changed on a schedule, and then passwords not documented so that admins have to copy hashes from server to server. The whole situation is a self-inflicted nightmare.

            So I don't think your question is fitting.

            The underlying problem to me appears to be business process based rather than something Microsoft have to address. That's my feeling and I guess we disagree :-)

          • Aaron Bertrand says:

            It was just a quick analogy. A lot of people won't accept "very soon" and strong passwords should not be an exception. I think you will find a *lot* of professional DBAs do exactly what I said when they migrate logins, because copying the hash is easier to automate, and obviously much safer than getting their users to give them their passwords on a sticky note. Some will even argue that the DBA and sysadmin should never know the passwords, so you'll have a hard time convincing them to document all the passwords in some binder on an IT person's desk.

            So yes, I guess we disagree.

          • Cody says:

            It feels like such a non-issue. That flag does not necessarily mean the policy is valid now, it also means to check the policy NEXT time. That's why it has to be set and shouldn't be forced off.

            Adding a warning? Sure, I guess so, if you really wanted… but I don't think it's a priority.

            I care about security too and that means documenting owners and forcing them to change SQL passwords to on a schedule. If you're doing that then all of this will weed out very soon anyway.

            Did I miss something?

          • Aaron Bertrand says:

            Cody, my main issue is that the column name (and the sparse documentation around it) lead people to believe that they can trust the password that's there now. There is nothing that even hints that this value really means, "well, it *will* be checked, next time." At best that is confusing; at worst, it is downright dangerous. What you're missing, I think, is that "very soon" is not soon enough for everyone. Would you deposit cash in a bank that is getting a vault and security guards "very soon"? What if you found out that they said they were FDIC insured but in reality they were going to be filing the paperwork for that "very soon"?

          Leave A Comment

          Your email address will not be published.

          This site uses Akismet to reduce spam. Learn how your comment data is processed.