Fixing Orphan User on SQL Server Snapshot Mirror

The problem is that the principal became disconnected from the witness server before it went offline. In this case, the witness cannot agree that the principal is offline because it was disconnected while the principal was still connected. As far as the witness knows, the principal may still be up.

In this scenario, it would require that you force service on the mirror to bring it online using ALTER DATABASE ... SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS.

I found the problem.

SQL Native Client and the .NET SQL Data Provider should seamlessly connect to a failover partner even if the primary is down; however, the default configuration of both providers is to try both TCP/IP and Named Pipes.

This configuration is fine, and works appropriately, when the servers are still both available but the primary has failed over to the backup, because the network-layer connection will succeed, but the application-level connection will be promptly refused causing the client to try the mirror. If the primary is unresponsive, however, the client will waste the entire default 20-second timeout period just waiting for the primary server to respond, first over TCP/IP, then over Named Pipes (which by itself has a default 20- to 30-second timeout period at the network level), and so in this situation it will return an error without ever having tried the backup mirror.

The solution, according to this TechNet article, is to force the client, one way or another, to connect using only TCP/IP, which will fail fast enough that the client has ample time to try both partners. You can manually configure the data providers to do this, or you can override their default settings case-by-case, by specifying the proper protocol to use in the connection string. The parameter to include that forces TCP/IP is Network=dbmssocn;. On top of that, the SQL client has a "three strikes" approach before it gives up and tries the failover mirror, so the Connection Timeout should be set long enough that it will get through this process and try the failover partner at least once. A timeout of 30 seconds should be adequate.

We tried this fix when our primary DB server was powered down again, and it does work. Now we're in the process of updating the config files of all our in-house apps (we have at least a half-dozen desktop apps and intranet sites that target this failover pair; it's a well-loved stack).