none
SQL Logins of DB is not getting sync

    Question

  • Hi,

    I am having two SQL 2012 SP1 configured in Availability Group. I have installed one application using listener. When I am failing over DB to other node, I am not able to login to the DB. When I checked DB users are exist on the other node but they do not have login mapped i.e. they are set to SQL user without login. Need help to fix this.

    Thanks..

    Thursday, January 16, 2014 11:39 AM

Answers

All replies

  • You need to create login in the second server and map to the user as well.
    Thursday, January 16, 2014 11:49 AM
  • I am not sure about the procedure. These users are created by the application (SQL Login), application itself maintains credentials.
    Thursday, January 16, 2014 11:58 AM
  • I am not sure about the procedure. These users are created by the application (SQL Login), application itself maintains credentials.

    What do you mean by user here? DB User? Application creates DB User? 

    Confused a bit, Can you show us the error message you are getting....

    Thursday, January 16, 2014 12:02 PM
  • Here is the screenshot for your reference. One with Login type as SQL User with Login and one with SQL User without Login

    Thursday, January 16, 2014 12:15 PM
    • Marked as answer by Vikram Athare Thursday, January 16, 2014 1:55 PM
    Thursday, January 16, 2014 1:22 PM
  • The kb article that you referenced discusses what to do with moving a database.  It does not discuss what to do when using "AlwaysOn High Availability", in which case the database is replicated.  The problem is that on the primary system when you create a user at the database level...specifically when the user is a "SQL user with login", it gets replicated to the secondary system as "SQL user without login".  This causes some problems after failing over as any application that is connecting is trying to login and it can't because it is expecting the user type to be "SQL user with login".  To temporarily fix it, you can delete this account, then recreate it again as a "SQL user with login".  Though the problem will be back the next time another failover occurs.  One might be able to get around this by creating and running a job/script every minute that checks for these conditions to see if system has failed over and then delete the account and recreate it, but that is not ideal.  We are currently seeing this problem with SQL Server 2012 SP1 CU9.


    • Edited by Jay Maier Wednesday, April 09, 2014 2:30 PM
    Wednesday, April 09, 2014 2:21 PM