I can connect to my instance locally but not remotely

By Kenneth Fisher, 2012/10/23

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

In SSCM Check to make sure that TCP/IP is enabled. If your shop uses Named Pipes check that also.
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.
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.
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.