I can connect to my instance locally but not remotely4
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
- 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.
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.
Awesome! Glad I could help!
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!
Glad it helped! 🙂