Enforce Windows Password Policy on SQL Server Logins
If users choose to use SQL login to connect to SQL Server rather than using NT authenticating, it is worth to remind that SQL server does provide the option of enforcing window password policy on SQL logins.
When creating a SQL login you can specify CHECK_POLICY=on, which will enforced on this login of the Windows password policies of the computer on which SQL Server is running.
On WinXP and Win2k system, the Windows password policy only checks if the password is complex enough (a complex password should include at least three combinations of lower-case alphabet, upper-case alphabet, number, or special characters etc).
On Win2003 and above system, the windows password policy check against password minimum length, password history (password can’t be used if it is the same as previous N password), the password minimum life (password can’t be changed within a minimal time since creation) and maximum life (password is forced to expire after the maximum life), and login can be locked out if inputting wrong password continuously for certain times. The option of CHECK_EXPIRATION will enforce password expiration policy, and you can only specify CHECK_EXPIRATION=on when the CHECK_POLICY=on.
By default, both CHECK_POLICYand CHECK_ EXPIRATION are off. If you do not turn on the password policy at login creation time, you can always use ALTER LOGIN to change the value of these options. Also you can query sys.sql_logins to look at the current property value of the login.
For more information check