SQL SERVER – Setting up a Robust Password Policy

Today’s topic of Setting up a Robust Password Policy often emerges during my Comprehensive Database Performance Health Check consultations. Recently, I had a conversation with a client who was uncertain about the origin of the SQL Server’s password policy. This conversation shed light on the fact that there’s a need for a clearer understanding of how SQL Server’s password policies tie into the wider Windows system.

SQL Server, one of the most popular relational database management systems, offers a wide array of security measures. One of these measures is the implementation of robust password policies.

Setting Up a New SQL Server Login

The first step toward secure data management is creating a new SQL Server Login. Here’s how you do it:

Launch your SQL Server Management Studio and connect to your SQL Server Instance.

Once connected, expand the ‘Security’ section. Right-click ‘Logins’ and choose ‘New Login…’ from the popup menu. This action will open up the Login dialog box.

In the Login dialog box, you’ll see checkboxes for ‘Enforce Password Policy’ and ‘Enforce Password Expiration’. If you select these checkboxes while creating the new SQL Server logins, SQL Server will leverage the Windows password policies set by the Windows administrator across the organization. Finally, click ‘OK’ to create the SQL Server Login. By default, the new login will have a ‘Public’ server role.

Finding the Password Policies

Now that you’ve created a new login, you might wonder where you can find the password policies. Here’s a quick guide:

Navigate to ‘Start | Control Panel | Administrative Tools | Local Security Policy’ or run ‘SECPOL.MSC’ from Command Prompt.

Expand ‘Security Settings | Account Policies | Password Policy’ to review the local policies on your machine.

Understanding Password Expiration

One crucial aspect of password security is password expiration. If the SQL Server password isn’t changed within the ‘Maximum Password Age’ window, it will expire, potentially leading to unplanned downtime for your applications. Therefore, it’s essential to change your password before the password expiration age in a planned way.

If your password expires, you will encounter the following error message in the SQL Server Error Log:

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

Wrapping Up

Establishing a robust password policy is a critical part of database security. It’s a simple yet effective way to enhance the security of your SQL Server environment and protect your data from unauthorized access.

You can always reach out to me on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)