Configuring SSH Tunnels for Database Connections

For security reasons, databases may be configured to not allow direct access from remote servers. The server may have SSH enabled - allowing users to use an SSH tool to remotely login to the server. When configured this way, connecting to the database from a client database tool such as RazorSQL requires configuring an SSH tunnel to the database server. Without the SSH tunnel, connections are not possible since the database server does not allow remote connections. Databases such as MySQL and PostgreSQL are many times set up this way by hosting providers.

Assuming the database server is set up to allow SSH connections, an SSH tunnel configured on the client machine links a port on the client machine to a port on the remote server. Any communication done via the ports is handled by SSH, and as a positive side effect, is encrypted.

SSH tunnels can be started manually, or when creating a connection in a tool like RazorSQL, the SSH tunnel information can be entered and RazorSQL will start the SSH tunnel automatically prior to attempting the connection.

Manually Configuring an SSH Tunnel

To start the SSH tunnel manually, software called OpenSSH can be used. OpenSSH is available by default on Mac OS X and most Linux systems. It can be freely acquired for use on Windows systems. The OpenSSH for Windows installers include software called Cygwin that allow Linux / Unix like functionality on Windows.

To start an SSH tunnel manually, open a terminal window or command prompt, and use the following command:

ssh -L local_port:database_host:remote_database_port username@database_host

For example, to start an SSH tunnel that listens on local port 8000 and is connecting to a database host with the IP address of 192.168.1.13 and listening on port 3306 and using the testuser user name, type the following:

ssh -L 8000:192.168.1.13:3306 testuser@192.168.1.13

After typing the following, all communication on localhost or 127.0.0.1 on port 8000 will be forwarded to the remote host on port 3306 via SSH.

After the tunnel is up and running, when creating a database connection in RazorSQL, enter localhost or 127.0.0.1 as the host and 8000 as the port. For the user name and password, be sure to enter your user name and password for the database.

Have RazorSQL Configure the SSH Tunnel

To have RazorSQL start the SSH tunnel automatically, click the "Configure" button next to the "SSH Tunnel" field when creating a connection profile. Enter the SSH host, port, and user name. If connecting via a password - enter the SSH password. If connecting via a private key file, browse to select the private key file. If the private key file is encrypted with a password, enter the private key file password in the password field. Enter a port in the "Local Port to Forward" field that is not currently in use by any other processes locally. The database will often be on the same server as the SSH server you are tunneling to, so for the remote database host, localhost or 127.0.0.1 will often be used. For the remote database port, enter the port the database listens on. For example, the default for MySQL is 3306 and the default for PostgreSQL is 5432.

The software RazorSQL uses to create the SSH tunnel has some limitations. It adheres to the US governments export restrictions policies, so any encryption algorithms above a certain strength will not be compatible. Also, if connecting via a private key file, it requires the private key file to be in OpenSSH format.

Creating SSH Keys to Enable Logging in Without a Password

For information on how to create public and private key files to enable logging in to the SSH server without a password - click the following: Creating SSH Keys