SSH Port forwarding / Tunneling with multiple hops

Background

• Three subnets exist in an AZ in AWS. Two of them are private and one is public.

• The Public Subnet has a Jumpbox which can be connected to from my local machine via ssh using a pem file (Sample - ssh -i my-key-file.pem ec2-user@host1

• The First private subnet has an EC2 Instance that acts as a Application Server. It can only be reached from the Jumbox via ssh. The same pem file is used here. (Sample - ssh -i my-key-file.pem ec2-user@host2). This command is executed on host1.

• The second private subnet hosts an Oracle Instance using AWS RDS Service. It is running on port 1521. The DB Can only be accessed from the App Server/host2.

How I am working currently

• host2 has sqlplus client installed.

• First,I connect to host1, then to host2, and then execute sqlplus to execute Queries at the Command line (No GUI).

I am planning to use GUI tools like SQL Developer to connect right from my local machine. I thought using Port forwarding/SSH Tunneling It can be achieved.

I tried using different options, but with no success. The following links are useful:

• https://superuser.com/questions/96489/an-ssh-tunnel-via-multiple-hops
• https://rufflewind.com/2014-03-02/ssh-port-forwarding

My Approach to SSH Tunneling

• ssh -N -L 9999:127.0.0.1:1234 ec2-user@host1 -i my-key-file.pem -v -v -v
  • This is executed on my local machine.
  • It does not do much as I can already connect to host1 using ssh. I did not know how to forward many levels. Using this host as my first hop. After this, ssh listens on port 9999 which is Local to my machine. It forwards any traffic to host1 to Port 1234. My assumption is that, If I use sqlplus on my local machine connecting to localhost:9999, the traffic will arrive at host1:1234
  • I used 127.0.0.1 because the target of SSH tunneling is with respect to the SSH Server, which is host1. Basically, Both Target, SSH Server are on the same host.

• ssh -N -L 1234:db-host:1521 ec2-user@host2 -i my-key-file.pem -v -v- v

  • This is executed on host1

  • After this, ssh forwards any incoming traffic on port 1234 to target host (DB Host)/1521 using host2 as the Tunnel.

  • Again, my assumption is that, ssh is listening on port 1234 on host1. Any traffic arriving from anywhere will be delivered to DB Host using host2 as the tunnel.

I executed both commands and did not see any error. I verified which ports are listening using netstat -tulpn | grep LISTEN.

After these two, My plan was to connect to the Database using Hostname as localhost and Port number as 9999.

What's going wrong !

• But when I try to connect to the DB from my local machine, getting an error from my SQL Client Got minus one from a read call. I could not understand the Debug messages from ssh logs.

I believe my understanding of how port forwarding works might not be right. Any inputs would be helpful. Thanks for your time !