September 28, 2011 at 12:20 pm
I have to create linked server on my SQL server. Both server that will be linked r SQL servers
I have configured them previously and had no issue. but this time I am confused.
For Creating Linked server Created same admin Accounts on both SQL server and local machine account.
I am able to access both servers through management studio from each server. And both allow remote connections
I checked Protocols; Named Pipe and TCP/IP is enabled. They are required Order TCP/IP before Named Pipes.
What can be the issue
This is the error i got :
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Named Pipes Provider: Could not open a connection to SQL Server [2].
OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "An error has occurred
while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections.".
(Microsoft SQL Server, Error: 2)
September 28, 2011 at 12:32 pm
It might be worth checking to see if there are any firewalls enabled on either server.
What operating system are your SQL 2005 servers running on?
Mark G.
September 28, 2011 at 1:20 pm
can you check what error comes when you directly connect to the instance from SSMS?
Regards
Sushant Kumar
MCTS,MCP
September 28, 2011 at 2:26 pm
What credentials are you using for the linked server? On the Security tab of the linked server, are you utilizing Impersonate or a Remote login?
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
September 29, 2011 at 8:08 am
It's the same error that I posted in the question. Using Remote Login.
I checked Fire wall, It's not that Issue either.
September 29, 2011 at 8:28 am
Can you check if the sql ports on that remote server are open to firewall?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 6, 2011 at 11:03 am
Yes they are open to firewall
October 6, 2011 at 11:32 am
can you ping the remote server
and
also try to telnet the remote server
wat u get?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 6, 2011 at 11:16 pm
Focus on this:
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Can you include the TSQL for the create linked server script?
A couple things to check:
1. In your Surface Area Configuration, Under Database Engine, "Remote Connections", do you have Local and Remote Connections checked? Make sure this is configured on both servers
2. When creating the Linked Server, Under your "Server Options", what did you select for "Data Access" and "Rpc"?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply