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
https://msdn.microsoft.com/en-us/library/ms189751.aspx
Comments
- Anonymous
March 24, 2009
PingBack from http://www.anith.com/?p=22354 - Anonymous
June 23, 2010
The default for check_policy is actually 'on' - your link to the MSDN site states this clearly: -"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." - Anonymous
June 08, 2011
Good info. thanks. - Anonymous
October 03, 2013
What if CHECK_POLICY is set to ON but CHECK_EXPIRATION is set to OFF. Does it means that the policy will be applied to all the other parameters of the account except Password EXPIRATION ? - Anonymous
October 29, 2013
Correct, CHECK_POLICY ON but CHECK_EXPIRATION OFF you get all policy except expiration. - Anonymous
November 21, 2013
Are there settings in Active Directory that can drive whether a new SQL install defaults to Check_Policy= ON? We were recently trying to install UPS Worldship and the installation kept failing because the password on the default admin acct for the bundled SQL Server Express didn't conform to policy. When we attempted installation on a domain computer, the install failed because of a non-conforming password. When we attemted installation on a non-domain computer, it was successful. - Anonymous
February 05, 2014
No, SQL Server always defaults to CHECK_POLICY = ON. - Anonymous
February 19, 2014
what are the default sql password policy settings if "enforce password policy" is off / unchecked?is there a window in sql that you can pull up to manage global password policy for the database or does it use the local machine's password policy?