none
SQL Server 2012 Always ON Users Sync

    Question

  • Team,

    We have a development cluster which is configured on Windows Server 2012. Recently we had an issue where the cluster failover happened momentarily and the roles were interchanged wrt to AlwaysON. During this process users lost connectivity and upon further investigation, the users were orphaned on the other node and had to manually permission the users. Now that we have fixed the permission issue, please let me know whether this happens everytime whenever the cluster failover happens?

    Also would like to seek your help in any user sync script which I can incorporate for the issue to sync up the logins.

    SQL Version: 11.0.3000

    Any help on this is much appreciated.

    Regards,

    Sharath

    Wednesday, July 23, 2014 12:44 PM

Answers

  • Hello,

    During this process users lost connectivity and upon further investigation, the users were orphaned on the other node and had to manually permission the users. Now that we have fixed the permission issue, please let me know whether this happens everytime whenever the cluster failover happens?

    This can be two fold.

    The first issue is the logins, as the logins exist on a server level and not a database level. If the logins don't exist, end users can't connect - simple.

    The second issue is orphaned users. When using Windows Authentication, the SID (security identifier) for the login is pulled from AD and stays the same inside AD boundaries. When using SQL Authentication, the SID changes and is not the same even if the login name is the same. In the situation you had, the logins may have existed but their SIDs are different and thus the user->login mapping is not correct, hence the orphaned user.

    In order to have this not happen, the SQL Logins must be created on all other replicas using the SID= optional argument of CREATE LOGIN statement. This ensures that the SID matches across all instances and orphaned users will not be an issue.

    I'm not sure how you fixed the orphaned users for the databases when they failed over, but chances are if you just re-linked them you're going to have the same issue as you did before when it fails again. You'll need to remove the logins and re-create them with the current login's SID that is working. You can find the SIDs in sys.sql_logins.


    Sean Gallardy | Blog | Microsoft Certified Master

    Wednesday, July 23, 2014 1:43 PM
    Answerer