Account password policies manage password settings at the Windows host layer. RDS SQL Server allows you to configure account password policies through the console or API to enhance security by precisely managing password validity periods. By default, user account passwords never expire. You can manually apply password policies to user accounts. Host account passwords are valid for only 42 days by default. Password expiration causes logon failures. We recommend that you configure password policies in advance. Host accounts automatically apply these policies without manual configuration.
Prerequisites
The RDS instance must meet the following requirements:
Instance type: General-purpose or dedicated (not shared)
Billable methods: subscription or pay-as-you-go (not Serverless instances)
The RDS instance does not run SQL Server 2008 R2.
Notes
When you create an account and apply the password policy that you configured for the account, the password cannot contain the username of the account. For example, if the username is Test240903, the password cannot be set to Test240903abc.
Step 1: Configure a password policy for an account
Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Accounts.
Click Account Password Policy, configure the following parameters, select the target policy, and then click OK.
You can configure one or both of the Maximum password age usage time and Minimum password usage time parameters.
Parameter
Description
Value range (days)
Maximum Password Age
The period of time that a password can be used before the password must be changed.
0~999
Minimum Password Age
The period of time that a password must be used before the password can be changed.
NoteThe value of this parameter cannot be greater than the value of the Maximum password age parameter.
0~998
Example 1: Configure only the maximum password age
If you want passwords to be valid for a maximum of 60 days and require users to change their passwords after 60 days, you can set Maximum Password Age to 60 days.
Example 2: Configure only the minimum password age
If you want passwords to be used for at least 30 days and prevent users from changing their passwords within 30 days, you can set Minimum Password Age to 30 days.
Example 3: Configure both the maximum and minimum password age
If you want users to change their passwords every 90 days and use new passwords for at least 30 days, you can set Maximum Password Age to 90 days and Minimum Password Age to 30 days.
Step 2: Apply the password policy to user accounts
For user accounts, you need to manually apply the password policy. Host accounts automatically apply the password policy without manual configuration.
Apply the password policy when you create a user account
On the page, apply the password policy when you create a standard account or a privileged account or a database account with SA permissions.
Apply the password policy to existing user accounts
On the page, apply the password policy to existing accounts.
FAQ
By default, host accounts use the Windows password policy, with a default expiration time of 42 days. If RDS SQL Server creates a host logon account, the password expires after 42 days by default. After expiration, you need to reset the password.
RDS SQL Server supports Custom password policy. You can modify the maximum or minimum password age in the RDS console as needed. After modification, host accounts automatically apply the new policy with immediate effect. If user accounts also have the password policy applied, the policy also takes effect immediately. However, note that shared instances, SQL Server 2008 R2 instances, and Serverless instances do not support this feature.
By default, user accounts created through the RDS console do not have the Windows password policy enabled. If you need to enable password expiration policies, you can modify the user's CHECK_POLICY and CHECK_EXPIRATION to ON. When you apply a password policy to a user through the RDS console, the corresponding account's CHECK_POLICY and CHECK_EXPIRATION are set to ON, thereby using the Windows policy.
For example, if you set the minimum password age to 1 day and the maximum password age to 2 days through the RDS console, the corresponding Windows password policy will set Minimum password age to 1 day and Maximum password age to 2 days.
You can use the following SQL search statement to check the user's password policy status and related information, including whether policies are enabled, whether passwords have expired, and more:
-- Query user password policy status and related information
SELECT
name,
is_policy_checked, -- Whether password complexity policy is enabled
is_expiration_checked, -- Whether password expiration policy is enabled
LOGINPROPERTY(name, 'IsMustChange') AS IsMustChange, -- Whether password must be changed at next logon
LOGINPROPERTY(name, 'IsLocked') AS IsLocked, -- Whether account is locked
LOGINPROPERTY(name, 'LockoutTime') AS LockoutTime, -- Time when account was locked (NULL if not locked)
LOGINPROPERTY(name, 'PasswordLastSetTime') AS PasswordLastSetTime, -- Time when password was last modified
LOGINPROPERTY(name, 'IsExpired') AS IsExpired, -- Whether password has expired
LOGINPROPERTY(name, 'BadPasswordCount') AS BadPasswordCount, -- Number of incorrect password attempts
LOGINPROPERTY(name, 'BadPasswordTime') AS BadPasswordTime, -- Time of last incorrect password attempt
LOGINPROPERTY(name, 'HistoryLength') AS HistoryLength, -- Password history length (prevents reuse of old passwords)
modify_date -- Time when user account was last modified
FROM
sys.sql_logins; -- Query SQL Server login user information from system view
Related operations
To configure account password policies for an RDS SQL Server instance through API, see ModifyAccountSecurityPolicy - Configure SQL Server account password policies.
To modify account password policies for an RDS SQL Server database through API, see ModifyAccountCheckPolicy - Modify SQL Server account password policies.
To apply RDS SQL Server password policies when creating database accounts through API, see CreateAccount - Create a database account.