Access my SQL server from home in Server Explorer

Question

On my LAN at work, I have SQL Server 2005 running on one machine and my developer machine (a different machine) has VS2008 on it and I can access the SQL Server from Server Explorer in Visual Studio using the computer name and the local lan domain name i.e.: mspc2.ourcompany.local So, I know it is all up and running well, and I can see table on it and everything.

Now, the SQL Server box has a public IP that points to it, which I presently only use to Remote Desktop into the machine to poke around on it from home.

So, what do I need to do so I can hit that SQL Server from Visual Studio on my laptop at home, or from any other computer that is not on the LAN? I have turned off all software firewalls on that machine, and I am doing a pass-through on the hardware router to let all traffic/ports to that public IP route through to the LAN IP of that machine.

I can hit that machine with Remote Desktop with no problem.

I tried typing the public IP address into Server Explorer, but it comes back and says could not connect.

So what do I need to do to open up that machine to allow me to hit the SQL Server on it from off site?

Answer 1

You would be better off setting up a secured VPN into your network and accessing the SQL Server that way, rather than making it publicly accessible.

Answer 2

Try checking the port SQL Server is listening on, on that specific machine and ensure that you have either port forwarding set up on the work LAN/gateway and/or that the port is unblocked between your laptop, home LAN/gateway and the work LAN/Gateway and the SQL box itself.

To check which port SQL is using use the SQL Configuration Manager typically found in Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools.

Once open, expand the LHS tree "SQL Server 2005 Network Configuration" and check TCP/IP is enabled. Right click TCP/IP and select Properties, then the IP Addresses tab. The default port is 1433.

As someone else has already mentioned, establishing a VPN connection into the work network is the safer and recommended approach though.

Answer 3

To access the box via Management Studio from your home box, you'll have to make sure that TCP/IP port 1433 is open on both the server and the client firewall. This is the handshake port (or you can change the default port and access it via xxx.xxx.xx.xx,portnum as the address). After the handshake completes, the client and server will actually communicate over a different port so this will have to be open as well. You can find more information here.

In general, though, you should simply be able to point Management Studio to the IP address and be good to go. Once you reach the box, you'll need to log in via a SQL login account (do not use sa - see my advice below).

If you are still having trouble, run the SQL Server Configuration Manager (on the SQL Server menu) and make sure that TCP/IP is enabled (see Protocols under Network Configuration) and that SQL Server is open to connections from outside the box. Finally, if you are still having trouble, check the networking on your local box: find CliConfig.exe in the Windows directory (or System32) and run it to determine whether you are using TCP/IP there as well.

Ok - a few words about security:

My first piece of advice is to recognize that you are quite vulnerable in your current configuration. To help reduce vulnerability, make sure you run the SQL Server surface analysis and follow its advice where appropriate.

You'll also want to disable the sa account (go to Security, Logins and find sa, right-click and select properties, then click Status and, under "Login" click disabled). You'll use your administrative Windows user account to access the box rather than sa. Finally, create an new SQL login account and a difficult password to use from your remote computer. The sa account, you see, will be attacked incessantly once hackers find port 1433 open (I was getting 16,000+ per day before hiding behind a firewall). Other accounts names will not.

Answer 4

You may also need to make sure the SQL Server Browser service is running, if you still can't see it after opening port 1433 (or whatever yours is set up to), etc.