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.