Sync User Passwords Across SQL Server AlwaysOn Availability Group
With a user's default database now in an AG, the password change succeeds on the primary but fails on the secondary because the database is inaccessible.


Passwords are stored in the master database, there is no need to connect to that database on the secondary. Unless the database is a contained database, server logins are also stored in the master database. Contained databases take care of themselves as contained users can be "logins" and are "contained" within the database, thus automatically included.


  I've tried changing the user's default database to master temporarily (fails to add it back while in an AG), permanently (some apps expect the default to be their own database), and modifying the CGI to use master as the database when connecting (works on primary, fails on secondary).


If you're using must_change or password policies you'll have an event raised or a message returned about this and can do it programmatically.

Ex: https://technet.microsoft.com/en-us/library/ms131024(v=sql.110).aspx


  Any ideas?


A few come to mind:


Buffer the password (hashed) from the initial password change request. Check to see if login is mapped to any databases that currently reside in any availability group. Get the distinct replica names for each availability group. Connect to each replica and issue the password update.
Run a job on all replicas and check for logins that have been updated recently. This information you can get from sys.server_principals (modify_date) and can use sp_help_revlogin to get the command needed up update the login. This could be run every so often (10 minutes, 15, etc.)
Use event notifications for the ALTER_LOGIN DDL event and have an activated stored procedure that did #2 (the one above) but on demand at the same time rather than on a schedule.


There are other combinations of this using server side traces and/or extended events but it all boils down to about the same process.