SQL Login keeps losing access to databases

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.

It is rather easy to explain actually. If you created the SQL logins on each instance separately they each will have different SIDs in the master database. When the mirrored database fails over the SID in the database matches the SID stored in the master database of the principal server not the mirrored server. That is where the SQL login becomes orphaned. You fix this, but then it needs to be fixed again when you fail back to the original server. The other way is to copy the SID for the SQL login from the principal server to the mirrored server, this way the ISDs will match when the database fails over. The option is to use a domain login that is the same on both servers and you don't need to worry about SIDs.

Building on what Lynn said, for SQL logins, the start of authority is the instance, so that's used to build the SID. A different server will have a different start of authority, so the SIDs will be different. For domain logins, the start of authority is the domain, so they're the same on different servers on the same network. Like he said, using a Windows login should eliminate the problem.

If this isn't realistic in your environment, you'll have to fix the SID of the user to match the login when the failover occurs. This can be done with the sp_change_users_login procedure. You can create a job to fire a procedure that does what you need. When you create it and assign a schedule, pick the "Run when SQL agent starts" in the schedule type.

Sean now understands!

I thank you and greatly appreciate both of you for taking the time to help me and explain how this works.

I assure you this is a lesson that I'll never forget:-)

Excellent. Thanks for the feedback.

I had to deal with it at a previous employer where I implemented database mirroring. I used event notifications to handle the fixing of the SID in the databases that were mirrored as well as activating/disabling the scheduled backups as appropriate on the systems.