SQL Always on, user permissions dropping after Failover
-
Hello there!
I'm having an issue with Always On, I'm all set up with two replicas in the AG. When testing failovers with our Cognos setup, the app is no longer able to connect to the DB until I drop the user and recreate it using the proper user mapping (it defaults to public only). If I failover to the other SQL server, it will work again. If I drop the user and recreate it with the proper user mapping, it will work when the second server is set as primary, but then when I failover again, the first SQL server won't connect without again dropping the user and recreating with the proper user mappings. Hopefully this is clear as mud! I'm new to these forums, so please let me know if there are any further details I can provide to help.
-
I am assuming this is a SQL Server Login you are having troubles with?
This issue is known as the orphaned user issue, plenty to read up on the net about it.
But the TLDR is, that the SID is mismatched, so you need to create the account on both servers with the same SID.
Take a look at "sp_help_revlogin" and "sp_hexadecimal", https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances
As this is the process to undergo to ensure when failovers happen SQL accounts continue to work without intervention
-
This was exactly what I needed to find my solution. I ended up finding the following article which made creating a login user with the matched SID very simple.
Thank you!
-
use powershell dbatools ( free ) copy_dbalogin to copy accounts (including sid) from one AG member to another and avoid these issues.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply