Redshift Connectivity through SSH

[RBhupathiraju]

Hi Team,

I am unable to make a connection to Redshift(via SSH) using Talend. I tried tSSH and tDBOutput (Redshift). It is failing saying Connection timed out. I want to know if Talend supports Redshift connection via SSH?

Thanks

[Anonymous]

Hello,

Talend doesn't supports Redshift connection via SSH, please use tDBConnection(Redshift) to provide the connection parameters directly

Thanks

[RBhupathiraju]

I tried that but tDBConnection(Redshift) doesnt have any option to specify SSH parameters

[Anonymous]

Hello,

Try to create SSH tunnel outside of Talend via PuTTy or openssh and manage this on the server, then from Talend you could use localhost:port.

tSSH uses a library that also supports port-forwarding but this is not exposed, and would require a routine / custom java code. https://github.com/apache/mina-sshd/blob/master/docs/port-forwarding.md

[RBhupathiraju]

Hi,

So should we connect to the bastion server, and then install talend on the server and connect directly to redshift from there? I dont think that is how SSH should work nor the bastion server. Can you please tell if Talend has the capability to give the SSH details in the job itself and make a connection to Redshift?

[Anonymous · Accepted solution]

Hello,

Currently we don't have such component that would allow you to create an SSH tunnel towards a bastion server.

This is usually something you'd need to do at the server level, outside of Talend. On the jobserver / Remote engine server create an SSH tunnel towards/through your bastion, and then let Talend connect to this tunnel. Depending on the tunnel setup this might be a localhost entry or you might be using a dedicated computer.

I.e.:

Talend -> tunnel -> redshift

Where tunnel could be:

localhost:port -> bastion

or

remote_host:port -> bastion

[RBhupathiraju]

Hi,

Thanks for your reply. I will work with my network team on this.

[RBhupathiraju]

Hi,

We are finally able to make a connection to redshift from Talend without using the SSH command within Talend Studio. Below are the steps we followed:

1) A Batch file is setup to open a connection to bastion

the batch file `ssh -T -N -i C:/xxxx/Privatekey -L 5439:xxxxxxxx.ap-southeast-2.redshift.amazonaws.com:5439 username@bastionIP &

pause

(The & and the pause are to keep the connection and the command open)

2) In talend, we scrapped the command step, as Talend just runs then closes this step.

3) In Talend, we modified the host to 127.0.0.1 which is the universal “localhost” address, this tells Talend to look for the connection “on the same machine that we are running on”