While doing a planned Availability Group failover, the application stopped talking to the database. After checking the SQL Server log, we found that all the SQL Logins were failing with an “incorrect password” error. The logins were on the server, the users were in the databases, and the passwords were even right, so what was wrong? It all comes down to SID’s (Security Identifiers).
Read on for the cause and the solution. I’d also recommend
Sync-DbaAvailabilityGroup as a good dbatools cmdlet to use.