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

Short description

Password policy and expiration intervals are configured at the host level (OS, Microsoft Windows layer). Amazon RDS is a managed service. So, modifications to the password policy aren't possible due to restricted access to the operating system.

The password policy is turned on by default in RDS for SQL Server when a new login or modified using SQL Server Management Studio (SSMS) or T-SQL.

Resolution

The RDS for SQL Server instance isn't joined to an Active Directory

If the instance isn't joined to an Active Directory, then the policies are defined on the Windows operating system. You can't modify these policies. The following are the values configured on the Windows password policy:

• Enforce password history: 0 passwords remembered
• Minimum password length: 0 characters
• Password must meet complexity requirements: Disabled
• Store passwords using reversible encryption: Disabled
• Minimum password age: 0 days
• Maximum password age: 42 days

Note: An account lock out policy isn't possible for RDS for SQL Server instances that aren't joined to an Active Directory. An account lockout policy requires access to the underlying OS. Amazon RDS is a managed service, so host-level access isn't available.

The RDS for SQL Server instance is joined to an Active Directory

You can enforce and modify password policies if you're using Windows Authentication for RDS for SQL Server. This applies to Windows Authentication only. SQL Authentication uses the local password policy defined in the previous section whether or not it's joined to a domain.

Run the following query to identify the SQL Server logins configured with the password policy and the password expiration on the instance:

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:

Note: These options are only for RDS for SQL Server instances that are joined to a domain.

• The policy_checked column is 0: The SQL Server login does not do any password policy enforcement.
• The policy_checked column is 1 and is_expiration_checked is 0: The SQL Serve 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.

If policy_checked and is_expiration_checked are both 0, then the policy is for the primary user in your RDS for SQL Server DB instance. This indicates that password complexity, lockout setting, and password expiration aren't set for the primary user. So, the primary user doesn't expire in RDS for SQL Server. If your primary user loses access, you can reset the password. For more information, see Why did the master user for my RDS for SQL Server instance lose access and how can I gain it back?