Understanding Password Policy for SQL Server Logins.

Article ID: 2028712 - View products that this article applies to.
Expand all | Collapse all

SUMMARY

Windows Logins abide by the login policies of the underlying Operating System. In SQL Server 2005 and later, SQL Server logins can also adhere to the windows login policies if the operating system version is Windows Server 2003 and later. The parameters specified in the "CREATE LOGIN" T-SQL command dictate if the login policy is enforced. The CHECK_POLICY parameter specifies that the SQL Login must abide by the Windows Login policy and Account Lockout policy which includes the password strength. This option is also available when creating the Login using SQL Server Management Studio.Here is a list of Best Practices for password policy.

Best practices for password policy

·         Mandate a strong password policy, including expiration and a complexity policy for your organization.

·         Make sure that the password is at least 8 characters long.

·         If you must use SQL logins, ensure that SQL Server 2005 runs on the Windows Server 2003 operating system and use password policies.

·         Outfit your applications with a mechanism to change SQL login passwords.

·         Set MUST_CHANGE for new logins. If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON.


MORE INFORMATION

For more information regarding the Password Policy, please refer to the White Paper http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx

Additional Resources

Connecting to SQL Server When System Administrators Are Locked Out

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:


Collapse this tableExpand this table
Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated 
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)





SQL login Password Policy Strength and password Expiry





The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect when an instance of SQL Server 2008 R2 contains SQL Logins that do not adhere to the windows password policy.

If you run the BPA tool and encounter a Warning with the title of Engine – SQL Login Password Policy Strength and password expiry, then your SQL Server 2008 or SQL Server 2008 R2 contains SQL logins that were created without password policy enabled.
SQL Server 2008
SQL Server 2008 R2







SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)





SQL login Password Policy Strength and password Expiry




The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect when an instance of SQL Server 2012 contains SQL Logins that do not adhere to the windows password policy.

If you run the BPA tool and encounter a Warning with the title of Engine – SQL Login Password Policy Strength and password expiry, then your SQL Server 2012 contains SQL logins that were created without password policy enabled.
SQL Server 2012 








Properties

Article ID: 2028712 - Last Review: April 2, 2012 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 Standard
Keywords: 
KB2028712

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com