Linked Server connection failure

High Availability
1

Here is my issue

Principal Server:-ServerA
Mirror Server:-ServerB
ClientServer:-ServerC

Linked server is configured to connect a Database named DB1 with server and failover partner configured as
ServerA and Server B respectively.

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServer',@srvproduct=N'', @provider=N'SQLNCLI10', @provstr=N'Server=ServerA;FailoverPartner=ServerB;Network=dbmssocn', @catalog=N'DB1'

Linked server connection fails and not recognizing the mirror server ( after failover)
though failoverpartner param set.
After Recycling Client SQLServer,linked server is able to connect the mirror server.
Is this related to provider cache(SQLNCLI10)?

2

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 server name.

3

Thanks for the reply Jason.Your help is greatly appreciated.I'm configuring this in a workgroup environment and hence cannot go with Windows authentication.Is there a alternative for workgroup systems?
harcode the user name and password in provider string?