0

Mine is Always ON availability environment. I have 3 SQL 2014 servers(Primary and 2 replicas). Since one year all the logins working FINE in the servers. Some how suddenly, I am getting errors from the Application server stating that the PIDS were not able to access the Database. The PIDS are successfully logging in the server but not able to access database. I checked the mappings of the login in the server. I seen no database was checked. I dont know how it lost its mapping to user in the database.

When I checked for the user in the database, I can see that user exist in the database and has all the required privileges assigned to that user too..

NOte: I tried to restart the server that which fix the problem for 2 hours and again we ran back to the same issue. I droped all those users(who lost mapping from Login) and recreated them with all the SIDs synced in all three servers. This fixed my problem for now. but I want to know the root cause of the issue.

Question 1: How my user lost their mappings with login suddenly that which are working absolutely fine since 1 YEAR.

Question 2: How a simple server restart fix the issue for a couple of hours and again why the problem came back.

0

Question 1: How my user lost their mappings with login suddenly that which are working absolutely fine since 1 YEAR.

There is nothing internal to AGs that would do this. This had to have been done by someone or something. The Logins and Users are mapped via their SID, thus if the mapping no longer was valid (different SIDs), the only way those can become different is either it was initially setup incorrectly or someone/something changed it. You could attempt to look at the default trace and see if it has any data, but if you weren't auditing for it chances are you won't find it retroactively as SQL Server doesn't save that type of data.

Question 2: How a simple server restart fix the issue for a couple of hours and again why the problem came back.

The "simple restart" just masked the problem. If you restarted a primary replica, then the primary would have changed - probably to the one with a working mapping. If it was a secondary when restarted, then the readable connection (if using read only routing) would have been disconnected and a reconnect would have went to your other secondary where the mapping was probably fine.

The restart didn't "fix" anything, you got lucky and "masked" it. This is completely dependent on the exact state of each replica at the time you completed this.

When I checked for the user in the database, I can see that user exist in the database and has all the required privileges assigned to that user too..

Yes, and it should. If it didn't then someone deleted the user and re-created it.

Users exist inside the database and thus their information is sent to each replica. Logins, however, are server level objects stored in master and thus are not part of the AG traffic as it is outside the bounds of the database. That's how this happens (mismatched SIDs).

Also, this is only an issue with a SQL Login - as Windows logins obtain their SID from active directory and shouldn't change (note that it can but takes admin intervention and commands).

  • Everything pretty acceptable. But why dont I got this problem since 1 year. Why I was encountered this problem suddenly. – Damodara Lanka Feb 14 '17 at 19:52
  • @DamodaraLanka something or someone changed the login or the mapping sid. There is no other way. – Sean Gallardy Feb 14 '17 at 19:54
0

Since an Availability Group is just a container of databases, you should treat users and logins the same way you would for a mirrored database, log shipping, etc. And that is that the logins on a secondary server must be created with the same explicit SID as the logins on the primary server. Otherwise the link between the server-level login and the database-level user will be broken upon failover - which I suspect is what happened here (I'm not going to try to retrace all of your steps and figure out how many times you failed over and back - let's focus on preventing this from happening again).

You say that you re-synced with the SIDs, but I don't know what that generic phrase means. You should include the code that demonstrates what you did, because if it isn't doing what you think it's doing, we may be able to tell you why, but we can't do that with just hand-waving.

The script I think you should be using is the one from Robert Davis, which Mike Walsh describes here (I'm not going to copy the code here):

There is also an SSMS add-in written by Jonathan Kehayias that helps automate synchronization of things like logins and jobs across all the nodes involved with an availability group:

However, this add-in only works in SSMS 2012/2014.

Length of time is irrelevant here. You had a year of uptime. Yay! You could failover tomorrow and be right back in the same boat.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.