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.
This is a known issue.  Linked servers which are looking for database mirroring logins only work for Windows logins not SQL Logins as talked about on this connect item.  Basically you need to either hard code a username and password in the linked server, which isn't recommended or use Windows authentication to access the linked server.

Another option would be to setup alerts and SQL Agent jobs on the two machines so that when the database becomes active on those machines they automatically reconfigure the linked server with the correct servername.