How to rotate database credentials without downtime

Database password rotation causes outages when the password changes in one place while applications, pools and replicas still hold the old one. The fix is the same idea on every engine: make two credentials valid at the same time, move every consumer to the new one, prove the old one is idle, then retire it. How you get two valid credentials differs by engine: MySQL supports a secondary password on one account, while PostgreSQL and SQL Server need two login roles that you alternate between. This guide gives the exact statements from the vendor manuals, the queries that show who is still connected with which account, and a rollback for each step. It is for DBAs and platform engineers who own application service accounts on PostgreSQL, MySQL, SQL Server and managed cloud databases.

This is an independent guide from NHI Mgmt Group, based on the MySQL, PostgreSQL, Microsoft SQL Server, AWS and HashiCorp documentation as of November 2026. Confirm syntax against the manual for your engine version before running it in production.

Key takeaways

• Two valid credentials, then one. Every no-downtime method overlaps an old and a new credential; the only question is whether the engine gives you that overlap on one account or you build it with two.
• MySQL has it built in. ALTER USER ... RETAIN CURRENT PASSWORD keeps the old password as a secondary, and DISCARD OLD PASSWORD ends it once every client has moved.
• PostgreSQL and SQL Server need alternating logins. Two login roles with identical grants, usually through a shared group role, let you change one while the other carries traffic.
• Connections outlive passwords. A password change does not end sessions that are already authenticated, so prove the move with session views, not with the absence of errors.
• Automate the second time. Secrets Manager alternating users, Vault static roles and RDS managed master passwords run the same overlap on a schedule, but only if applications read the secret at connect time.

When you need this

• Scheduled rotation: your policy sets a maximum age for service account passwords, and the account belongs to an always-on application.
• People or supplier change: a DBA, developer or contractor who knew the password has left, or a vendor that held a connection string no longer needs it.
• Exposure without proven misuse: a connection string turned up in a ticket, a report or a log. Connection strings shared in customer documents were part of the Red Hat Consulting GitLab Breach 2025.
• Not for a confirmed compromise: if someone is using the password now, speed beats overlap. Change the password, terminate sessions for that account and follow the Database Breach Response Playbook.

Before you start

Choosing the overlap method

Use dual password wherever MySQL is the engine. Use alternating logins for PostgreSQL and SQL Server accounts that serve always-on applications. Accept single-user rotation only where clients retry cleanly and a few failed connection attempts are harmless. For greenfield services, skip rotation altogether with dynamic credentials.

Step-by-step

Step 1 is read-only. Steps 2 to 5 add or change credentials and are reversible. Step 6 is the only point at which the old credential stops working.

1. List who connects with the account today. Run the session view for your engine several times over a normal business cycle and record each distinct client address and application name.

   -- PostgreSQL
   SELECT usename, application_name, client_addr, backend_start, state
   FROM pg_stat_activity WHERE usename = 'app_user';
   
   -- MySQL (INFORMATION_SCHEMA.PROCESSLIST is deprecated; the manual recommends the Performance Schema processlist table)
   SELECT ID, USER, HOST, DB, COMMAND, TIME FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'appuser1';
   
   -- SQL Server
   SELECT session_id, login_name, host_name, program_name, login_time
   FROM sys.dm_exec_sessions WHERE login_name = 'app_user';

   backend_start in PostgreSQL is the time the client connected, which tells you how long pooled connections live. Long-lived sessions matter in step 5.
2. Create the second credential.

   MySQL: add a new primary, keep the old as secondary

   ALTER USER 'appuser1'@'host1.example.com'
     IDENTIFIED BY '<new-password>'
     RETAIN CURRENT PASSWORD;

   Run it on each server that is not a replica, then wait for the change to replicate. The manual describes this procedure as working "without downtime". Note the edge cases it lists: RETAIN CURRENT PASSWORD fails if the current primary password is empty, replaces any existing secondary, and cannot be combined with a change of authentication plugin, which discards the secondary password.

   PostgreSQL: build an alternating pair once

   Put the privileges on a group role that cannot log in, and make two login roles members of it, using GRANT for membership as the ALTER ROLE page directs. Create the second login role with a generated password, for example CREATE ROLE app_b LOGIN PASSWORD '<generated>' IN ROLE app_rw; where app_a is already a member of app_rw. On later rotations, simply change the idle login: ALTER ROLE app_b WITH PASSWORD '<new-password>';

   SQL Server: second login, or change the idle login

   SQL Server holds one password per login, so build the same pair: a second login and database user added to the same database roles. On later rotations change only the idle login: ALTER LOGIN app_b WITH PASSWORD = '<new-password>'; which requires ALTER ANY LOGIN.
3. Write the new credential to the secret store. Store the user name and password together as one secret version, so a consumer never reads a new password with an old user name. In AWS Secrets Manager the alternating strategy stores both in one secret and labels the live version AWSCURRENT; with Vault static roles the client reads the current password from the role.
4. Move consumers. Reload or redeploy each consumer so it reads the new secret. Applications that read credentials only at start-up keep using the old value until restarted. Update scheduled jobs, reporting tools, ETL connections and any linked server or database link that stores the password; see Database Links and Linked Servers.
5. Prove the old credential is idle. Re-run the step 1 queries. For alternating logins, the old login should show no sessions. For MySQL dual password, the session view cannot tell you which password a client used, so rely on the consumer list and restart evidence, then use the authentication log if your audit plugin records failures after the next step. Remember that a password change does not end existing sessions: SQL Server documents that a connection stays authenticated as long as it is maintained, and on Azure SQL Database a password reset does not reauthenticate an existing connection even when pooling resets it. Wait for pools to recycle, or restart them, before you treat a quiet view as proof.
6. Retire the old credential.

   -- MySQL: run on each non-replica, then let it replicate
   ALTER USER 'appuser1'@'host1.example.com' DISCARD OLD PASSWORD;
   
   -- PostgreSQL: block the idle login without dropping it
   ALTER ROLE app_a NOLOGIN;
   
   -- SQL Server: disable the idle login
   ALTER LOGIN app_a DISABLE;

   Disabling is reversible; dropping is not. SQL Server notes that disabling a login does not affect logins already connected, so check sessions again and end stragglers deliberately. On the next rotation, set a new password on the disabled login, re-enable it and switch back.

Automating the overlap

Two warnings from the vendor documentation. Vault says not to manage the same root credentials you gave Vault in config/ with a static role, because rotation would then break every user Vault manages; rotate the root with vault write -force database/rotate-root/<name> instead. And AWS recommends single-user rotation, not alternating users, when cloned users would not carry the same permissions as the original.

Verify it worked

• Sessions: the session view shows the expected applications connected with the new login (alternating) or freshly started connections after the change (dual password).
• No authentication failures: the database error log and audit records show no failed logins for the account during the retirement window.
• Secret state: the secret store's current version matches the credential in use; in Secrets Manager AWSCURRENT is on the version you wrote.
• Replicas: on MySQL, the discard has replicated; check that replicas accept the new password and reject the old one.
• Record: rotation date, old and new login names and consumer list are written to the account's inventory entry.

Rollback and common errors

Detection and measurement

• Failed logins by account: alert on authentication failures for service accounts in the hour after any rotation, the fastest signal of a missed consumer. See the Database Audit Log Detection Guide.
• Use of a retired login: any session from a login marked idle is either a missed consumer or misuse; both need a ticket.
• Unexpected client addresses: a known account connecting from a new client_addr or host deserves review whether or not a rotation is in progress.

Metrics that show the programme works:

1. Percentage of application database accounts with a password younger than policy age.
2. Percentage of accounts rotated by automation rather than by hand.
3. Rotations that caused an incident, per quarter (target zero).
4. Median hours between creating the new credential and retiring the old one.
5. Number of accounts shared by more than one application (each one blocks independent rotation).

Common failure patterns

• Changing the password and calling it rotated: sessions authenticated with the old password keep running, so a leaked password used for a long-lived session is not cut off. Terminate sessions when the trigger is exposure.
• One account shared by many applications: every rotation becomes a coordinated change across teams, so it gets postponed. Split accounts per application.
• Grants drift between alternating logins: someone grants a new table to app_a directly, and the first switch to app_b breaks a feature.
• Rotating the admin account that the rotation tool uses: the tool locks itself out. Keep the rotation identity separate and rotate it through its own path.
• Rotation without removal: old logins left enabled "just in case" double the attack surface. The Incomplete rotation breaches guide shows how often a half-finished rotation is the gap attackers use.

Hardening afterwards

• Remove the password where you can: cloud databases support identity-based authentication, for example Microsoft Entra authentication for Azure SQL and IAM database authentication on Amazon RDS. The Database Credential and Access Security Guide compares the options.
• Least privilege per account: application logins should not own schemas or hold administrative roles.
• Owner and purpose: every service account has a named owner, consumer list and rotation method recorded in the inventory.
• Scale out: for many accounts at once, sequence and stage the work as described in Credential Rotation at Scale.

Framework mapping

Practitioner checklist

• Confirm the trigger is routine or precautionary; for active misuse, use the breach playbook.
• Build the consumer list from session views over a full business cycle.
• Pick the overlap method for the engine: dual password on MySQL, alternating logins elsewhere.
• Generate the password in the secret manager and store user name and password as one version.
• Reload every consumer and recycle connection pools.
• Prove the old credential is idle with session views and the error log.
• Retire by disabling or discarding, never by dropping first.
• Automate the next rotation and record owner, method and date.

Standards and references

• MySQL: Password Management (Dual Password Support)
• PostgreSQL: ALTER ROLE
• Microsoft Learn: ALTER LOGIN (Transact-SQL)
• AWS: Lambda function rotation strategies (Secrets Manager)
• AWS: Password management with Amazon RDS and Secrets Manager
• HashiCorp: Vault database secrets engine
• PostgreSQL: The Cumulative Statistics System (pg_stat_activity)
• Microsoft Learn: Rotation for resources with two sets of credentials

Related NHI Mgmt Group resources: Database Credential and Access Security Guide · How to rotate an AWS IAM access key without downtime · Dynamic Secrets and Ephemeral Credentials Guide · Credential Rotation at Scale