Rotate the HA SQL Admin Password (haadmin)

Rotate the password of haadmin, the Azure SQL server admin login on ha-dev-azsqldb (staging) and ha-prod1-azsqldb (production). Every HealthAlign tenant connects to its database as haadmin today.

Use this runbook when:

• The password has been exposed (logs, a served config file, a leaked env file)
• Someone with access to it leaves
• A scheduled rotation is due

Not covered: ha-stage-azsqldb has a different admin (hastageadmin), and no tenant env file points at it.

Choose an option

A server admin login has exactly one password, so the old and new values can’t both work at once. Running apps only pick up a new password when they’re redeployed. That leaves two options:

Use A when the rotation can’t wait for code changes and an outage window is acceptable. Use B otherwise; it only has to be done once.

What uses the password

There is one password for both servers. The operator tools read a single common copy of the secret and use it against both ha-dev-azsqldb and ha-prod1-azsqldb, so the two servers must always be rotated to the same value.

The value lives in three copies of the secret haadmin_azure_sql_user_password:

In healthAlignPMS, the .webapp (including IdentityServer’s), .interfacetaskservice, .interfacetaskwebtest, .interfacetaskworker, .webappnext, .webappcore, .beta10 and .databasedeploy env files all set username=haadmin. The password reaches the apps in two ways:

• Windows apps (webapp, interface task service): the release pipeline writes the vault values into the host’s env files at deploy time. A container restart reuses those files, so only a redeploy changes the password they use.
• IdentityServer and other Linux containers that fetch their vault themselves: they read the vault on every container start, so any restart picks up a new vault value.

