Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

How to configure password enforcement options for standard SQL Server logins

MSSQLTips author K. Brian Kelley By:   |   Read Comments (5)   |   Related Tips: More > Security

Problem
I understand that starting with SQL Server 2005, SQL Server could enforce password complexity and password expiration. But I don't understand how that works or what the parameters are for either. I want to be able to explain to an auditor what my SQL Server is enforcing. How do I figure this out?

Solution
Yes, starting with SQL Server 2005, SQL Server is able to support both password complexity requirements as well as password expiration. These are nice security features that make SQL Server more secure than in previous versions. However, within SQL Server, there are no parameters you can set with regards to password length, when a password gets locked out, and how long until the password expires. That's because SQL Server pulls that information from the operating system. You can see what the values are by checking the local security policy on your server.

To do this, go to Start >> Control Panel >> Administrative Tools >> Local Security Policy if you're using the Classic View for Control Panel. Otherwise, go to Start >> Control Panel>> Performance and Maintenance >> Administrative Tools >> Local Security Policy.

Once inside the Local Security Policy console, expand Account Policies and you should see two subfolders, Password Policy and Account Lockout Policy, like in Figure 1.

Figure 1:

Password Policy

The Password Policy subfolder contains the password complexity settings like:

  • Password History - number of old passwords remembered
  • Minimum Password Age - how long before another password change can be attempted
  • Maximum Password Age - how old a password can be before it is expired
  • Minimum Password Length - how many characters a password must be to be acceptable.
  • Password must meet complexity requirements - enforces mixed case, etc., for the password

These values tell you the basics of what is acceptable for a password. They do not, however, tell you what would cause a password to be locked out and how long that lockout would last.  That information is contained in the Account Lockout Policy folder.

Account Lockout Policy

The Account Lockout Policy folder has the following settings:

  • Account Lockout Duration - how many minutes before a locked account is unlocked again
  • Account Lockout Threshold - how many failed login attempts can occur before the account is locked out
  • Reset Account Lockout Counter After - how long before the failed login attempts are reset to zero (assuming no successful logins have occurred since, since that automatically resets the failed logic count to zero).

Now if you go to change a setting, you may notice that it's grayed out. What this means is that the setting is being pushed down via Group Policy. It is being centrally managed by Active Directory and the setting cannot be overridden. If a SQL Server based login is configured to use the password policy, it will use these values.


It is entirely possible for a login not to use the password policy. Actually, there are three options for SQL Server logins:

  • The SQL Server login doesn't do any password policy enforcement at all.
  • The SQL Server login enforces password complexity and lockout, but not password expiration.
  • The SQL Server login enforces password complexity, lockout, and expiration.

The way to see what logins fit which of these three options is easily done by query the catalog view sys.sql_logins.

The following query provides that information:

SELECT 
    
[name]
  
is_policy_checked
  
is_expiration_checked
FROM sys.sql_logins;

  • If the is_policy_checked column is 0, then the login fits the first category. Nothing is being enforced.
  • If the is_policy_checked column is 1, but the is_expiration_checked is 0, then it fits the second category. The password complexity and lockout settings are used, but the password won't expire.
  • If both columns are equal to 1, then the login fits the third and final category, and all the settings are used, including password expiration.

Next Steps



Last Update: 12/28/2009


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Tuesday, November 02, 2010 - 10:08:13 PM - Pascale Read The Tip

Hello, I read your very useful article "How to configure password enforcement options for standard SQL Server login" - I had a quick question, what would password_hash mean in SQL server 2005 and is this related to password policy?  Should I be auditing or reviewing this?  What would it mean if it the column is 0 or 1.  Please advise.  Thank you!!


Monday, January 09, 2012 - 12:55:28 PM - Liliam Read The Tip
You're on top of the game. Thanks for srhaing.

Tuesday, August 28, 2012 - 9:39:54 AM - srikanth Read The Tip

Hi All,

  I forgot my sql authentication user id and password. Is there any chance to know about my user id and password.

Please clarify this,

Regards,

Srikanth.

 


Tuesday, August 28, 2012 - 1:31:19 PM - K. Brian Kelley Read The Tip

Short of brute forcing, srikanth, there's no way to recover the password. You're better off just getting someone with permissions to reset it.


Thursday, September 19, 2013 - 5:55:06 PM - Moni Read The Tip

Thank you very much for this useful article! It's good to find information so complete and understandable like this!

Regard from Peru



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.