Linked Server to Mirrored Database

[linkedServer]

Hi

I'm trying to set up a link server to a mirror database but it not working.

ServerA - Principle - sql 2014 Std
ServerB - Mirror - sql 2014 Std
ServerC - Linked Server - sql 2008R2

setup script:

EXEC master.dbo.sp_addlinkedserver
@server=N'MirrorTest',
@srvproduct=N'SQLSERVER',
@provider=N'SQLNCLI',
@provstr=N'Server=ServerAIP,PORT;FailoverPartner=ServerBIP,PORT;Network=dbmssocn;Database=MirrorTest;UID=sa;PWD=###',
@catalog=N'MirrorTest'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MirrorTest',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='###'

GO

after the setup of the linked server on ServerC everything works,
once i fail-over from ServerA to ServerB i get an error:

OLE DB provider "SQLNCLI10" for linked server "MirrorTest" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "MirrorTest" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 258, Level 16, State 1, Line 0
TCP Provider: The wait operation timed out.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
Msg 4060, Level 11, State 1, Line 1
Cannot open database "MirrorTest" requested by the login. The login failed.

please help

[Erland Sommarskog]

linkedServer (shemr...@gmail.com) writes:
> EXEC master.dbo.sp_addlinkedserver
> @server=N'MirrorTest',
> @srvproduct=N'SQLSERVER',
> @provider=N'SQLNCLI',
> @provstr=N'Server=ServerAIP,PORT;FailoverPartner=ServerBIP,PORT;Network=dbmssocn;Database=MirrorTest;UID=sa;PWD=###',
> @catalog=N'MirrorTest'
>

When you set up a linked server, you should not include everything
in the connection string like you usually do. My experience is that
if works better with putting the server in the @datasrc parameter.
Also, leave out username and password. That is.

@dataerc = N'ServerA,1234',
@provstr = N'FailoverPartner=ServerB,5687',
@catalog = N'MirrorTest'


> EXEC master.dbo.sp_addlinkedsrvlogin rmtsrvname=N'MirrorTest', > @useself=N'False', @locallogin=NULL, @rmtuser=N'sa', @rmtpassword='###'
>

I hope that you understand that this is quite a big security risk. This means that anyone on ServerC can access ServerA/B as sa.


--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[shemr...@gmail.com]

I have changed it and i still get the same error

EXEC master.dbo.sp_addlinkedserver
@server = N'MirrorTest',
@srvproduct=N'SQLSERVER',
@provider=N'SQLNCLI10',
@catalog=N'MirrorTest',
@datasrc = N'ServerA,1234',
@provstr = N'FailoverPartner=ServerB,5687'

its only working if ServerA is the principle.

[Erland Sommarskog]

Just to rule out the simple errors, set up a second linked server, but with
the roles reversed, make sure that it works when ServerB is the primary.

[shemr...@gmail.com]

works.

[Erland Sommarskog]

(shemr...@gmail.com) writes:
>> Just to rule out the simple errors, set up a second linked server, but
>> with the roles reversed, make sure that it works when ServerB is the
>> primary.
>>
>

> works.
>

But it still fails, if you fail over?


--
Erland Sommarskog, Stockholm, esq...@sommarskog.se

[Erland Sommarskog]

Since I don't have any mirrored servers to play with myself, I asked
around. An MVP colleague suggested that you need to use MSDASQL + ODBC:

EXEC master.dbo.sp_addlinkedserver @server = N'TESTMIRROR3' ,
    @srvproduct = N'SQLServer' ,
    @provider = N'MSDASQL' ,
    @provstr = N'Driver={SQL Server Native Client 11.0};Server=<server
1>,<port>;Failover_Partner=<server
2>,<port>;Database=<database>;Trusted_Connection=yes;'

Although, I think you will have to specify @srvproduct as ''; I will not
accept SQL Server when you use a different provider.

[shemr...@gmail.com]

Hi

its still not working

i have tried with the @srvproduct and without.

i am getting login error:

OLE DB provider "MSDASQL" for linked server "MirrorTest" returned message "[Microsoft][SQL Server Native Client 10.0]Login timeout expired".
OLE DB provider "MSDASQL" for linked server "MirrorTest" returned message "[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

