1

We are experiencing this error intermittently:

The server principal is not able to access the database under the current security context

The database is part of an Availability Group.

Context : A trigger is fired on "Operations" database, which records entry in ChangeHistory database whenever a record is changed in Operations.

We literally have thousands of records working every minute with no issues. It happens so intermittently that it is very difficult to pinpoint. E.g. it could happen for a 20 minute period now - and then again only in 2 day's time and for only 4 minutes.

I believe that although the error message is what it is, that there is a different issue giving the same error message.

I wonder if it could be that the sys.fn_hadr_backup_is_preferred_replica for Operations and ChangeHistory are out of sync? E.g.

DBInstance1 Operations is_preferred_replica=1,ChangeHistory preferred_replica=0.
DBInstance2 Operations is_preferred_replica=0,ChangeHistory preferred_replica=1.

The DBInstance1 which is active , would then try to write to its own ChangeHistory database, which is now not the preferred instance and would generate some sort of error - even though it does not match.

Would this be possible - different DBs with different preferred replica statuses on one server?

If it is, would this be the error that is displayed?

Shanky
  • 18,985
  • 4
  • 35
  • 58
Peter PitLock
  • 1,375
  • 4
  • 21
  • 31
  • Most likely, this error occurs due to a failover from one node to another. Compare sys.server_principals between AG instances. For each login, is the sid the same across nodes? – AMtwo Feb 06 '17 at 11:38
  • Thanks AMTwo, I am attempting to change the preferred instance on one database, and then testing it at that point, will keep posted, thank you – Peter PitLock Feb 06 '17 at 12:52
  • Chasing the preferred backup replica isn't the answer. Are both databases part of the same AG? If not, why not? If they are in the same AG, then changes to the Operations and ChangeHistory databases will always happen together, on the same instance. – AMtwo Feb 06 '17 at 13:09
  • Are these databases in two different availability groups?! – Sean Gallardy Feb 06 '17 at 14:56
  • Both databases are on the same AG. I did want to know whether its possible for the 2 databases on one SQL instance to have different preferred instances - but it sounds like this will never be the case? – Peter PitLock Feb 07 '17 at 04:37
  • The documentation on fn_hadr_backup_is_preferred_replica seems to say that the settings will be the same for all AG DBs on the instance: "Returns 1 if the database on the current instance is on the preferred replica." There's theoretically no way the preferred backup settings could be directly related to your trigger issue, but perhaps the discrepancy is evidence of deeper problems with your AG that could also be causing the trigger issue. – T.H. Feb 07 '17 at 15:36

0 Answers0