Terraform only reads these servers (data "azurerm_mssql_server" in infra/ha-infra/business_unit_1/*/databases.tf), so it never sets or reverts the admin password.

Prerequisites

• Azure: rights to update the SQL server (Microsoft.Sql/servers/write, e.g. SQL Server Contributor) on HA-DEV-SQL-RG/ha-dev-azsqldb and HA-PROD1-SQL-RG/ha-prod1-azsqldb
• GCP: add and disable versions of haadmin_azure_sql_user_password in all three projects above, and access to every {app}-vault in the vault-secrets projects (what ha_vault_migrate needs)
• ADO: permission to run ALL-Release-Staging and ALL-Release-Production, used through ha_ado
• A connectivity check from your machine: sqlcmd plus a firewall rule or VPN that reaches both servers
• Option A only: an outage window announced for each environment, following Execute a Planned Outage
• A tooling freeze while haadmin changes: no launchbot runs, launch-tenant workflow runs or mssql tool runs. They read the common copy, which changes before the servers do.

Before you start

1. Confirm haadmin is still the server admin on both servers:

   az sql server show -g HA-DEV-SQL-RG -n ha-dev-azsqldb --query administratorLogin -o tsv

   az sql server show -g HA-PROD1-SQL-RG -n ha-prod1-azsqldb --query administratorLogin -o tsv

   Both should print haadmin.

2. Confirm the three copies hold the same value, comparing hashes so the password never reaches the terminal:

   for p in prj-c-secrets-a7cc prj-bu1-n-homealign-infra-075c prj-bu1-p-homealign-infra-2b1b; do
     printf '%-32s ' "$p"
     gcloud secrets versions access latest --secret=haadmin_azure_sql_user_password --project="$p" | sha256sum
   done

   All three hashes must match. If they don’t, stop: work out which value each server actually accepts before rotating, or the old copies will hide a mismatch.

3. Note each copy’s current version number, for rollback:

   for p in prj-c-secrets-a7cc prj-bu1-n-homealign-infra-075c prj-bu1-p-homealign-infra-2b1b; do
     echo "$p: $(gcloud secrets versions list haadmin_azure_sql_user_password --project="$p" --filter=state=ENABLED --format='value(name)' | tr '\n' ' ')"
   done

Generating a password

Both options generate passwords the same way. Keep the value in a shell variable, don’t echo it, and don’t write it to a file in the repo:

NEW_PW=$(openssl rand -base64 64 | tr -dc 'A-Za-z0-9' | head -c 40)
[[ ${#NEW_PW} -eq 40 && $NEW_PW =~ [A-Z] && $NEW_PW =~ [a-z] && $NEW_PW =~ [0-9] ]] && echo ok || echo "regenerate"

Letters and digits only. The value is substituted into ADO.NET connection strings and $(password) tokens, where ;, ', ", =, $, {, } or spaces break the substitution or the connection string. Forty alphanumeric characters meets Azure SQL’s complexity rules (upper, lower and digit; 8–128 characters; must not contain the login name).

When storing a password in Secret Manager, always pipe it with printf '%s', not echo. ha_vault_migrate copies the secret’s bytes verbatim, so a trailing newline would end up inside every connection string.

Option A — rotate in place

Staging goes first and acts as the canary. Production keeps working on the old password until its own reset in step A6.

A1. Generate the new password

See Generating a password.

A2. Add the new value to all three secret copies

failed=0
for p in prj-c-secrets-a7cc prj-bu1-n-homealign-infra-075c prj-bu1-p-homealign-infra-2b1b; do
  if printf '%s' "$NEW_PW" | gcloud secrets versions add haadmin_azure_sql_user_password --project="$p" --data-file=-; then
    echo "ok $p"
  else
    echo "FAILED $p" >&2; failed=1
  fi
done
if (( failed )); then
  echo "STOP: every copy must have the new value before A3." >&2
  false
else
  echo "All 3 copies updated."
fi

All three copies must print ok. A copy left on the old value would fan the old password out, or hand the tools a stale one, after the servers change. Re-run the loop until it reports all three. A copy that gets the value twice just gains an extra version, which is harmless.

Leave the old versions enabled until step A8; they are the rollback.

A3. Fan the new value out to the staging vaults

Only staging here. Production’s vaults are switched in step A6, just before its reset.

Dry-run first and read the Keys to update line for every app. It should list only password. Any other key means that value has drifted and would change too, so find out why before continuing.

./zig/zig build scripts -- ha_vault_migrate --all --env n --dry-run

./zig/zig build scripts -- ha_vault_migrate --all --env n

Don’t pass --disable-old-versions yet. The Windows apps keep using the old password until they’re redeployed, and the server still accepts it.

Until the server is reset, IdentityServer and any other container that fetches its vault at start will read the new password on its next restart and fail to connect. Go straight on to the reset, and don’t restart those services in between.

A4. Check the legacy Swarm secret

create_docker_secrets.sh created a Swarm secret named password on the HA Linux managers. Swarm secrets can’t be changed in place, and re-running the script skips secrets that already exist. On each HA Linux manager, list the services that mount it:

for s in $(docker service ls -q); do
  docker service inspect "$s" --format '{{.Spec.Name}}: {{range .Spec.TaskTemplate.ContainerSpec.Secrets}}{{.SecretName}} {{end}}'
done | grep -w password

• No output: nothing uses it. Remove it after step A8, since it still holds the old password: docker secret rm password.

• Services listed: after that environment’s server reset, create a new secret and swap it into each listed service. Replace <service> with each name from the output:

  printf '%s' "$NEW_PW" | docker secret create password_v2 -

  docker service update --secret-rm password --secret-add source=password_v2,target=password <service>

A5. Reset the staging server and redeploy staging

Start of the staging outage. Existing pooled connections survive a password reset, so errors can show up minutes later rather than immediately. Don’t read “nothing broke yet” as success.

az sql server update -g HA-DEV-SQL-RG -n ha-dev-azsqldb --admin-password "$NEW_PW"

Portal alternative: SQL server → Overview → Reset password. Check that the new password works:

sqlcmd -S ha-dev-azsqldb.database.windows.net -d master -U haadmin -P "$NEW_PW" -Q "SELECT 1" -b

Redeploy straight away. The outage for each tenant lasts until its redeploy finishes:

./zig/zig build scripts -- ha_ado deploy --all --env n --skip-database

./zig/zig build scripts -- ha_ado deploy --app synctenant --env n

The full orchestration covers IdentityServer and every tenant’s webapp and interface task service. --skip-database skips the database deploy, which only reads the password when it runs, but it also skips SyncTenant, hence the separate SyncTenant deploy.

Verify before moving on:

• https://<tenant>.myhaapp.com/api/healthcheck returns 200 for a few tenants, including core
• Logging in at https://core.myhaapp.com works, which exercises IdentityServer
• Interface task service logs in Papertrail show no Login failed for user 'haadmin'

A6. Switch the production vaults and reset the production server

Switch the production vaults, dry-run first as in step A3:

./zig/zig build scripts -- ha_vault_migrate --all --env p --dry-run

./zig/zig build scripts -- ha_vault_migrate --all --env p

Then reset straight away. Start of the production outage.

az sql server update -g HA-PROD1-SQL-RG -n ha-prod1-azsqldb --admin-password "$NEW_PW"

sqlcmd -S ha-prod1-azsqldb.database.windows.net -d master -U haadmin -P "$NEW_PW" -Q "SELECT 1" -b

A7. Redeploy production

./zig/zig build scripts -- ha_ado deploy --all --env p --skip-database

./zig/zig build scripts -- ha_ado deploy --app synctenant --env p

Production deploys need manual approval in Azure DevOps. Verify the same way as staging, against https://<tenant>.myhomealign.com.

A8. Retire the old password

Once both environments are healthy:

1. Disable the old versions of all three secret copies, using the version numbers you noted before starting:

   gcloud secrets versions disable <old-version> --secret=haadmin_azure_sql_user_password --project=<project>

2. Disable the old vault versions:

   ./zig/zig build scripts -- ha_vault_migrate --all --env n --disable-old-versions

   ./zig/zig build scripts -- ha_vault_migrate --all --env p --disable-old-versions

3. Remove the stale Swarm secret from step A4 where applicable.

4. Clear the variable: unset NEW_PW.

5. Lift the tooling freeze, then check mssql / launchbot against each server with a read-only command.

Rollback (Option A)

Rolling back means making the old value latest again. Re-add it as a new version of each copy, rather than disabling the new one. Version numbers differ per project, so use the ones you noted for each. If step A8 already disabled the old version, re-enable it first, or it can’t be read:

gcloud secrets versions enable <old-version> --secret=haadmin_azure_sql_user_password --project=<project>

gcloud secrets versions access <old-version> --secret=haadmin_azure_sql_user_password --project=<project> \
  | gcloud secrets versions add haadmin_azure_sql_user_password --project=<project> --data-file=-

Then re-run the vault fan-out for each environment already switched: ha_vault_migrate --all --env n (step A3) and, if you reached step A6, --env p.

• Before step A5 (neither server reset), that’s all. The Windows apps were never affected.

• After a server reset, also reset that server back to the old value and redeploy that environment:

  az sql server update -g <rg> -n <server> --admin-password "$(gcloud secrets versions access latest --secret=haadmin_azure_sql_user_password --project=prj-c-secrets-a7cc)"

If you’re rotating because of an exposure, rolling back re-exposes the leaked password, so fix forward if you can.

Option B — move apps to haadmin2, then rotate

The apps move to a second login, haadmin2, while haadmin still works. Because the old and new login are both valid throughout, no step causes an outage. Once nothing but the operator tools uses haadmin, it can be rotated without touching the fleet.

haadmin2 gets a different password per environment. The apps read the per-environment secret copies, so a staging exposure no longer reveals production’s app password.

haadmin2 has to be a server login mapped into every tenant database as db_owner. The mssql tool can’t create it: its users are contained users in a single database, with at most db_datareader, db_datawriter and EXECUTE. That isn’t enough for the database deploy (DDL) or for SyncTenant, which works across tenant databases.

B1. Create haadmin2 on each server

Do this per server, with a separate password for each. SERVER is ha-dev-azsqldb for staging and ha-prod1-azsqldb for production; ADMIN_PW is the current haadmin password.

Generate NEW_PW (see Generating a password), then create the login in master:

sqlcmd -S "$SERVER.database.windows.net" -d master -U haadmin -P "$ADMIN_PW" -b \
  -v PW="$NEW_PW" -Q "CREATE LOGIN [haadmin2] WITH PASSWORD = '\$(PW)'"

Map it into every user database as db_owner. Azure SQL can’t switch databases inside one connection, so connect to each in turn:

failed=0
# An empty or failed listing would otherwise skip the loop and report success.
if ! databases=$(sqlcmd -S "$SERVER.database.windows.net" -d master -U haadmin -P "$ADMIN_PW" -h -1 -W -b \
    -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE database_id > 4") || [[ -z $databases ]]; then
  echo "FAILED to list databases" >&2; failed=1
fi
for db in $databases; do
  if sqlcmd -S "$SERVER.database.windows.net" -d "$db" -U haadmin -P "$ADMIN_PW" -b \
      -Q "IF USER_ID('haadmin2') IS NULL CREATE USER [haadmin2] FROM LOGIN [haadmin2]; ALTER ROLE db_owner ADD MEMBER [haadmin2];"; then
    echo "ok $db"
  else
    echo "FAILED $db" >&2; failed=1
  fi
done
if (( failed )); then
  echo "STOP: map every database before the cutover." >&2
  false  # non-zero status without exit, which would close an interactive shell
else
  echo "All $(wc -w <<<"$databases") databases mapped."
fi

Every database must print ok. A tenant whose database wasn’t mapped can’t connect once it switches to haadmin2, so don’t continue to B2 until a re-run of the loop shows no failures.

Check that it can log in:

sqlcmd -S "$SERVER.database.windows.net" -d hACore -U haadmin2 -P "$NEW_PW" -Q "SELECT DB_NAME(), IS_ROLEMEMBER('db_owner')" -b

haadmin2 has no server-level roles. If staging shows an app needing more than db_owner (for example VIEW SERVER STATE, or dbmanager to create databases), grant exactly that before moving production.

Store the password in the matching ha-infra project, as a new secret haadmin2_azure_sql_user_password (prj-bu1-n-homealign-infra-075c for staging, prj-bu1-p-homealign-infra-2b1b for production). Replace <project> with the one for this server:

gcloud secrets create haadmin2_azure_sql_user_password --project=<project> --replication-policy=automatic

printf '%s' "$NEW_PW" | gcloud secrets versions add haadmin2_azure_sql_user_password --project=<project> --data-file=-

Grant readers the same access they have on haadmin_azure_sql_user_password in that project.

New tenant databases created after this step won’t have the haadmin2 user. Until the launch tooling adds it, repeat the mapping loop for each new database.

B2. Point the vault fan-out at haadmin2

In infrahive, change the password entry in SECRETS in src/scripts/ha_vault_migrate/main.py to read haadmin2_azure_sql_user_password. Change create_docker_secrets.sh the same way. Merge that PR, but don’t run ha_vault_migrate yet: step B4 runs it per environment.

B3. Switch the username in healthAlignPMS

Open a healthAlignPMS PR that changes username=haadmin to username=haadmin2 in the env files. Use one PR per environment, staging (.envs/*-staging) first.

List the files it has to cover:

git grep -l -w "username=haadmin" -- '.envs/*-staging/*'

The username (from the env files) and the password (from the vault) must change in the same deploy.

• If the vault switches first, a deploy of the old env files pairs haadmin with the haadmin2 password.
• If the env files ship first, a deploy pairs haadmin2 with the haadmin password.

Either way that tenant can’t connect. So for each environment:

• Freeze its deploys from the vault switch in B4 until its redeploy finishes.
• Don’t release the production PR until you’re about to do B4 for production.

B4. Cut over each environment

Staging first, then production (use --env p and the production PR for the second pass):

1. Merge the environment’s .envs PR (for production, release it) with deploys frozen.

2. Switch the vaults to the haadmin2 password. The dry-run’s Keys to update should list only password:

   ./zig/zig build scripts -- ha_vault_migrate --all --env n --dry-run

   ./zig/zig build scripts -- ha_vault_migrate --all --env n

3. Redeploy IdentityServer first. It reads the vault on every start, so an unplanned restart before its redeploy would pair the new password with the old username. Then redeploy everything:

   ./zig/zig build scripts -- ha_ado deploy --app identityserver --env n

   ./zig/zig build scripts -- ha_ado deploy --all --env n --skip-database

   ./zig/zig build scripts -- ha_ado deploy --app synctenant --env n

   Tenants not yet redeployed keep running as haadmin, which still works, so there’s no outage while the fleet rolls.

4. Verify as in step A5, and check that no app still logs in as haadmin. In Azure SQL, sys.dm_exec_sessions only shows sessions for the database you’re connected to, so check each one as haadmin. Only operator tools should still show haadmin:

   failed=0 checked=0
   if ! databases=$(sqlcmd -S "$SERVER.database.windows.net" -d master -U haadmin -P "$ADMIN_PW" -h -1 -W -b \
       -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE database_id > 4") || [[ -z $databases ]]; then
     echo "FAILED to list databases" >&2; failed=1
   fi
   for db in $databases; do
     if sqlcmd -S "$SERVER.database.windows.net" -d "$db" -U haadmin -P "$ADMIN_PW" -h -1 -W -b \
         -Q "SET NOCOUNT ON; SELECT DB_NAME(), login_name, host_name, program_name, COUNT(*) FROM sys.dm_exec_sessions WHERE login_name = 'haadmin' AND session_id <> @@SPID GROUP BY login_name, host_name, program_name"; then
       checked=$((checked + 1))
     else
       echo "FAILED to check $db" >&2; failed=1
     fi
   done
   if (( failed )); then
     echo "STOP: checked $checked of $(wc -w <<<"$databases") databases; an empty result proves nothing until all are checked." >&2
     false
   else
     echo "Checked all $checked databases."
   fi

   Empty session output only means “nothing still uses haadmin” when the snippet ends with Checked all N databases. Don’t continue to B5 on a STOP.

B5. Rotate haadmin (tools only)

With the fleet on haadmin2, only the common copy in prj-c-secrets-a7cc still matters:

1. Generate a new password (see Generating a password).
2. Add it to the common copy.
3. Reset both servers with az sql server update --admin-password as in steps A5 and A6.

There’s no vault fan-out and no redeploy. Keep the tooling freeze until both servers are reset. Then disable the old versions of all three haadmin_azure_sql_user_password copies. The ha-infra copies are no longer read once B2 is merged, so delete them once nothing references them.

Rollback (Option B)

Until the old haadmin password is retired in B5, every step is reversible without an outage:

• Vault: revert the B2 change and re-run ha_vault_migrate.
• Env files: revert the B3 PR.
• Apply both: redeploy.

Both logins keep working throughout.

Rotating haadmin2 later

haadmin2 is a single login, so rotating it in place has the same outage as Option A. To keep rotations outage-free, alternate between two app logins instead: create the next login (e.g. haadmin3) with B1, cut over with B3 and B4, then drop the old one.

Follow-ups

This runbook rotates the password; it doesn’t remove the underlying risk:

• Separate the operator tools’ password per environment. The mssql tool, launchbot and the launch-tenant workflow still share one haadmin password across staging and production. Scoped users reduce what depends on it: infrahive #1495 creates launchbot_client_creator, limited to dbo.Clients, for create_client_records.sh.
• Scope the app login per tenant. haadmin2 is db_owner in every tenant database. A login per tenant would limit an exposure to that tenant’s database.
• Add haadmin2 in the launch tooling, so new tenant databases get the user without the manual loop in B1.