July 13, 2007 at 1:57 am
I am having trouble setting up linked server from 2005 --> 2000.
Linked server from 2005 --> 2005 works fine.
2005 --> 2000 gives me errors:
OLE DB provider "SQLNCLI" for linked server "xxx" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'yyyy'.
OLE DB provider "SQLNCLI" for linked server "xxx" returned message "Invalid
connection string attribute".
Tips?
July 13, 2007 at 2:36 am
Are you using Be made using current security context and are your services un under local system account then this errors change the services to run under a domain account preferably the same account to be used across both the servers.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 13, 2007 at 2:49 am
I am using "Be made using this security context"
And that account has adequate rights in the remote 2000 server.
/k
July 13, 2007 at 3:08 am
Check the sql server service accounts they have to run under domain account not localsystem account.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 13, 2007 at 3:42 am
I have tried connection to another sql server 2000 machine.
If I have sql running under Local system or under a domain account doesnt matter, it works on that machine.
However onto two other machines it doesnt work:
OLE DB provider "SQLNCLI" for linked server "XXX" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'YYYY'
Any tips for troubleshooting?
/k
July 13, 2007 at 3:45 am
Afterthought:
The sql server 2000 which is unreachable is on a win 2000-server.
The sql server 2000 which is reachable is on a win XP.
Can that be a clue?
/k
July 13, 2007 at 4:00 am
Problem solved?
By using a lokal sql user account on the 2000 machine it works.
I have seen referred to as "the two-hop-problem".
Can anybody explain this?
I know it works but not why it works.
/k
July 13, 2007 at 4:27 am
This is usually referred to as Double HOP problem which can be sorted out only with running the sql services in domain account or using a sql id to connect always. A hop is when a connection is made using a client to server. With linked server you always have a double hop as your client connects to the server with hop 1 and then when you fire the query using linked server the sql account tries to impersonate to connect to the other server which is the hop 2. You get the error in hop2 when running services under local account as they can't access resources in network. for more information read link below.
http://www.sqlservercentral.com/columnists/bkelley/qa2k_1.asp
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 13, 2007 at 4:46 am
This works:
1. sql2005server ---> sql2005server (windows domain account)
2. sql2005server ---> sql2000server on local machine (windows domain account)
3. sql2005server ---> sql2000server (local sql account on 2000server)
This doesnt work:
4. sql2005server ---> sql2000server (windows domain account)
I am on the calling 2005-machine by means of Remote desktop.
Can you under these circumstances say that in number 4 there is a double hop?
/k
July 13, 2007 at 5:39 am
The service account on both servers should be domain account else a local account with username and password same in both the servers and called as .\accountname in the service properties.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply