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.
DB Parameter
Description
Allowed Values
Default Value
rds.password_complexity_enabled
Password complexity requirements must be met when creating or changing passwords for SQL Server logins.
0,1
0
rds.password_min_length
The minimum number of characters required in a password for a SQL Server login.
0-14
0
rds.password_min_age
The minimum number of days a SQL Server login password must be used before the user can change it. Passwords can be changed immediately when set to 0.
0-998
0
rds.password_max_age
The maximum number of days a SQL Server login password can be used before the user is required to change it. Passwords never expire when set to 0.
0-999
42
rds.password_lockout_threshold
The number of consecutive failed login attempts that will cause a SQL Server login to become locked out.
0-999
0
rds.password_lockout_duration
The number of minutes a locked-out SQL Server login must wait before being unlocked.
1-60
10
rds.password_lockout_reset_counter_after
The number of minutes that must elapse after a failed login attempt before the failed login attempt counter is reset to 0.
1-60
10
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:
On the Amazon RDS console, locate your RDS for SQL Server instance.
On the Configuration tab, choose the DB instance parameter group rds-sql-parametergroup.
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.
Choose Edit to modify the parameter values.
Change the value of rds.password_complexity_enabled from 0 to 1.
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:
Open SSMS.
Connect to the RDS for SQL Server using a login with the ALTER ANY LOGIN permission.
Expand the folders Security, Logins.
Choose one of the existing logins to view its properties.
Reset the password for the login by entering a complex password.
Select Enforce password policy and Enforce password expiration.
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):
rds.password_lockout_threshold (Default value = 0)
rds.password_lockout_duration (Default value = 10)
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.
Source: Read More
