Docs‎ > ‎Security‎ > ‎

Reverse SSH tunneling

Your Espresso Logic server runs (typically) in the public cloud (e.g. Amazon).

Your database very often runs in a private network, behind a heavily fortified firewall.

This means that, by default, your Espresso Logic server cannot connect to your database, since it is blocked by your firewall. That is, after all, your firewall's purpose.

However, there is an easy, elegant and secure technique that will allow you to solve this seemingly intractable problem. It's called reverse SSH tunneling.

You can set this up in just a few minutes, once you have the required information.

Find a computer on your network that can connect to your database.

This can be the database server itself, or it can be some other computer, including your own desktop or laptop. Any machine that can connect to your database should do.
In general, it's preferable to use a server-type machine, since you typically don't want to kill the connection when you go home with your laptop. But for temporary connections and experimentation, a desktop or laptop machine will do fine.
A Unix, Linux or Mac is usually preferable, but a Windows machine can also be used with a bit of extra work.

This assumes, of course, that your firewall allows you to do this. Port 22, which is used by ssh, is often constrained.

You will need the following information:
  • <private-key-file>: this is the path to the private key file that you got from Espresso.
  • <your-server-name> : this is the raw name of your Espresso server, which you got from Espresso. It will be something like ec2-XX-XX-XX-XX.compute-1.amazonaws.com
  • <database-server-address>: this is the address of your database server. It can be a name or an address, as long as you can access it from your machine.
  • <database-server-port>: this is the port number of your database server. For MySQL, it's usually 3306, for Oracle, it's usually 1521, and for SQL Server, it's usually 1433.

Instructions for Unix/Linux/Mac

1 - Make sure you can run ssh on this computer

Open a command line and try to run ssh. If it's not found (which is rather unusual on any Unix-type OS), you'll need to install it. 
On Windows, skip to "Instructions for Windows" below.

2 - Create the ssh tunnel

Execute the following command:

ssh -nNT -i <private-key-file>.pem -R 23432:<database-server-address>:<database-server-port> tunnel@<your-server-name>

This command will not return, and the tunnel will be open as long as that command is running. If you want to run the tunnel in the background, you can add the -f option. You can also run this as a service -- ask your Unix administrator for details, as this is rather OS-specific.

Instructions for Windows

Windows does not come with ssh pre-installed, so you'll need to get some software.

PuTTY is a free package (we recommend you download and run the installer). Putty is free, and is adequate for casual testing. 

For serious use, we recommend BitVise SSH client, which is fairly inexpensive. We have found BitVise to be more reliable and easier to use.

Instructions for Putty

If you use Putty, you'll need to convert the private key using puttygen:
  1. Start puttygen.exe (it should be in the same directory as PuTTY)
  2. Select Conversions -> Import key
  3. Select the private key file that you should have received from Espresso (it has a .pem extension)
  4. Click Save private key
  5. You may be asked "Are you sure you want to save this key without a passphrase to protect it?". Click Yes
  6. Type a file name for your converted key file, e.g. tunnel.ppk. This is the private key file you'll be using in the next step.
Important: in the next step, make sure you refer to the converted key file (with the .ppk extension), and not the one you got from Espresso (with the .pem extension).

Once you have the converted key, you can run the following command (plink.exe is in the same directory as PuTTY):

plink.exe -N -i <private-key-file>.ppk -R 23432:<database-server-address>:<database-server-port> tunnel@<your-server-name>

If successful, the output should simply be:

Using username "tunnel";

Note that this command will not return: as long as it is running, you will have a tunnel open between your Espresso server and your database. You can hit Ctrl-C to stop it and terminate the tunnel.

If you want this tunnel to run on a permanent basis, you can look at various solutions (such as Microsoft's srvany or Iain Patterson's excellent NSSM) to run this command automatically as a service.

Instructions for BitVise

BitVise can use the key file as is, there is no need to convert it.

After starting the BitVise client, you'll need to enter your server name in the "Host" field. "Username" should be "tunnel", and port should be 22. You can then click on Host key manager to import the key file.


Once that is set up, you can then select the S2C tab and create an entry with the following values:


The Listen interface should be the IP address of your Espresso server, which is implied in its name.
The Listen Port should 23432
The Destination Host should be the name or IP address of your database server.
The Destination Port should be the port number for your database.
Database    Default Port number
MS SQL Server 1433
mysql 3306
Oracle SQL*Net Listener 1521


Click the Login button, and your tunnel should be up.

Connecting to your database

Once the tunnel is established, you should be able to specify your database URL in the Logic Designer as:

MySQL:
jdbc:mysql://127.0.0.1:23432/<schema-name>

Oracle:
jdbc:oracle:thin:@127.0.0.1:23432:<Oracle-instance-name>

SQL Server:
jdbc:sqlserver://127.0.0.1:23432/<database-name>

Please note: for SQL Server, you may need to follow these additional instructions.


Use a valid user name and password for your database.

Please note: you must use the address 127.0.0.1 in the JDBC URL. This is a special address referring to your Espresso Logic server.

Test your database in the Logic Designer to make sure everything is working properly. High latency is possible if your database is far from your Espresso server.

Remember that your Espresso Logic server will only be able to talk to your database as long as you keep that tunnel open!