How do I configure the password policy for my Amazon RDS for SQL Server instance?

Short description

Password policy, lockout, and expiration intervals are configured at the host level (OS, Microsoft Windows layer). Because Amazon RDS is a managed service, access to the operating system is restricted. To configure the password policy, use DB parameters. For more information, see Using password policy for SQL Server logins on Amazon RDS for SQL Server.

When you use SQL Server Management Studio (SSMS) or T-SQL to create or modify a login, the password policy is turned on by default.

Resolution

Logins that use SQL Server Authentication

If your login uses SQL Server Authentication, then modify the SQL Server password policy to set custom requirements for complexity, length, expiration, and lockout. Configure the following password policy DB parameters in your custom DB parameter group:

• rds.password_complexity_enabled
• rds.password_min_length
• rds.password_min_age
• rds.password_max_age
• rds.password_lockout_threshold
• rds.password_lockout_duration
• rds.password_lockout_reset_counter_after

For more information, see Password policy parameters.

To identify the SQL Server logins that are configured with the password policy and the password expiration on the instance, run the following query:

select name, type_desc, create_date, modify_date, is_policy_checked,
       is_expiration_checked,  isnull(loginproperty(name,'DaysUntilExpiration'),'-') Days_to_Expire,  is_disabled

from sys.sql_logins

The following are the available options for password policy enforcement and password expiration for SQL Server logins:

• The policy_checked column is 0: The SQL Server login doesn't do password policy enforcement.
• The policy_checked column is 1 and is_expiration_checked is 0: The SQL Server login enforces password complexity and lockout, but not password expiration.
• The policy_checked column and is_expiration_checked are both 1: The SQL Server login enforces password complexity, lockout, and password expiration.

Logins that use Windows Authentication

Configure the password policy for your Windows logins in your Microsoft Active Directory. For information, see Working with Active Directory with Amazon RDS for SQL Server.