How to sync the password of existing logins between Primary and Read Replica instance for RDS for SQL Server

Question

Hi team

IHAC who is using RDS SQL Server with multi-AZ along with one RDS Read Replica in the same region to offload read traffic. We are using AWS Secret Manager to store the credential for application logins. We have also enabled the password rotation.

My question is, when we rotate the password of existing application logins, lets say appuser01 and appuser02. The new password works well in primary instance but whey we try to access to read replica the new password does not work.  I guess the reason is because the [master] database is different between Primary and read replica and logins are stored in [master] db.

I have gone through https://repost.aws/knowledge-center/rds-sql-server-sync-logins-read-replica post which talks about sync the new logins.

Please advise how to sync the password of existing logins  between Primary and Read Replica? Thank you.

Answer

As described in the documentation, this is not a supported scenario by Secrets Manager password rotation: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-secrets-manager.html

**Limitations for Secrets Manager integration with Amazon RDS**
Managing master user passwords with Secrets Manager isn't supported for the following features:
*     For all DB engines except for RDS for SQL Server, creating a read replica when the source DB or DB cluster manages credentials with Secrets Manager
*     Amazon RDS Blue/Green Deployments
*     Amazon RDS Custom
*     Oracle Data Guard switchover
*     RDS for Oracle with CDB

So, the best option I can imagine, is to modify the lambda function for the password rotation, to rotate it on both DB.

Best,

How to sync the password of existing logins between Primary and Read Replica instance for RDS for SQL Server

Relevant content