SQL Server 2005 Login Security - CHECK_POLICY and CHECK_EXPIRATION setting, how it affects the password policy?

Adding password complexity and password expiration to SQL Server logins is one of best feature I quote for SQL Server version 2005 onwards, similarly such password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced and password expiration policies are used to manage the lifespan of a password.

In any of the above cases the SQL Server engine runs the check to enforce password expiration policy and check in order to ensure the users are reminded to change old passwords, and accounts that have expired passwords are disabled. Similar to this you may get the error mentioned below:

Property IsLocked is not available for Login '[x]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo) 
 

Previously I blogged here to solve this problem over here -  LoginPropertyLocked and this issue occured on our side when a SQL instance is  installed with Windows Authentication and then switch to SQL Server Authentication, sometimes it  may not come into affect unless the SQL Server services are restarted. Further questions can arise such as will this happen on the instances which is installed with SQL Server Authentication or any chance in performing SQL Server installer to have any settings to turn off the check password policy, the answer is not quite easy. Say if you need to disable the check password policy on that SQL instance then you have to use below TSQL:

alter login sa [X] with password = 'yourpwd' unlock, check_policy = off, check_expiration = off

Further you have to ensure to disable password policy when using sp_addlogin, as this is deprecated from SQL 2005 onwards and you should continue to use CREATE LOGIN instead, as sp_addlogin statement. Also the Policy Enforcement can  be configured separately for each SQL Server login. Use ALTER LOGIN to configure the password policy options of a SQL Server login. When they are ON then relational engine sets the CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF, and to the  password history is initialized with the value of the current password hash. Whereas when the  CHECK_POLICY is changed to OFF, then CHECK_EXPIRATION is also set to OFF having the password history is cleared too! So ensure to use this sensibly within your Production server instance.

In few cases when SQL Server is running on Windows 2000, setting CHECK_POLICY = ON will prevent the creation of passwords when used NULL or empty. Also affects if you try to create a SQL login with the same as name of computer or login. So in the case of using the hashed password when creating the login, the password policy cannot be checked for complexity on such password, as the system cannot get the original password from the system and only this is available in hash, but the gain is you this will come into affect since that point of creation, that means when the user triesto change the password during next attempt or time. I recommend to refer to http://blogs.msdn.com/lcris/archive/2007/04/30/sql-server-2005-about-login-password-hashes.aspx blog post for such information.

Further Books online confirms that within  Windows Server 2003 a known issue that might prevent the bad password count from being reset after LockoutThreshold has been reached. This might cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON. During the recent usergroup meeting one user asked that is there a method in SQL 2005 to globally disable the CHECK_POLICY option? The answer is CHECK_POLICY cannot be disabled globally. You will have to do it at the login-level only that means when a single CREATE LOGIN statement is used, as you cannot use that in a transaction. Also I have seen such issues when you are using a web based application within ASP.NET stating the sql login is failed though it has relevant permissions set,  and in this case I suggest to refer the notes from BOL (again):

MUST_CHANGE
Applies to SQL Server logins only. If this option is included, SQL Server will prompt the user for a new password the first time the new login is used.

CHECK_EXPIRATION = { ON | OFF }
Applies to SQL Server logins only. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.

If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.

A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.

When CHECK_POLICY is set to OFF, lockout_time is reset and CHECK_EXPIRATION is set to OFF.


CHECK_POLICY = { ON | OFF }
Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.