From location1, machine1 connects to a remote SQL Server instance, no trouble. Take machine1 move to location2, very similar environment – machine1 fails to connect, the error message is the one above. You verify your SQL Server configuration and everything looks ok, and besides if something was not right you wouldn’t be able to connect from location1 either. So what is the problem?
It turns out location2 firewall is blocking UDP traffic and that is why you are not being able to connect. When the client sends the request to the server asking to connect to server1\instance1 it is the SQL Browser service (on that server) that handles the request and responds with more detailed information to the client, information that the client needs in order to establish the connection to the server. That communication between the SSMS and the SQL Browser service happens over UDP (port 1434). So, when the UDP traffic is filtered out the client does not hear back from the SQL Browser and consequently is not able to establish the connection.
So what can you do if you can’t change the firewall configuration? If you know where your SQL Server is listening then all you need to do is specify the protocol and the port number as shown on the pictures below and you will be able to connect – in this case the SQL Browser service is bypassed since the client already has all the info it needs to connect to that SQL Server instance.
What if you don’t know what port SQL Server is listening on? If you can connect from some other location to that server you can try Microsoft’s Port Query utility (this is a great tool for diagnosing your connection problems when you can not connect also) http://www.microsoft.com/downloads/details.aspx?familyid=89811747-C74B-4638-A2D5-AC828BDC6983&displaylang=en they also have a UI add on for this that you can download from here: http://www.microsoft.com/downloads/details.aspx?familyid=8355E537-1EA6-4569-AABB-F248F4BD91D0&displaylang=en – it will show you the response from the SQL Browser service indicating which port your SQL Server instance is listening.
No comments:
Post a Comment