Login fails when using SQL Mirroring

Question

I have a peculiar scenario where my mirrored database can't be accessed on one server but it can on the other. I've tried specifying a specific SQL login to get it working but when the database is failed over the other server SQL no longer is able to use those credentials. I also tried using integrated security to use domain administrator account which I figured would be consistent between both servers. No luck. So what can I do to remedy this problem and is there a way to have the one server instance pick up the security accounts from the other. Both servers are in a trusted domain.

Answer 1

SQL Server Database Mirroring is a database level high-availability mechanism. Anything that affects databases other than the one you're mirroring (ex. logins stored in Master) need to be manually propogated over to the mirror after the failover happens, or (more ideally) on a regular basis during scheduled maintenance.

If the logins are copied over and you're still running into issues, you're probably looking at a SID mismatch. Have a look here for more information and a resolution.

Answer 2

During any for of mirroring, only the principal is available, the mirror is offline.

In high availability mode there's also a witness, which is needed for auto failover. There need to be 2 nodes that agree on who the master is, to avoid both nodes become master.

You can use a connectstring like this to automatically connect to the active of the 2 servers:

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial
Catalog=myDataBase;Integrated Security=True;