Microsoft Best Practice: Security Failing

Dec 27, 2011 at 11:39 PM

SQL Server password policy and SQL Server password expiration is being evaluated against windows authentication and coming up as a failure on each instance. Any idea why? Please let me know. Thank you.

Dec 28, 2011 at 12:49 AM

Looks like they messed up that policy and forgot to include an important condition. Modify to the Condition to include 'AND @LoginType = SqlLogin'. This should cause it to ignore evaluation against Windows accounts.

Dec 28, 2011 at 5:28 PM

Thanks for the reply. I tried your suggestion and also tried @LoginType != WindowsGroup. It is still not working. I fired up another Dev SQL Server 2008 R2 and imported the 2 policies and evaluated against one instance. I am getting the same results. Please let me know if you have any other suggestions. Thank you very much!

Dec 28, 2011 at 6:45 PM

When you imported the policies, did you import the MS ones or the newly modified ones? Also, you don't need to import those policies on each server you evaluate on. You can use CMS or Local Registered Servers (only in 2008/2008 R2 SSMS) to evaluate policies from single server to multiple targets.

Dec 28, 2011 at 7:23 PM

I imported the MS ones and the modified ones with @LoginType =SQLLogin. And, yes I am using CMS but I was just testing the policy against one instance. Here is the result for the windows authentications:-

@PasswordExpirationEnabled = True --> Result: X

@LoginType = SqlLogin --> Result:X

Dec 28, 2011 at 7:44 PM

D'oh! Just caught my mistake. Ok so instead of modifying the condition itself (Password Expiration Enabled), we're going to create another condition. When you open the policy, in the box for Target it says 'Every Login'. Click on the down arrow and select New Condition. Create a new condition called 'SQL Logins Only'. Your expressions should be  @LoginType = SQLLogin AND @LoginType != WindowsUser AND @LoginType != WindowsGroup. Click OK to create new condition.

Your target condition should now show up there instead of every. Re-run policy and see if it gets results you want.

Dec 28, 2011 at 8:15 PM

YEAH that works. Thank you very much!!!

Dec 28, 2011 at 9:17 PM

You're welcome, glad I could help!