How to prove a network problems with Linked Servers
-
Hi all,
I was wondering if anyone knew of a way to prove that the problem with a Linked Server connection is network related? Our linked server stopped working all of a sudden one day and I'm pretty sure it's related to the network or firewall (that I don't control because I'm a contractor). The linked server was working and our configuration hasn't changed (ie. TCP/IP is enabled and above Named Pipes on the connection stack, we are using the standard 1433 port for sql, and remote connections are allowed on the target machine with unlimited concurrent users, tried using SQL Browser).
If I remote in to the source server, I can ping the destination server, but I can not connect to it through SSMS. If I simply try to connect using the server name it throws a 'Network-related or instance specific error occurred while establishing a connection to SQL-Server. The server was not found or was not accessible. ... (provider: Named Pipes Provider, error 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53) The network path was not found.
If I then add the port number to the connection string, I get the same error but instead of Named Pipes Provider I get the TCP Provider, error 0 - The wait operation timed out. Microsoft SQL Server, Error: 258 The operation timed out.
I've looked up those errors and tried all of the solutions, but to no avail. Using the IP address instead of the domain name didn't work either. Any guidance to find where my problem lies would be greatly appreciated.
Thanks,
JimiHaze
-
linked servers are just an ODBC connection, so i'd start by removing the linked server from the equation.
from anyplace on the LAN, or from the server you are adding linked servers to. can you make an connection? I'm assuming we are talking about other SQL servers for now, but the idea is still the same.
prove there's no connection from outside first. if you can connect via SSMS to SQL, for example, the issue is your linked server; wrong provider or whatever makes a huge impact. if you cannot connect, it's most likely the windows firewall, and you'd need to ask them to add an inbound exception to go to SQL server for the port(1433, perhaps, or something else?)
if the linked server is MySQL, Oracle or something else, this would be the critical first step: make sure you can connect before you make the linked server.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi, thanks for the response.
I'm trying to connect from one SQL instance to another SQL instance. For simplicity I will call the server initiating the connection the SOURCE and the target as the DEST servers.
If I remote into the SOURCE server I can connect to that local instance of SQL, but I can not connect to the DEST sql instance via SSMS. If I remote into the DEST server I can connect to both the local SQL instance as well as the SQL instance sitting on the SOURCE server.
-
JimiHaze (3/29/2016)
Hi, thanks for the response.I'm trying to connect from one SQL instance to another SQL instance. For simplicity I will call the server initiating the connection the SOURCE and the target as the DEST servers.
If I remote into the SOURCE server I can connect to that local instance of SQL, but I can not connect to the DEST sql instance via SSMS. If I remote into the DEST server I can connect to both the local SQL instance as well as the SQL instance sitting on the SOURCE server.
remote desktop does an end run around the firewall, so you are on the right track.
since you cannot connect via SSMS, it sounds to me like it's just a firewall issue; if they don't have some appliance preventing traffic(it's not in a DMZ, is it?) then it should be a two minute fix to add an exception to the firewall.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
It is not in a DMZ, however it is behind more than just a Windows Firewall, there's a firewall appliance in the middle. I will try adding the inbound rules to the DEST server first and if that doesn't work it looks like I'll have to talk to the people who handle our firewall appliance rules.
Thanks very much for the tips!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply