Connecting to a Database using SSH tunnel in NiFi

SandyClouds — Wed, 10 Aug 2022 14:19:43 GMT

Hello Lovely community,

I am new to NiFi, I am able to connect to mysql dummy database from Nifi and everything works fine.

But I want to understand how can we connect to databases that use SSH tunneling..

Because I have not seen any properties related to SSH in the database processors.

My processors being used is "capturechangeMysql"

This source DB server is secured and I need SSH tunnel to make the connection.

I have 100 such sources. Should I add my nifi server ssh keys in all the source servers ? and even then where can i give the SSH tunnel information? and the private key..

Because while using any SQL client we can use the tunnel along with main DB username and password as shown in attached screen, but for Nifi I am not able to see a way..

Please help me with your suggestions.. /\SSH tunnel

Re: Connecting to a Database using SSH tunnel in NiFi

MattWho — Wed, 10 Aug 2022 18:36:36 GMT

@SandyClouds

The ExecuteSQL processors do not support SSH tunnel. The expectation by these processors is that the SQL server is listening on a port reachable on the network. SSH tunnels are used to access the server via remotely and then execute a command locally on that SQL utilizing the SQL client on that destination server. The ExecuteSQL processor uses a DBCPConnectionPool to facilitate the connection to the database. The DBCPConnectionPool establishes a pool of connections used by one too many processors sharing this connection to execute their code. A Validation Query is very import to make sure a connection from this pool is still good before being passed to requesting processor for use.

While I have not done this myself, I suppose you could set up and SSH tunnel on each NiFi cluster server (example: https://linuxize.com/post/mysql-ssh-tunnel/). Then you could still use the DBCPConnectionPool except use the established tunnel address and port in the database connection URL.

Downside to this is that NiFi has not control over that tunnel, so if the tunnel is closed, your dataflow will stop working until the tunnel is re-established. The Validation Query will verify the connection is still good. If it is not, the DBCPConnectionPool will drop it and try to establish a new connection.

If you found this response assisted with your query, please take a moment to login and click on "Accept as Solution" below this post.

Thank you,

Matt

question Re: Connecting to a Database using SSH tunnel in NiFi in Support Questions

Connecting to a Database using SSH tunnel in NiFi

Re: Connecting to a Database using SSH tunnel in NiFi