Forum Discussion

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

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

Microsoft

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
Copper Contributor
Nov 23, 2020
bake13 Thank you, this has been helpful.