SSH Tunnel connection fails when using JDBC driver

Can you share the Python libraries used to create the tunnel? I wonder if the tunnel is only available to Python libraries... you could test that theory by checking if an HTTPCaller (pure C++) will use the tunnel.

Yes no problem, I'm using sshtunnel which is based on paramiko which in turn also uses cryptography (newer version than FME uses by default, so needed to temporarily modify the sys.path to load a newer version of cryptography first) fairly extensively.

I have actually used this Python tunnel with the JDBC driver on FME Desktop in my initial testing on my local machine. So the local testing process goes, start first python script to set the remote port tunnel from the DB, start a second python script to set the local port tunnel to the jump servers remote port, start FME workbench and connection + SQL execution and response goes fine. So I'm pretty sure the Python tunnel is equivalent to opening one using PuTTY/bash ssh.

I've also done this locally with asyncssh but had more trouble with getting that to work on FME Cloud, I think due to package conflicts (I haven't gone back and tried with the temporary sys.path trick yet).

Very interesting! Now I understand why you asked about this from an FME Cloud perspective.

Too far outside my area of expertise for me to effective I'm afraid.

Yeah, pretty excited to write this up once I'm done. But I'm fairly confident I've narrowed it down to something about the combination of JDBC and FME Cloud.

Ok, further experimentation with the alternative asyncssh which forced me to learn quite a bit about threads and asyncio in Python has revealed that if an FME Engine starts an SSH tunnel then pauses until the task is manually canceled then another Engine running an SQLCreator with the JDBC driver can execute successfully.

However, even if I managed to get the asyncssh tunnel running on a parallel thread, which a Python library can then use to execute and return SQL to the FME Environment to pass to a Logger, and running a subprocess that successfully tests the TCP tunnel via bash, still the SQLCreator hangs if it tries to use the tunnel. Now requiring a reboot of the FME Server as the secondary thread doesn't get correctly killed.

I'm now fairly certain that something deeper in how FME Engines are implemented is getting in the way. At this stage I'd really like any insight an FME dev might have.

we are coming against the same requirements, has an idea been created for this or have you made any further progress? many thanks

So there is good news and bad news.

My requirements were to manage a temporary SSH tunnel just long enough to execute the workbench then close the tunnel, potentially making it available for reuse. Particularly with the Python libraries there seems to be some mysterious behaviour of async/sub-processes either closing before the Transformers start working or never closing causing the next execution to fail.

What I did realize, is that in my case FME Cloud is running on a Linux box and therefore I could execute a fairly straight forward system call using subprocess.Popen(), stash the process PID and the process object, then ensure I kill it after the workbench finishes. Requires you to have available to the FME engine a known hosts file and probably use a public key for auth, but it facilitates compression over the tunnel as well so the performance seems to be fairly good.

Now if you have a pure FME Server instance and the SSH connection never needs to be broken, you have some simpler options. If you are in my boat then you also have to worry about port management/locking potentially requiring a semaphore type code pattern with a lock file or database and I found the easiest way was simply to have a second "manager" workbench which sadly consumes two engines but is simpler coding wise.

Drop me an email if you'd like more details as it is still a bit hacky where I'm at so I'm a touch embarrassed to publish publicly. But I can confirm, it is possible to either tunnel direct or via a jump server to give you port mappings to any other machine without opening those ports to the world wide hackers.

@jstanger​ thank you for such a comprehensive reply, that is really helpful and saved me a lot of investigation time. The process logging is the main concern for me as we have many database calls. @stewartatsafe​ what are your thoughts on implementing SSH tunnelling to sql rds databases? It would really help avoid us having convoluted methods to reach these resources in secure environments. Shall I create an idea for this? Thank you

Hi @oliver.morris​ . Supporting creating an SSH tunnel to a FME Cloud instance isn't out of the question. Clearly there is demand based upon this thread. Obviously security is a key concern as we would be opening up another entry point into the the infrastructure. Performance also needs to be considered. Have you ever sent larger volumes of data in production over an SSH tunnel (TCP)? I am a little bit nervous about stability and how lost packets would be handled.

Please create an Idea for it so we can see if anyone else is interested. I will file a task for us to do some further research and testing internally.

Hi @stewartatsafe​ . I can understand quite well the concern about stability. This is actually why I open the tunnel just long enough to execute the workbench then close it again. In terms of data volume, I've been pretty successful pushing what probably amounts to around 100mb worth of results from SQL queries (approx say 35 unique SQL transactions) without issue. Typically the issue arises if the SSH tunnel is expected to remain open over long periods of idle time. Also keeping the tunnel only as long as needed would be a way to mitigate security and if the tunnel required public key security rather than username/password it shouldn't be more vulnerable than existing attack points I feel.

Moving rasters and the like would obviously be a bit more of a risk but since the tunnel itself it just forwarding packets, the TCP or UDP protocol over top should handle dropped packets if possible.

I'll have a think about the best way to propose the idea. Some part of me would be quite happy with just having an SSH tunnel be similar to a Web Connection Resource on FME Server that could be referenced as required for various transformers somehow. But another part of me would quite like the idea of being able to drop in a SSHTunnel transformer that would open a tunnel that would be closed once the FME engine process finished. I have a feeling the way FME engine handles threads might make that tricky though.

thanks @stewartatsafe​ idea created - https://community.safe.com/s/idea/0874Q000000j0MJQAY/detail

we are more than happy to test if performance is a concern.