13

With a SQL Sever database which is on a remote hosted dedicated server I can work using SQL Server Management Studio installed either on the same server or on my local computer. I the first case I should work using Remote Desktop and this makes the work a little bit slower. In the second case I need to open additional port in server's firewall, but I will have more comfortable user experience.

What is the recommended practice of these two?

1
  • It wasn't until I read the first answer that I realized I was assuming you would use a VPN. Jan 14, 2011 at 15:04

4 Answers 4

9

Opening the SQL server to direct outside connections is an idea to avoid if you can - you are significantly increasing your visible surface area for attack and any communication between you and the server (aside from login credentials in the authentication phase, perhaps) will not be encrypted. If you must open the port, make sure it is open only to your fixed IP address(es) (assuming you have fixed addresses that are not shared by anyone else, i.e. you are not using an ISP that has you behind a NAT arrangement like most mobile internet providers).

A much better option would be to install a VPN setup (such as OpenVPN for instance) or a SSH service (Cygwin includes a port of the common standard OpenSSH which I fund currently runs well as a service under Windows 2003 and 2008) that you can tunnel a connection through. This way SSH or the VPN handle both authentication and encryption, and adding (assuming you have secure passwords/keys) a significant layer of protection that a directly open port would not have. The compression support they offer will help response times for any queries that return more than a small amount of data too. A SSHd based arrangement may be easier to setup, especially if you are familiar with SSH already of course, though most true VPNs (like OpenVPN) have the advantage that communication over them is more likely to survive short communication blackouts (like an ADSL router losing sync and reconnecting). The VPN option is also less hassle if you have multiple instances of SQL server running on the target machine or at the target site. I've used both methods successfully for a number of things, including talking to SQL Server using local versions of the standard tools and found them to work well (though my OpenVPN servers are all on Linux, but I'm told it works well as server on Windows too).

Connecting to the remote machine (in)directly like this can be very convenient, but be aware that even with a careful VPN setup you are increasing the amount of machines your SQL Server is exposed to so, especially if it is a production system with your user's real data in, make sure you take the time to ensure that the machines that can see the server over the VPN/tunnel are secure and uninfected themselves (you probably do this already, of course, but it is always worth restating!).

10

The way that I know of to do this is to establish a VPN connection to the other site, thus eclipsing the need to open a port and maintaining the security of the setup, while eliminating the need to RDP into the other server. Granted, you run the risk of dropping a connection while you're working, thus possibly ending queries prematurely, but otherwise ... it's what we do and it works well for us.

If you're going to use them on a regular basis remotely you may look into a semi-permanent VPN connection (I'm sure there's a better name .. VPN bridge?) that will allow you to just connect by IP address. A networking guy can weigh in better here.

5

At our office we have two firewalls one firewall to the public internet and one to the internal network. If you are on the internal network, you can connect to the SQL server directly. If you on the external Internet you cannot, because the port is not open on the firewall. If you want to connect through the Internet, you have to login through VPN.

3

At our office we have a permanent VPN to all the db servers and we work with local Management Studio over remote servers. Using remote desktop is a bit slower than having the local tools look & feel :).

When remote desktop connection is really needed, you can use some free tool (like Royal TS) and keep all the server connections in an encrypted file and use it only when needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.