11

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.

  • 1
    What Conference ? SQL Server days? BICC? Did a quick search, it seems that there is no native function that will quickly give you a result. It might be possible to use a third party tool. – Stijn Wynants Apr 15 '15 at 13:15
  • SQL Server days. I've done some quick searches as well, and other than some tools that just do dictionary attacks using PWDCOMPARE() I haven't really found much, regardless thank you for the effort! – Reaces Apr 15 '15 at 13:35
  • You could validate the policy on the next login and force a reset for all the users who don't log in before the audit. Less user annoyance compared with reseting all the passwords. – CodesInChaos Apr 15 '15 at 15:31
  • @CodesInChaos How do you validate the policy during a login event? – Aaron Bertrand Apr 15 '15 at 15:34
  • 1
    I've blogged about this issue - and I encourage all of you to up-vote and comment on the Connect item. – Aaron Bertrand Apr 20 '15 at 20:24
15

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 of ALTER 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).

SELECT N'ALTER LOGIN ' + QUOTENAME(name) 
  + N' WITH PASSWORD = N'''' MUST_CHANGE, CHECK_POLICY = ON;' 
  FROM sys.sql_logins 
  --WHERE is_policy_checked = 0;

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 to is_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 with CHECK_POLICY = ON, and make a note of every one that fails. However, this can not work - even with CHECK_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.

SELECT N'BEGIN TRY
  CREATE LOGIN ' + QUOTENAME(N'copy_of_' + name) 
    + N' WITH PASSWORD = ' 
    + CONVERT(NVARCHAR(255), password_hash, 1)
    + ' HASHED, CHECK_POLICY = ON;
  DROP LOGIN ' + QUOTENAME(N'copy_of_' + name) + ';
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 15118
    PRINT N''' + REPLACE(name, '''', '''''') 
      + N' was not complex enough.'';
END CATCH'
FROM sys.sql_logins;

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.

  • Another complication is that I'm actually quite sure the previous admin just checked this policy on after putting in easy to remember passwords. select @@SERVERNAME as servername, name FROM sys.sql_logins where PWDCOMPARE(name, password_hash) = 1 AND is_policy_checked = 1; gave several positive results. So I'll have to do this for all the logins, not just those with is_policy_checked turned off. – Reaces Apr 15 '15 at 14:07
  • @Reaces Possibly. Or your password policy in Windows could be weak or disabled, so trying to enforce with the policy doesn't help. – Aaron Bertrand Apr 15 '15 at 14:10
  • I just tried creating the same user on a second server, and got a password validation failed. So I guess the complexity check works. I like the idea of recreating copies of the users though! I'll set to work creating a script that does just that! EDIT: actually I could just use sp_help_revlogin to do the heavy lifting. – Reaces Apr 15 '15 at 14:16
  • "it doesn't perform any validation of an already-hashed password" How would it do that? If you only know the hash and not the plain password, you can't check the complexity of the password. You could guess the password, but that'd be very expensive for a decent password hash since the whole point of a password hash is preventing recovery of the plaintext password. – CodesInChaos Apr 15 '15 at 15:33
  • @CodesInChaos I know, that's my point... I said "doesn't" but could have also written "couldn't possibly"... – Aaron Bertrand Apr 15 '15 at 15:35
4

There is no way that you are going to get this 100% accurate. Though you can use PWDCOMPARE to check against a list of weak passwords (you can add to the list of weak passwords and do a comparison).

I have written a similar script that does the comparison and gives you the results. I have posted it on github.

EDIT:

Now you can have a list of weak passwords in a csv and then use dbatools Test-DbaLoginPassword with -Dictionary switch (Specifies a list of passwords to include in the test for weak passwords.)

  • I'd very much like to use your script if I ever have to do an audit myself of a particular user's password. However not knowing what the auditor is going to do I'd rather just ensure that everything is policy compliant and rely on those creating the policies. Thanks though! +1 – Reaces Apr 15 '15 at 14:23
0

Password Policy per SQL Login is only a flag for on or off. If the Password Policy flag is checked, then the Windows Password Policy from the operating system are enforced.

Check the CREATE LOGIN documentation for the details on what happens when CHECK_POLICY and CHECK_EXPIRATION are set.

You can see the settings per SQL user by checking columns is_policy_checked and is_expiration_checked in sys.sql_logins

something like below :

SELECT name,
create_date,
modify_date,
LOGINPROPERTY(name, 'DaysUntilExpiration') DaysUntilExpiration,
LOGINPROPERTY(name, 'PasswordLastSetTime') PasswordLastSetTime,
LOGINPROPERTY(name, 'IsExpired') IsExpired,
LOGINPROPERTY(name, 'IsMustChange') IsMustChange
From sys.sql_logins ;

For SQL Server Authentication Logins:

select * from sys.server_principals where type in ('U','G') - will show you the logins and groups that can access a SQL Server via Windows Authentication.

  • Updated my question a bit, as I was already using a similar query. My main issue is that I need to assure my boss that the passwords are policy compliant, however not knowing the passwords I don't know how I could. – Reaces Apr 15 '15 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.