How does one forward a TCP connection (specifically MyQSL client-->server) through a multiple hop SSH tunnel?

Question

I want to open a connection to a remote MySQL database. This database is in a private subnet on a different network, so I cannot directly open a connection to it. Remote SSH access is also completely disabled on the MySQL server.

My current process is this:

I open an SSH tunnel through a publicly available server, HOST_1. From that tunnel, I open an SSH connection to a server (HOST_2) which is on the same subnet as the MySQL server (MYSQL_HOST). Using the SSH session which I have open at that point (on HOST_2), I use the MySQL client to open a connection to MYSQL_HOST.

Here's a super-duper crude diagram of what's currently done:

What I want to do, is use my local computer's MySQL client to connect to 127.0.0.1:3306 and have the connection tunneled all of the way through from HOST_1, to HOST_2, and then to MYSQL_HOST, as though I were having the MySQL server running from 127.0.0.1 in the first place.

Again, I cannot open an SSH tunnel into the MYSQL_HOST from HOST_2, so no tunneling from there. I need to figure out how to forward the MySQL client connection through the SSH tunnel to HOST_2 and then have HOST_2 forward the MySQL client connection to MYSQL_HOST.

I thought I could maybe use redir or nc to achieve this. I'm unsure as to how, though.

There are a couple of questions that I've read, but don't understand everything enough to put the information together to get the answer I'm looking for:

1. An SSH tunnel via multiple hops
2. UDP traffic through SSH tunnel
3. https://unix.stackexchange.com/questions/267090/reroute-mysql-connection-through-external-machine

I know there is information in this page again, which would help me, but I don't know how to use it: http://sshmenu.sourceforge.net/articles/transparent-mulithop.html

Thanks!

Answer 1

This answer assumes that sshd is configured to allow port forwarding on HOST_1 and HOST_2.

You've already found most of what you need (option two in Mika Fischer's answer in your first link).

Tunnel from localhost to host1 and from host1 to host2:

ssh -L 9999:localhost:9999 host1 ssh -L 9999:localhost:1234 -N host2 This will open a tunnel from localhost to host1 and another tunnel from host1 to host2. However the port 9999 to host2:1234 can be used by anyone on host1. This may or may not be a problem.

The idea is to string together the port forwarding (ie you're forwarding a local port to a remote port that you will then forward to an even more remote port).

For your example, the command would be

ssh -tt -L 3306:localhost:XXXX [user@]HOST_1 ssh -L XXXX:MYSQL_HOST:3306 [user@]HOST_2 where XXXX is any port above 1024 that isn't already in use (use the same number to replace both instances)

Once executed, you'll be asked for password twice (once for HOST_1 and once for HOST_2), unless you're using SSH keys for password-less login. The tunnel will function for as long as you remain connected to HOST_2 via HOST_1.

Here's the breakdown:

ssh is the local ssh client you're executing

-tt forces allocation of pseudo-tty (see Pseudo-terminal will not be allocated because stdin is not a terminal for why this might be needed)

-L 3306:localhost:XXXX [user@]HOST_1 is telling your local ssh client to forward 3306 on your local system to port XXXX on "localhost" on the other side of the SSH connection. You only have to specify user@ if the user on your local computer is different from the user you're connecting to on HOST_1. More generally, you could specify a local port other than 3306, but you would then point your mysql client to 127.0.0.1:[your chosen port].

ssh (the second one) As you may know, ssh has an option to run a single command instead of launching a shell. You'll use that to execute another SSH session on HOST_1 which will create the next link in the port forwarding chain.

-L XXXX:MYSQL_HOST:3306 [user@]HOST_2 Since this ssh session is executing on HOST_1 this is forwarding XXXX on the local side (HOST_1) (which is being forwarded to from 3306 on your client) to port 3306 on MYSQL_HOST on the remote side (HOST_2). You only have to specify user@ if the user on HOST_1 is different from the user you're connecting to on HOST_2.

If additional hops are required, add instances of ssh -L XXXX:localhost:XXXX hostX for each hop in the middle, as long as XXXX is an available port on every server in the path.

Answer 2

Since you're proxying the SSH connection to HOST_2 through HOST_1, you can do it with only a single -L port forward:

ssh -A -o 'ProxyCommand=ssh -q user@HOST_1 nc %h %p' user@HOST_2 -L 3306:MYSQL_HOST:3306

This does essentially what you're doing now: opens an ssh connection to HOST_2 proxied over an ssh connection to HOST_1, and then it forwards connections from 3306 on the local host over that to MYSQL_HOST:3306. Note that MYSQL_HOST will see the connection coming from HOST_2, since that's where it exits the tunnel and turns back into a normal TCP connection.

BTW, if you have OpenSSH 7.3 or later on both the local computer and HOST_1, you can simplify the SSH proxying using the new -J option (or ProxyJump directive in your ssh config file):

ssh -A -J user@HOST_1 user@HOST_2 -L 3306:MYSQL_HOST:3306

This also makes it easier to go through multiple SSH proxies:

ssh -A -J user@HOST_1,user@HOST_3,user@HOST_3 user@HOST_4 -L 3306:MYSQL_HOST:3306