Connecting to SQL Server 2008 through an ADSL Router

I'm trying to connect to a SQL Server 2008 instance over the Internet through an ADSL Modem/Router. I've already configured the router to forward ports TCP 1433 and UDP 1434 to the IP of the server. I've also disabled the Windows Firewall on the server. I can connect locally to the instance using SQL Server authentication, but I'm unable to connect over the Internet, because it fails with the error message:

Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

I'm really out of ideas of what's wrong. Any help would be appreciated.

Solution 1:

I'll probably not be the only one to say this, but exposing a server to the general internet in this way is a bad idea. Consider some sort of VPN software, not only will it be far more secure, but you'll have a much easier time with the port forward setup. (You'll only need to route a single port through the modem to the server for the VPN connection and everything else will work through that).

Checkout http://www.openvpn.net for a decent, free VPN solution.

Solution 2:

It's probably because SQL Server is not listening over TCP/IP.

A default install of SQL Server will accept any incoming connections. This includes other machines on the same LAN, as well as from the Internet (which, in really means traffic coming from the router on your LAN).

You use the SQL Server Configuration Manager to enable other protocols that SQL Server will listen on (e.g. TCP/IP):

After that you'll have to re-enable the (old) default port 1433 for use:

i would use Microsoft SysInternals TCPView to check that SQL Server is finally listening on the port you expect:

This is all part of Microsoft's new broken by default strategy.