SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Change SQL Server Login Properties to Enforce Password Policies and Expiration Settings

How to set SQL Server Password Policy | Set SQL Password Policy

This article demonstrates the steps to create an SQL Server Login which enforces password policies and password expiration policies.

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 SQL Server Login and Enforce Password Policy and Enforce Password Expiration
Create SQL Server Login and Enforce Password Policy and Enforce Password Expiration

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 Password Policy
Local Security Settings Password Policy

Enforce Password Policy SQL Server | SQL Server Password Requirements

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.

Error Message

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.

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement