A customer recently inquired whether FireDAC could be used to connect to a database via an SSH tunnel. Of course, the short answer is yes. But first, let’s define an SSH tunnel and how it can be easily accomplished with Delphi and FireDAC.
Table of Contents
SSH Tunnels
Secure Shell (ssh) is a standard tool that comes with most “network” operating systems, such as Windows. Linux, UNIX, macOS, and now Windows are all supported (natively speaking). If you’ve ever used PuTTY to connect to a remote command-line interface, most likely a Linux system, SSH was the protocol you used.
The key thing to remember when using FireDAC via an SSH secure interface is that FireDAC (or any other database access framework) has nothing to do with it. What you need to do is create a permanent SSH tunnel and then properly configure your database connection.
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 you 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 to another proof of concept I had to do last 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 an integral part of the operating 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 the 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 set up a FireDAC database connection and test it. As you can see below, this is a standard FireDAC connection using the PostgreSQL driver, the only nontrivial 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 PuTTY or any other 3rd party tool. To achieve this you need to consider an 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 by doing a search on 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)
[crayon-6741b3cc2a75b962655794/]
[crayon-6741b3cc2a767609542100/]
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 on my GitHub, along with some other demo projects.
Feel free to leave your comments, issues, use cases, etc., it will be greatly appreciated!
Do you want to try Embarcadero’s Cross-Platform Development Framework? InterBase is an ultrafast, scalable, embeddable SQL database with commercial grade data security, disaster recovery, and change synchronization. Request a free full trial here.