SSH Tunneling through a secure box

Hi, many of us must have landed into a situation which I faced recently, the issue was with one of the new features deployed to PRODUCTION environment in the latest release not working as expected, but I was unable to reproduce this on Local or QA environments.

So to debug it quickly, I tried something which ultimately proves really efficient, so here I want to share what I tried and the challenges that come into way and learning’s from them.

One thought that come into mind was try running the application at local so that any code changes done reflect quickly but need to point its database location to production as data seems the only thing that can reproduce the issue. But production cluster is setup in such a fashion that every server is encapsulated by a secure layer of firewall, and each of the machines residing inside can only be accessed via a gateway server. So there is only one way in.

Lets take into consideration that we want application running at local to be using the MySQL instance of remote server inside that Secure Network. So we need to forward MySQL requests from application, which can be achieved by below command

ssh -L <port1>:<host2>:<port2> host1

this will open port1 listening on local, which would forward any request on this port to host1 which would then forward that request to port2 on host2 server. So the example command could be like this.

ssh -L 3307:hostSQL:3306 hostGateway
where hostGateway is hostname for gateway server and hostSQL is hostName for server running MySQL instance

This will forward request on port 3307 on local machine to 3306 on hostSQL, but here connection from hostGateway to hostSQL will not be secured, so if we need to make it all the way secure we can use below command

ssh -L 3307:localhost:9999:hostGateway ssh -L 9999:localhost:3306 -N hostSQL
This will open a ssh connection from localhost to hostGateway and another secure channel from hostGateway to hostSQL.

Also please ensure that your application now has access to databases, so it does not make any write operation. Like turning off background jobs.
Another better way could be to use a MySQL user which has only read-only permission for database access.

Here is another blog which talks about tunneling over single hop. You can refer this to better understand tunneling.

Hope this helps!!!

Parampreet Singh