1

We have an AlwaysOn Availability Group setup (DB1+DB2) and also a web server (Web1) outside of the domain that we need to connect to the AG using Windows Authentication.

The following Windows Users/SQL logins have been set up:

Web1
Windows: Web1\User

DB1 [AG Primary]
Windows: DB1\User
SQL Login: DB1\User
SQL User: DB1\User (from DB1\User login)

DB2 [AG Secondary]
Windows: DB2\User
SQL Login: DB2\User

Because the secondary is read-only I cannot add the corresponding DB2 SQL User until we failover to it, and this is impractical due to the frequency we add users.

Is there a way on the DB1 primary to add DB2\User pointing to DB2\User login? Or a better way of doing this while still using Windows Authentication.

  • If you add the login to server 1, and migrate the login (with matching sid) to server 2 (this doesn't involve AGs, but the problem and solution are identical), and then associate the user in the AG database on server 1 to that login, that association should happen on server 2 as well. This is assuming that you are talking about "DB1" and "DB2" but they are really the same database. I don't think there's an elegant way to have different user mapping. – Aaron Bertrand Apr 26 '14 at 16:01
  • Does this work with Windows Authentication? The Windows users DB1\User and DB2\User have different SIDs. – Stuart Apr 26 '14 at 16:19
  • so your question talks about both SQL logins and Windows logins. Are you concerned about one, the other, both? What is the DB1\ DB2\ actually representing? Are these supposed to differentiate between Windows domains, servers, databases, something else? Can you clarify in your description whether you are talking about logins (server level) or users (database level) so that there is less ambiguity? – Aaron Bertrand Apr 26 '14 at 16:22
  • Sorry, the SQL logins were created using "FROM WINDOWS". DB1 and DB2 are servers and members of the availability group "MyAg" and in their own domain to which we cannot add users. For creating users I created the (windows) user locally on Web1, then locally on Db1 and Db2. Then on the primary created SQL login from windows and then the same on secondary. My problem is I can add a user on primary matched to its Windows authenticated login but cannot do same on secondary. Hope that makes more sense. – Stuart Apr 26 '14 at 16:36
  • You need to drop the login on the secondary (I don't believe its SID will match that of the login created on the primary, which will prevent the database user from being associated with it correctly). Then create the login on the secondary based on the script provided in the above link. – Aaron Bertrand Apr 26 '14 at 20:02

Your Answer

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

Browse other questions tagged or ask your own question.