I can connect to my instance locally but not remotely


October 23, 2012 by Kenneth Fisher

Every now and again (today for example) I’ll get a call that one of our SQL Server instances is down.  I’ll try to connect from my machine with no success.  Then I’ll remote to the server itself and first check if the server is up, then if it is, try to connect locally.  Today is one of those occasions where I was able to connect locally.  So now I’m in a bit of a quandary.  I can connect locally, but not remotely.  There are a number of reasons that this can happen.  Below I’ve listed the tests I perform to figure out what’s going on.  There certainly may be additional items that can be tested and if you know them I would love to have you add a comment. 

After each step if you have changed anything do a test remote connection to see if your problem is resolved.

Note: I’m abbreviating SQL Server Configuration Manager as SSCM

  1. In SSCM Check to make sure that TCP/IP is enabled.  If your shop uses Named Pipes check that also.
  2. If your instance is not using port 1433 then it needs the SQL Server Browser.  Here is a good description of what it does.  Either start or restart SQL Server Browser.  I’ve noticed over time that even though the Browser shows it’s started it may not be working.  Restarting it should fix this.
  3. At this point it’s probably a good idea to check the application event log on the server.  Particularly if the Browser won’t start.  Obviously the solution here is to Google/Bing/Searchofyourchoice the error and then correct it.
  4. Check to make sure your port is open.  You can find the port you are using in SSCM under the TCP/IP properties. To test the port it’s easiest to use telnet in my opinion.  There are a number of other products available if you prefer, but telnet is free and comes with windows.  TELNET <servername> <port>.  If it won’t connect then you probably need to check your firewall.  I’m not much good with firewalls but here is Pinal Dave’s blog on how to make the change.  Don’t forget to add the port for the Browser (1434) along with the ports for your instance.

I’ve placed these in a specific order based on experience and ease of test/fix.  Feel free to put them in whatever order you feel the most comfortable with.

4 thoughts on “I can connect to my instance locally but not remotely

  1. Pablo says:

    Thank you!! My issue was resolved simply by restarting the SQL Server Browser service. I had two instances of SQL Server running on the same machine, and I could not connect remotely to the non-default instance, but I could do so if I RDP’d into the server. Anyway, I was speculating that maybe there was some service that helped external queries get routed to the correct instance. Then I found your page here and the link to the Server Browser service description and I realized that was my issue! Restarting it did the trick.

  2. Gaurav Shah says:

    Thank god. spent 2 days messing with my firewall, server, client things, and finally came across this. Even though i had very little hope, after reading all the way, last bullet point fixed my issue. I didn’t know that server side needs 1434 port open as well!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: