How to configure password enforcement options for standard SQL Server logins

By:   |   Comments (6)   |   Related: > Security


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?


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:

PasswordSettings 01

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:

  • 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, August 8, 2014 - 9:12:01 AM - Nil Back To Top (34059)

Thanks man, I had to remove all the policies from all the accounts on a specific instance due to application testing. So below is a small piece of code should anyone require it that will remove it from all your users. You can execute the results to get it done and see what you are doing before you execute it... I know this goes almost every law of SQL security and best practices but it was requested by business ;)


USE master


SELECT 'ALTER LOGIN ' + '[' + name + ']'+ ' WITH



FROM sys.sql_logins

WHERE is_policy_checked = 1

or is_expiration_checked = 1

Thursday, September 19, 2013 - 5:55:06 PM - Moni Back To Top (26872)

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

Regard from Peru

Tuesday, August 28, 2012 - 1:31:19 PM - K. Brian Kelley Back To Top (19260)

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.

Tuesday, August 28, 2012 - 9:39:54 AM - srikanth Back To Top (19251)

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,




Monday, January 9, 2012 - 12:55:28 PM - Liliam Back To Top (15563)
You're on top of the game. Thanks for srhaing.

Tuesday, November 2, 2010 - 10:08:13 PM - Pascale Back To Top (10327)

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!!

get free sql tips
agree to terms