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:

• CHECK_POLICY is a no-op with CREATE LOGIN … PASSWORD … HASHED

Thanks in advance!

Aaron Bertrand

Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.

Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.