topic Re: Using SSH and .pem file connect to Redshift DB in Talend Studio

Using SSH and .pem file connect to Redshift DB

Parikhharshal — Sat, 16 Nov 2024 03:36:51 GMT

Hi experts

I have a scenario where I want to connect to Redshift db and start reading data from redshift tables using SSH tunnel plus .pem file.

Can you please let me know how can I do it?

Thanks

Harshal.

Re: Using SSH and .pem file connect to Redshift DB

Parikhharshal — Tue, 14 Jan 2020 21:25:30 GMT

Anyone has got any idea on this? Please share.

I tried configuring custom ssh component and built job like this but getting the error:

Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Re: Using SSH and .pem file connect to Redshift DB

Talenduser1 — Tue, 03 Mar 2020 21:34:40 GMT

Did you find the solution for this? I also have similar requirement. Please share what you have done to connect to Redshift through SSH Tunnel

Re: Using SSH and .pem file connect to Redshift DB

Parikhharshal — Wed, 04 Mar 2020 04:12:13 GMT

@Gopal I think current ssh component is faulty.

So I implemented the same using following command in .cmd file and scheduled in task scheduler and running all the time on the Talend sevrer. So Talend can SSH anytime into tunnel and connect.

ssh -N -L 54321:XXXXXX.rds.amazonaws.com:5432 user-name@ec2-XXXXX.com -i C:/ETL/XXX.pem Username-sol-XXXXX.pem.

Working absolutely fine.

Re: Using SSH and .pem file connect to Redshift DB

Anonymous — Thu, 18 Jun 2020 22:54:04 GMT

I found here a solution how to use tSSHTunnel or to use the cmd command to use ssh tunnel with private key.

https://stackoverflow.com/questions/53941259/talend-tsshtunnel-usage

ssh.JPG

Re: Using SSH and .pem file connect to Redshift DB

Parikhharshal — Thu, 18 Jun 2020 23:01:45 GMT

Hi @aser

What I found out is there were different issues with this component so finally I manahed to write the script on the server which always runs in the background and port will remain open as long as script runs.

The powershell script looks like like this:

ssh -N -L 54321Bservernamewhereyouareconnectingto:5432 ec2-user@whereyouareconnectingfrom -i C:/ETL/test.pem dwuser-bastionhost.pem

Hope this makes sense.