A customer recently asked me if FireDAC can be used to connect to a database through an SSH tunnel. The short answer is yes, of course. But let’s understand better what is an SSH tunnel, and how this goal can be easily achieved when using Delphi and FireDAC.

SSH Tunnels

Secure Shell (ssh) is a standard tool included on most “network” operating systems i.e. Linux, UNIX, MacOS, and now also on Windows (natively speaking). If you had used PuTTY sometime in your life to connect to a remote command line interface, in general a Linux system, SSH was the protocol used for that.

So the key thing here to use FireDAC via an SSH secure interface is to understand that FireDAC (or any other database access framework) has nothing to do with it. What you need is to establish a permanent SSH tunnel and then setup your database connection properly.

We’ll see two ways to establish an SSH tunnel: one using an external tool (PuTTY) and the second one directly coded in your application.

SSH Servers

The aim of this article isn’t to teach how to create an SSH Server, but I had to create one to test my solution, so here is what I’m using. Basically I have a Linux VM running a PostgreSQL database (this is the database the customer would like to connect, but the solution is database agnostic), and over it I’ve installed and started an SSH Server following this tutorial:

https://docs.oracle.com/cd/E37670_01/E41138/html/ch25s03.html

This is for Oracle Linux (this is what I’m running due another proof of concept I had to do past week) but you’ll find similar tutorials for your preferred Linux flavor.

Testing the Connection

Before trying the connection from FireDAC, it’s a good idea to prove the connection using just command line tools that are integral part of the operation systems, so you have an idea if things are working or not.

So, having both the SSH Server and the database instance (PostgreSQL in my case) open and running on Linux, we can try a first connection from Windows using any SSH client terminal. Windows 10 has a native client since the 1809 “October Update”, but I’ll stick with PuTTY as this is a dominant utility for system admins.

These are my Putty configurations. In the Session page you need to specify the server IP and the SSH port (22 is the default):

And in the SSH/Tunnels configuration page you specify how the tunnel will be created:

What this configuration is saying? Basically to create an SSH tunnel you have to specify the Source port (any available/not used port number you have in your system) and the Destination IP/Port (in this case the Linux server IP address and the Port where the PostgreSQL instance is listening – the 5432 is the default PostgreSQL port). Of course, the Source IP address will be always “localhost”, meaning your database connection will point to localhost/63333 and the tunnel will do the magic, sending the commands to the server and receiving back the results.

Finally, opening the PuTTY connection and providing a valid Linux user and password (it’s recommended to create an specific user only for the SSH usage), you are going to see something similar to this below. Ugly, I know. You can test some Linux commands on it, like the “ls -l”:

Coding Time!

Having the infra properly configured and tested, let’s setup a FireDAC database connection and test it. As you can see below, this is a standard FireDAC connection using the PostgreSQL driver, the only non trivial thing is the Server/Port pointing to the tunnel created via PuTTY:

Now, normally you’ll want to make this “transparent” to your end user, meaning not having to configure the PuTTY or any other 3rd tool. To achieve this you need to consider a SSH client implementation for Delphi that basically will create this tunnel for you but from inside your app. There are many 3rd party components available for this, but also some wonderful open source implementations that you can find doing a search in GitHub.

My choice was the Ssh-Pascal library. It’s very complete and was built by the father of PyScript (one of the best open source Python IDE – fully made in Delphi) – so deserves an extra credit ?

https://github.com/pyscripter/Ssh-Pascal

The implementation is based on the libssh2 , a well known SSH implementation that is present in many languages and frameworks. With respect to the installation, it’s just a matter of adding the library sources in your project and using it, no components or external dependencies. To deploy, the libssh2.dll should be made available along your executable.

Your Own SSH Tunnel

Below we have what I have built for this proof of concept. Please, consider this is just to test the concept and not a fully implementation, meaning there are many improvements that can be done.

Some details:

• The PostgreSQL connection configuration is exactly the same from the one presented previously
• The Open/Close database buttons are responsible for, well, opening and closing the database connection
• The Open/Close tunnel code can be found below (please notice that all parameters are hard coded as this is just an example, please, don’t take this as an example of a good coding practice by any mean)

And finally we have the app running showing up some PostgreSQL data. Again, this can be done for any database – until someone send me a message saying something isn’t working ?

The full source code can be found here in my GitHub, along with some other demo projects.

Feel free to let your comment, issues, use cases, etc., it will be very appreciated!