SQL Server: Password policy FAQ


I am starting this post to collect frequent Q&A related to password policy. I plan to keep updating the post if anything new is worth adding to it. Note that this FAQ does not cover SQL Server Compact Edition. Also note that BOL stands for Books OnLine.


Q: What is the SQL Server password policy feature?


A: Password policy is a feature introduced in SQL Server 2005 for the purpose of strengthening SQL Authentication by having it enforce the same password policies that Windows is set to enforce. This means password policy is specific to SQL logins. A password policy determines what passwords are acceptable and also when they expire or whether the account should be locked out after a number of unsuccessful login attempts due to the use of incorrect passwords.


While the primary use of password policy is to strengthen SQL Authentication, password policy is also enforced whenever setting a new password with the encryption features introduced in SQL Server 2005 – this enforcement only refers to password strength; there is no expiration/lock out enforcement for these passwords. This enforcement cannot be disabled.


Q: On what versions of SQL Server is the password policy available?


A: The password policy enforcement is done using a new API introduced in Windows 2003: the NetValidatePasswordPolicy API. Thus, password policy is only available on versions of the Microsoft Windows OS starting with the 2003 version. Note that if this API ever gets backported to older OSs, then SQL Server should automatically start using it. The best way to determine if something changed about the availability of this feature on older OSs is to check the API page for any updates.


Note that on older OSs, SQL Server does perform a few basic password complexity checks, as described at the end of this password policy BOL article. However, this is all that is available on older OSs – there are no lock out or expiration features.


Password policy is not restricted based on SQL Server edition.


Q: How do I use the password policy feature?


A: If you are creating a new login using CREATE LOGIN, you can use two clauses related to password policy: CHECK_POLICY and CHECK_EXPIRATION. If they are not specified, the default for CHECK_POLICY is ON and for CHECK_EXPIRATION is OFF. These options can be changed at a later time using ALTER LOGIN. CHECK_POLICY governs the bulk of password policy enforcement related to password strength and lock out. CHECK_EXPIRATION separately covers the enforcement of password expiration, which was considered too disruptive for the rolling out of this feature, to be enabled by default. CHECK_EXPIRATION is also required when using the MUST_CHANGE option, to force a password change on the first login through that account. CHECK EXPIRATION depends on CHECK_POLICY; other dependencies are covered in BOL.


Q: How do I manage this feature using Management Studio?


A: There should be checkboxes in a login properties dialog corresponding to the T-SQL options. A GUI can change more often than T-SQL syntax, so I prefer to describe features using the T-SQL interface. Also, I prefer to use only T-SQL for SQL Server management operations.


Q: How do I check the password policy settings on my system?


A: Execute secpol.msc, then look at Account Policies. If you are on a standalone machine and you are an administrator, you can change these settings. If your machine is joined to a domain, these settings are controlled by the domain administrator.


Q: Why can’t I set the policy options for Windows logins in SQL Server?


A: Because Windows logins already benefit from password policy enforcement at the OS level.


Q: I keep getting messages about my password not being acceptable when creating a new login. How do I fix this?


A: The error message will usually indicate what the problem is; for example, it will tell you if the password is too short. To find more details about what restrictions are in place for passwords, you can check the policy settings using the secpol.msc tool, as described in a previous answer. You can also disable the policy enforcement for the login, but this is not recommended as it can make that login more vulnerable to a bruce force attack.


Q: I am running SQL Server on Windows XP/2000, etc. Does this mean I cannot benefit from password policy enforcement?


A: Pretty much, yes. On OSs that don’t support the password policy API, SQL Server only performs a tiny number of checks on the password strength – to prevent you, for example, from using an empty password. The actual checks are described in the last part of this password policy BOL article. These checks are governed by the CHECK_POLICY settings, so they can be turned off or on. If you ever hit a password policy error on older OSs than Windows 2003, then you really need to revisit the method you are using to pick passwords.


Q: I am getting a password policy error even though I run on Windows XP/2000. I thought there is no password policy enforcement for these systems. What’s going on?


A: You are using really weak passwords. See above answer.

