Enforce Password Policies and Password Expiration for SQL Server Logins

By: Editor
March 15, 2010

Introduction

This article demonstrates the steps to create an SQL Server Login which enforces password policies and password expiration policies feature that is available in Windows Server 2003 and later versions to passwords which are used inside SQL Server 2005 and higher versions.

Let us start by creating a new SQL Server Login.

1. Using SQL Server Management Studio, connect to SQL Server Instance.

2. Expand Security, and right click Logins to choose New Login… option from the popup menu to open up Login dialog box as shown in the snippet below.

3. In Login dialog box, you will be able to see checks boxes for Enforce Password Policy and Enforce Password Expiration as highlighted in the snippet below. If you select those check boxes while creating the new SQL Server logins, then the SQL Server will leverage the Windows password policies set by the windows administrator across the organization. Click OK to create the SQL Server Login with Public server role.

Create an SQL Server Login

Where I will be able to see the Password Policies?

1. Navigate to Start | Control Panel | Administrative Tools | Local Security Policy or Run SECPOL.MSC from Command Prompt.



2. To review the local policies on the machine expand Security Settings | Account Policies | Password Policy as shown in the snippet below.

Local Security Settings 

Important Note

If the SQL Server Password is not changed within the Maximum Password Age window then it will be expired. You will be able to see the below mentioned error message with in SQL Server Error Log. This can result in an unplanned downtime for your applications hence you should make sure you change password before the password expiration age in a planned way.

Logon Error: 18487, Severity: 14, State: 1.
Logon Login failed for user 'Login Name'. Reason: The password of the account has expired.

Conclusion

In this article you have seen how to leverage Windows Polices to enforce password policies and password expiration for SQL Server Logins.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."