Lynn Pettis (8/29/2016)
First, to clarify, you say this only happens on the mirrored instances of SQL Server, not the stand alone instances.When you get these errors, has there been a failover from the primary database to the mirrored database? If this is the case I suspect that the problem is a difference in the SID for the login between the principle and mirror database. If this is the case you have three choices.
1) create a process that runs on the mirrored systems to fix the SID when there is a failover. This will be needed on both systems.
2) copy the login (master database) from the principal to the secondary. What you really need is the SID. If these match between principal and mirror you don't have to do #1.
3) switch to using a Windows domain account
Thank you for replying and your solution, Lynn. 🙂
Yes, this issue has only been noticed on our mirrored instances and it happens after a failover.
Now that I know how to fix the problem, I want to know why this happened. I'm trying to understand how/why the SID would make a difference to SSRS when the report is ran.
Does the SQL login go into a "limbo status" after a failover and break all connections to the databases? Every time we create the account on the new principle databases it works, which means it is a new SID every time. If it works once on SERVER1 and fails over to SERVER2 and then back again to SERVER1, it should still work (in my mind), but that doesn't seem to be the case.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.