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!


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.

  • You're talking about just mirroring, not Always On right? What do you see if you ping or nslookup when a failover is initiated? Jun 12 '13 at 21:27
  • Yes, just mirroring. After I've failed over to Server2, I can ping both Server1 and Server2 from Server3 (the one that has the linked server).
    – Eli
    Jun 12 '13 at 21:33
  • What do you get for name resolution? Jun 12 '13 at 21:46
  • I'm not sure if this what you're asking, but if I run "nslookup Server1" or "nslookup Server2", it gives me their ip addresses. Since I can get Server2 to work if I make it the primary in the provider string, I don't think it's an issue with connectivity. I just seems like the linked server won't try the FailoverPartner.
    – Eli
    Jun 12 '13 at 21:55
  • This doesn't solve the immediate problem, but might give some insight. I came across it while researching the issue. blogs.msdn.com/b/spike/archive/2010/12/08/… I'd also like to understand what's happening in this case. Jun 12 '13 at 22:53

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.

  • I think I'm out of luck using Windows Authentication because the mirrored servers are in a different untrusted domain from the server with the linked server. And, hardcoding the sql username and password is what I was trying to do and doesn't work (except on that one SQL Server Express instance in the same untrusted domain.
    – Eli
    Jun 13 '13 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.