The requirement is to rotate database credentials at least weekly . The AWS service that is purpose-built for storing and automatically rotating secrets is AWS Secrets Manager . Secrets Manager supports rotation schedules (including daily, weekly, or custom intervals) and integrates with supported databases through rotation Lambda functions. With rotation enabled, Secrets Manager can periodically generate a new password, update the secret value, and apply the new credential to the database user, while allowing applications to retrieve the current credential securely at runtime.

Option C matches this best practice: create a database user, store the username/password in a Secrets Manager secret, and enable rotation (daily rotation more than satisfies a weekly requirement). Applications on EC2 can retrieve the secret at startup or on a refresh interval using the AWS SDK, and can cache it according to best practices to reduce API calls, while still supporting rotation.

Option A (Parameter Store secure string) encrypts parameters, but Parameter Store does not provide the same managed secret rotation workflow for database credentials; rotating the KMS key is not the same as rotating the database password. Option B is not applicable as stated for RDS for Microsoft SQL Server: IAM database authentication is supported for specific engines (commonly MySQL and PostgreSQL) and is not the standard mechanism for SQL Server password rotation. Option D is insecure and operationally fragile: embedding long-lived credentials in user data and environment variables makes rotation difficult and increases exposure risk.

Therefore, the most secure and compliant configuration is C : store DB credentials in AWS Secrets Manager and enable automated rotation on an interval that meets or exceeds the weekly rotation requirement.