1

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.

  • 1
    Is there an option that you could setup a process that runs every X minutes, looks for password changes and then syncs that password over? one that runs on the backend, rather than the web server side. – Nic Oct 21 '16 at 21:56
  • Yes, that sounds do-able. I'll be working on the solution soon (Monday disasters take priority) – Joe Creighton Oct 24 '16 at 19:39
3

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.

  • Thank you. All three of these seem very reasonable and I'll update and confirm the answer as soon as I can get things working. My notes: 1) connecting to the replicas explicitly means elevated permissions on the CGI side to issue the update; however, the hash could be inserted into a table in our maintenance db, outside of the AG, and that could trigger the update. 2) modify_date might change for other reasons (defdb) so I'd suggest loginproperty([name], 'PasswordLastSetTime') as a more accurate alternative. 3) this also keeps things nicely synced, a state we were in before AGs arrived – Joe Creighton Oct 24 '16 at 19:35
  • You don't have to elevate permissions, module signing can work just fine: technet.microsoft.com/en-us/library/ms345102(v=sql.105).aspx – Sean Gallardy Oct 24 '16 at 21:24
  • 1) Better: buffering the userid or sid is enough on the web server side since I can pull the matching hash from from the SQL server side. New Data Point: attempting ALTER LOGIN fails because CHECK_POLICY is enabled. This is easy to disable but the Remarks section of the docs -- msdn.microsoft.com/en-us/library/ms189828.aspx -- state I will lose the password history. This is probably unavoidable. – Joe Creighton Oct 27 '16 at 18:33
1

I just recently solved the same issue at a client's, and I blogged about the solution.

The short version of it is: I've set up a stored procedure that runs on each of the replicas, connects through a linked server to the primary replica to retrieve login SIDs and password hashes, and applies those to the secondary replicas where neccessary.

The SID (the unique identifier of the account) is what connects the login (the server principal) to the user (the database principal), so if you're creating new logins on the secondary replicas, you'll need to bring your own SID with you, so the login and the user matches.

The SID can be found in sys.server_principals, the hashed passwords in sys.sql_logins. Those two tables are connected by the sid column.

To address your security concerns: The client application itself never has permission to do this. Rather, I'm running a SQL Server Agent job that operates on a very strict set of permissions. The synchronization doesn't happen immediately, because it's scheduled, so you'll either have to set the job to run relatively frequently or provide some type of DDL trigger like @SeanGallardy suggests.

Disclaimer: don't use T-SQL code from strangers on the Internet without testing it first. I'm providing it on a best-effort basis.

  • Thanks for the reference. Our initial setup of logins went well and I've got some automation in place for creating new logins with matching SIDs. However, I know we're behind on other issues (roles, memberships, etc.) and I had been planning to explore that. I'll definitely be looking at this as a possible solution to monitoring our synchronization. – Joe Creighton Oct 24 '16 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.