Sql-server – Linked Server to Mirrored Database Fails

I've setup up a mirrored database on two servers Server1 and Server 2.

On Server3, I'm trying to setup a linked server that will work regardless of which server has the active database.

I'm using this to setup the linked server.

EXEC sp_addlinkedserver
  @server = N'MyLinkedServer',
  @srvproduct = N'',
  @provider = N'SQLNCLI',
  @provstr = N'Server=SERVER1;FailoverPartner=SERVER2;Network=dbmssocn',
  @catalog = N'MyDatabase';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'data access',
  @optvalue = N'true';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'rpc',
  @optvalue = N'false';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'rpc out',
  @optvalue = N'true';

EXEC master.dbo.sp_serveroption
  @server = N'MyLinkedServer',
  @optname = N'connect timeout',
  @optvalue = N'60';

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'MyLinkedServer',
  @locallogin = NULL,
  @useself = N'False',
  @rmtuser = N'sqluser',
  @rmtpassword = N'sqlpassword';

As long as MyDatabase on Server1 is the principal, the linked server works. When I manually failover to Server2, the linked server fails.

The error messages are:

Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sqluser'.
Msg 4060, Level 11, State 1, Line 1
Cannot open database "MyDatabase" requested by the login. The login failed.

The 18456 and 4060 error messages repeat until the query times out after 60 seconds.

On Server1, I can see repeated failures trying to login to "MyDatabase". It's failing because Server1 is the mirror. I see no attempts to connect to Server2 which is the principal.

If I recreate the linked server with Server2 as the Primary and Server1 as the Mirror, it will work until I fail back to Server1.

The mirrored servers are SQL Server 2012 and the box I'm trying to create a linked server on is SQL Server 2005, but I also have the same problem when creating the linked server on a SQL Server 2008 R2 box.

I've seen reports that it does work.

What am I missing?

Thank you!

Update

I've made progress. Yesterday, when I first set things up, I could have sworn it worked once. I could never reproduce it. Then, today, I got it to work once. I can failover from Server1 to Server2 (or vice versa) one time. The linked server will work after a failover, but if I failover back to the original server, it stop working. A restart of the SQL Server service on the server with the linked server will let it work again (for 1 failover). Letting things sit overnight must work too since it worked the first time I tried it today.

Creating a new linked server (with a different name) doesn't work, so I'm guessing that the SQL Native Client driver is caching something.

Another Update

I did get the linked server (as described above) to work correctly on a SQL Server 2012 Express instance. Then, I tried it on another SQL Server 2012 Standard instance. It did not work. One thing that might be relevant is that Server1 and Server2 are in a different untrusted domain than Server3. But, since I'm using sql server authentication, I wouldn't think it should. The Express instance that works fine is in the same untrusted domain.