Configure password policy for Amazon RDS for SQL Server

Amazon Relational Database Service (Amazon RDS) for SQL Server now supports configuring password policies for logins using SQL Server Authentication. This feature allows you to create custom password policies tailored to your business requirements. Password policies for SQL Server define various rules relating to the evaluation of passwords and maintaining entities that use those passwords. These policies can include:

• Enforcing password length and complexity requirements on new passwords
• Enforcing password expiry and rotation
• Locking out accounts when an incorrect password has been entered too many times

In this post, we guide you through the process of enabling the password policy for Amazon RDS for SQL Server and creating a SQL Server login that adheres to the policy.

Overview of different authentication for SQL Server

A SQL Server login is a server-level object that represents a security principal that can be authenticated to the database. Any user needs to authenticate using a login to connect to a SQL Server instance. Create a login in the following forms:

• Using SQL Server Authentication (a login name and password)
• Using Windows Authentication (a Windows user or domain account)
• From a certificate
• From an asymmetric key

In this post, we focus on how to configure the password policy for logins using SQL Server Authentication.

Prerequisites

To follow this post, you need the following prerequisites:

• RDS for SQL Server instance
• SQL Server Management Studio (SSMS) with access to connect to the RDS for SQL Server instance
• Custom parameter group

Create or modify a parameter group

You can enable the password policy on Amazon RDS for SQL Server through a parameter group. For more information, see Working with parameter groups.

The following table lists the parameters you can configure to set up SQL Server password policies. All the following parameters are dynamic, and you can apply changes immediately to the RDS database instance without requiring any reboot.

You can create a new parameter group or use an existing parameter group based on the version and edition of your RDS instance. You must have a custom parameter group. Create a new parameter group if your RDS instance is running with a default parameter group. Assuming that you already have an RDS for SQL Server instance and a custom parameter group attached to it, note down the name of the parameter group. Then complete the following steps:

1. On the Amazon RDS console, locate your RDS for SQL Server instance.
2. On the Configuration tab, choose the DB instance parameter group rds-sql-parametergroup.
   
3. Search for parameters that contain the text password in the name.
   This will load all the parameters related to password settings on the RDS instance.
   
4. Choose Edit to modify the parameter values.
5. Change the value of rds.password_complexity_enabled from 0 to 1.
6. Choose Save Changes.
   

Note: In an RDS for SQL Server Multi-AZ configuration, the password policy applies to both primary and standby instance.

Configure password complexity for SQL Server logins

Now that you have enabled the parameter rds.password_complexity_enabled, connect to the RDS instance and complete the following steps to apply the password complexity policy to one of the existing logins:

1. Open SSMS.
2. Connect to the RDS for SQL Server using a login with the ALTER ANY LOGIN permission.
3. Expand the folders Security, Logins.
4. Choose one of the existing logins to view its properties.
5. Reset the password for the login by entering a complex password.
6. Select Enforce password policy and Enforce password expiration.
7. Choose OK.
   

Setting up password lockout policies

Let’s take a step further and add a lockout policy to the RDS for SQL server instance. There are three parameters that control the lockout behavior. (Refer to the above parameter table for more details):

1. rds.password_lockout_threshold (Default value = 0)
2. rds.password_lockout_duration (Default value = 10)
3. rds.password_lockout_reset_counter_after (Default value = 10)

To enable the lockout policy for the SQL Server logins, navigate back to the RDS parameter group and update the rds.password_lockout_threshold parameter from 0 to 3 . This setting will lock out the SQL Server login after 3 unsuccessful password inputs.

rds.password_lockout_thresholdis a dynamic parameter and it will apply to all logins with CHECK_POLICYenabled.

Now that you have enabled a lockout policy, for any SQL Server login with “CHECK_POLICY” or “Enforce password policy” enabled, the login will be locked out after 3 consecutive failed login attempts using an incorrect password.

You can verify the SQL Server login status from the SQL Server Management Studio as shown in the following figure.

Alternatively, you can also refer to the SQL server logfile by running

EXEC rdsadmin.dbo.rds_read_error_log

RDS for SQL Server error log shows that after 3 incorrect password attempts the login user1 is locked out and not able to connect even after providing the correct password. As per the configured parameters in the example, the lockout period will expire after 10 minutes. A database administrator can manually unlock logins which are locked out anytime.

Note: Configure Database Activity Streams for SQL Server Auditing to track failed logins for proactive monitoring.

Clean up

If you no longer require this setup and want to avoid future charges, you can delete RDS for SQL Server instance.

Conclusion

In this post, we shared how to enforce the password policy for logins using SQL Server Authentication in RDS for SQL Server database instances. This feature enables you to configure the password policies for SQL Server logins based on your organization’s requirements.

Try out the new feature, and if you have any comments or questions, leave them in the comments section.

About the authors

Vikas Babu Gali is a Sr. Specialist Solutions Architect, focusing on Microsoft Workloads at Amazon Web Services. As a native of India, Vikas enjoys playing Cricket and spending time with his family and friends outdoors.

Wasim Shaikh is a Senior Partner Solutions Architect specializing in databases at AWS. He works with customers to provide guidance and technical assistance about various database and analytical projects, helping them improving the value of their solutions when using AWS.