Forum Discussion

The_Boz's avatar
The_Boz
Copper Contributor
Nov 13, 2020

2016 SQL Standard - Always on Availability Group questions

Hello,   Thank you in advance for any assistance. I setup two 2019 servers with SQL 2016 Standard, with the hopes of setting them up with Always on Availability. I am at the point were the database...
The_Boz's avatar
The_Boz
Copper Contributor
Nov 13, 2020

With further investigations, it looked like the SQL Accounts were getting orphaned after failover and was failing at login. After using:

exec sp_change_users_login 'auto_fix'

 

I was able to get access to the database. As a test, I failover again and the accounts got orphaned again. Trying to determine why the accounts get orphaned after a failover now.

  • bake13's avatar
    bake13
    Icon for Microsoft rankMicrosoft
    Nov 19, 2020

    Hi The_Boz  -- This is most likely occurring because the SQL logins were created separately on each Availability Group node.  When a SQL login is created, a unique SID is also created.

     

    So while the SQL login names may be identical on each Availability Group node, the SIDs likely are not which is what is causing the orphaning.

     

    You can use the process outlined at this link to transfer logins between the Availability Group nodes while maintaining the same SID.  Take care.

    • The_Boz's avatar
      The_Boz
      Copper Contributor
      Nov 23, 2020

      bake13 Thank you, this has been helpful.

Resources