it looks like it try to impersonate the login even if i use sa account.

[Erland Sommarskog]

(shemr...@gmail.com) writes:
> i am getting login error:
>
> OLE DB provider "MSDASQL" for linked server "MirrorTest" returned message
>"[Microsoft][SQL Server Native Client 10.0]Login timeout expired".
> OLE DB provider "MSDASQL" for linked server "MirrorTest" returned message
>"[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for
>user 'NT AUTHORITY\ANONYMOUS LOGON'.".
>
> it looks like it try to impersonate the login even if i use sa account.
>

You are making progress then, since you are actually reaching the server.

The connection string says Trusted_connection=yes, so that is not sa. If
you get the same message when you put in UID and PWD in the provider string
rather than Trusted_connection=yes, this would indicate that the other
machine is configured for Windows authentication only.

Judging from the error message you get, the instance you are connecting from
is running under Local Service or some such. There may also be a trust
issue, so that delegation does not work. You could try granting access
to DOMAIN\ServerC$, that is the machine account for server C.

[shemr...@gmail.com]

I have added the UID, PWD and it works when the principle was on ServerA
when i failed over to ServerB i got an error:

OLE DB provider "MSDASQL" for linked server "MirrorTest" returned message "[Microsoft][SQL Server Native Client 10.0]Login timeout expired".

OLE DB provider "MSDASQL" for linked server "MirrorTest" returned message "[Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
OLE DB provider "MSDASQL" for linked server "MirrorTest" returned message "[Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [5]. ".
OLE DB provider "MSDASQL" for linked server "MirrorTest" returned message "[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'CTLinked'.".
OLE DB provider "MSDASQL" for linked server "MirrorTest" returned message "[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "MirrorTest" requested by the login. The login failed.".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSDASQL" for linked server "MirrorTest" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MirrorTest".

[Erland Sommarskog]

It is difficult to piece this together, since the error messages are
conflicting in nature. Do you really get them all in the same attempt?`

But if I try to make an interpretation: The first three messages says
that you could not reach the server. These could be produced when you
are trying to reach the primary server - which is down.

The next three messages are produced when you try to access the mirror
database. This login fails, because the database MirrorTest is not
available.

So, if I'm guessing correctly, the linked server as such works,
but you are not able to access the mirrored database. There are a
number of possible reasons (and please beware of that I'm talking
right out into the blue with no knowledge of your environment):

1) The secondary database has not yet come online after the failover.
2) The database name is incorrect.
3) The login CTLinked is not a user in the MirrorTest database.
4) For the latter case, observe that if you just did "CREATE LOGIN
CTLinked WITH PASSWORD" on the mirror server, the login will
get a different SID than on the primary server, and mapping
between login and user is on SID, not on name. In this case, drop
the login and create it, this time using the SID option.

It also helps when troubleshooting to test direct connections, so that
you know whether the problems are due do the linked server, or are
general problems.

[shemr...@gmail.com]

Hi

1) after the fail over i have checked the status of the DB and it up on one server and restoring on the other.
2) the names are identical on both servers
3) CTLinked user is an sql login that exists on both servers with the same SID, its also sysadmin so it it not linked directly to any database, just like SA.

the error i have attached is the one i get whenever i try to connect after fail-over.
if i change the order of the connection string after each fail over or if i create new linked server only to the principle (ServeA or ServerB) everything works.

the problem only happen when i fail over.

[Erland Sommarskog]

(shemr...@gmail.com) writes:
> 1) after the fail over i have checked the status of the DB and it up on
> one server and restoring on the other.
> 2) the names are identical on both servers
> 3) CTLinked user is an sql login that exists on both servers with the
> same SID, its also sysadmin so it it not linked directly to any
> database, just like SA.
>
> the error i have attached is the one i get whenever i try to connect
> after fail-over.
> if i change the order of the connection string after each fail over or
> if i create new linked server only to the principle (ServeA or ServerB)
> everything works.
>

Since I don't see your system, and I don't have any possibility to
play with this myself, it is difficult to assist any further. All I can say
is that it sounds like something is not what think it should be.

One thing you could try is to use Profiler, and capture the event
Audit Login Failure, and do this on both servers, to see which one that is
producing the message.

[shemr...@gmail.com]

ok
Thanks for your help