Can't access SQL Server from outside the network

Question

I am trying to log in remotely to SQL Server 2008 R2. After reading several posts I was able to log in remotely from a computer on the same network. But I still cannot remotely log in from outside the network. I have tried both setting up an ODBC connection as well as connecting via the Management Studio I have made several changes to the router and still cannot connect.

1. The operating system is Server 2008. Does the operating system know if a packet is coming from outside the network and can it reject it for that reason? For testing the firewall has been turned off.

2. Is it possible I have a bad router and need to replace it?

Solution 1

Ensure your router is set up to allow and/or forward the SQL traffic through to the server (port 1433 usually).

Check out this Microsoft KB article "TCP/IP port numbers required to communicate to SQL over a firewall" for reference.

Solution 2

I'm not sure if this is a personal thing or a corp environment or what, but if possible, you're much better off with a VPN solution. Exposing SQL Server ports to the outside world is generally not recommended and considered insecure. A VPN allows the remote machine to essentially act as if it's on the same network as the SQL server.

If you really want to expose it, not only do you need to make sure the firewall ports are open and forwarding to the correct machine IP, but you also need to ensure the SQL Server is configured to accept TCP/IP connections (Configuration Tools -> SQL Server Configuration Manager). Click Client Protocols and make sure TCP/IP is enabled.

My guess is that the router is not forwarding the port packets to the proper machine, and you should check that first.

Comments

[MaQleod]

When the router gets the request, does it know where that database is? basically, have you set port forwarding in the router to forward requests to your database to the right computer?

[Ƭᴇcʜιᴇ007]

"Exposing SQL Server ports to the outside world is generally not recommended and considered insecure" -- by who? Got any reputable sources? Although I agree most LAN resources are best to be protected behind a VPN when possible, SQL is no less secure than ANY Internet facing program, and actually has very good login security (especially in comparison to MANY web services). You can also encrypt the MS SQL connections. Check out msdn.microsoft.com/en-us/library/ms175483.aspx for more info.

[Scott]

Thank you for this answer. As soon as I read it I had a duh moment. I created the VPN connection an everything is working fine.