SQL Server – Sync User Passwords Across AlwaysOn Availability Group

availability-groupssql server 2014

I'm looking for a method to sync users across an AG when they initiate a password change on their own.

Historically (before setting up the AG), they could use the command line or, for app users, they had access to a web front-end that issued sp_password on their behalf; this was elegant because it was connecting as the user and the web server side didn't need elevated permissions set up on it. 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.

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).

I can find docs on synchronizing users the first time (had no trouble with this) but having a hard time finding anything on how a user might change their own password in an AG when they are only allowed to connect to the primary.

Any ideas? I'd still be grateful if it were glaringly obvious and embarrassing.

Edit: I meant to add: I could create a user with elevated permissions (probably 'alter any user') to change the user's password or to copy the password_hash across but I've been asked to try to limit that exposure of credentials on the web server side, if possible.

Thanks.

Best Answer

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:

  1. 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.
  2. 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.)
  3. 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.

Related Question