Comments (13)

  1. rsisk101 says:

    Q: Who or what password policy is enforced when creating a Database Master key? Is it based on the user doing the Create? On the service account of the SQL Server?

  2. lcris says:

    A: Password policy is system specific, not account specific. SQL Server will enforce the machine’s password policy, or if a domain specific policy is in effect, it will enforce the domain’s password policy.

  3. vikash jain says:

    how to create three time enter password  user  login lock

  4. lcris says:

    If you want a user account to be locked out if a password is incorrectly entered three times, you need to set this option in Windows and have CHECK_POLICY turned ON for that login. See secpol.msc/Account Policies/Account Lockout Policy/Account lockout threshold.

  5. SQL User says:

    Hi, We have a SQL 2005 system running on Windows 2003 with Password Policy Disabled at the domain level. "Enforce Password Policy Check" option is enabled for the 'sa' user account. Even then, the SQL server throws an error about the 'sa' password not meeting Password policy requirements, when we tried changing the password for 'sa' ..

  6. lcris says:

    Is password policy enabled locally perhaps?

  7. SQL User says:

    Hi Laurentiu,

    Many thanks for the response !

    The Local Password Policy under Account Policies in 'Secpol.msc' is grayed out since the policy is set ('Disabled') at the Domain Controller as a group policy.. Even then the SQL server seems not to consider the "Enforce Password Policy", which in this case should not check for any policy since its disabled at the Domain Controller.. Kindly advise if additional details are required.. Thanks !

  8. lcris says:

    Well, I have only one more suggestion: check the end of this article: msdn.microsoft.com/…/ms161959.aspx. You may be hitting the minimal checks that are done when password policy is not available.

    If your password is indeed complex and you still get a password policy error, contact the SQL Server team following the instructions I mentioned here: blogs.msdn.com/…/how-to-request-features-in-microsoft-products.aspx. Post here a link to the thread or report that you opened and I'll keep an eye on it.

  9. SQL User says:

    Hi, If the password policies :  'Password must meet complexity requirements' is "disabled", "Minimum Password Length" is set to "0"  at the Domain Controller as a group policy and if the same policies are grayed out under local security policy since group policy is in place, the SQL Server should eventually ignore anything with regards to password complexity right ?.. Also, we have other domain member servers with SQL 2005 on them. All the other SQL servers except the server in question did not complain about password policy at all and use the same password for 'sa' user account..

    Initially, The SQL server install on this particular server threw an error ( [Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa'. Refer to server error logs and setup logs for more information. ). The error log had the following (

    DateTime Logon Error: 18456, Severity: 14, State: 10

    DateTime Logon Login failed for user 'sa'.

    )

    We completed the install by adding a registry key "SQLArg3" with the value  "-T4606", as per this post :

    blogs.msdn.com/…/login-failed-for-user-sa-while-installing-sql-server-2005.aspx

    Now, after the install, we deleted the registry key "SQLArg3"  and we are not able to log on to the SQL server with 'sa' user account,  until "Enforce Password Policy" option is unchecked for user 'sa' …

    It appears that even thought Password Policies are disabled both at the Domain Level and locally, the SQL server somehow tries to check for password complexity for the 'sa' user account.. But all the other SQL 2005 servers, which are also Domain Members doesn't seem to have any issues and don't complain about the password policy for 'sa' account..

    It would be of great help if you could help us on finding the root cause and also to make the "Enforce Password Policy" on this SQL server to not check for complex passwords since its disabled at the Domain level and locally..

    Kindly advise if additional details are required..

    Many thanks !

  10. lcris says:

    Keep in mind that I haven't worked with SQL Server in the past 4 years – I wrote this post after I left the SQL Server team and my memory of this feature hasn't improved with time.

    Based on what you told me so far, it does indeed look like the password policy is enforced when it shouldn't be. If I remember well, traceflag 4606 disables password policy checks – this is why things work as you expect when you enable it, and don't when you disable it. Why this happens is something I don't know.

    In the meantime, as you discovered already, you can use traceflag 4606 to deactivate password policy checks. Or you can disable password policy enforcement on that sa login.

    You really need to contact the SQL Server team to make more progress on this issue, like I adviced before. Open a thread describing this problem on the SQL Server security forum (social.msdn.microsoft.com/…/threads) and that way you can get the SQL Server security team looped in. The only information I require from you now is the url for that thread – let's continue the discussion there and I'll pull some other people to help.

  11. James says:

    In a Windows server 2003, sql 2005 environment, we have a domain password policy set on the server (that cant be changed) and we have turned off all of the "enforcement" on the logins to ensure we are not following the password policy currently. We are looking into turning this back on within our environment but we do alot of users that this will affect. Since this environment already exists we need to know what will happen when it gets turned on. Specifically….

    Once we turn on the enforce password policy on all of these users, when will the "countdown" begin for the passwords to actually expire? will they automatically be prompted to change their password next login, or will the X amount of days start once we turn this on?

    If a password isnt compliant with the policy when we update a user to enforce the policy, what will happen?

    Any other things i need to consider when turning this on?

  12. lcris says:

    Keep in mind that there are two options: CHECK_POLICY and CHECK_EXPIRATION. If you keep CHECK_EXPIRATION off, then passwords won't expire. If CHECK_EXPIRATION is on, the expiration countdown will be based on the password last set time value, not on the time when you enabled expiration. So for logins that have not changed their passwords in a long time, they would be expired when they next attempt to login.

    You can use the loginproperty builtin to gain visibility into the policy settings and find out things like last password change time:

    msdn.microsoft.com/…/ms345412.aspx

    After you turn on expiration on a login, you can query the DaysUntilExpiration property to see when it will expire.

    As for the compliant password question, SQL Server will enforce the policy only for new passwords. Existing weak passwords will remain weak until they expire or they get changed.

    Expiring the passwords of existing logins would be the biggest impact you can expect. You might not even want to turn on expiration at the same time as policy. Turn policy setting on first, then ask everyone to change their password, then turn on expiration.

    I suggest you first verify the behavior by turning on these options on a single existing login. Also, for safety, you should also ask this question on the security forum I linked to in my previous post. I haven't worked on this feature in years and I may forget details that may be of use to you.

  13. lcris says:

    Sorry for the late response. Notifications are not working well on the current blog platform, but we're moving to a new platform these days, which hopefully will solve this issue.

    From BOL, it looks like accounts get disabled when their password expire. If SA gets disabled, I would expect various things to stop working. But I recommend contacting the SQL Server team on the MSDN forums, to ask for more information – I no longer work in SQL Server, so I forgot the details of these features over the years.