Reverse tunneling for DBA's

After quite a long time I'm finally writing up a new post for your digestion. The reason for the long gap is that I'm going through something of a career change at the moment and I'm essentially walking away from DBA work to go forth as a cloud guy - more on that in a later post......

Anyway I thought I would share another useful connectivity technique (I'm not going to call it hacking although it does feel vaguely like that). This may or may not be useful for you but I think t's a useful technique to share as it could give you a temporary workaround in times of crisis......

So in our case this is the scenario - we have a nice lovely shiny new public cloud (lets call that Azure for arguments sake) and we have a big old mean datacentre with our 'legacy' stuff in (lets call that on prem - a phrase which still seems to annoy me inordinately).

In our world on prem can talk to Azure on a number of ports but nothing at all is allowed back directly - this completely kills any interfaces that want to work that way round.

Now in the future the firewall policy may be relaxed to allow specifics through but for the moment while we are still evaluating security around all of this it stays firmly shut.

I can illustrate this in the excellent diagram:

So we can't connect anything back then right......?

Well wrong actually if you know what you are doing with 'reverse ssh tunneling'.

Tell me more I hear you ask - well OK here we go.

So in a very simple case above lets assume that we have a Linux box on prem with a database on and a Linux box in Azure that wants to connect to this database - lets show how this reverse tunnelling can be set up.

So to initiaite the reverese tunnel I run this command from the on-prem box

ssh -R 4444:on-prem-server:1521 azure-server
In my case I have ssh keys set up to allow this to connect without a password (but that's not necessary if you just want to do a test - just be aware that as soon as the session dies the tunnel of course dies too). There are extra flags to create the tunnel as a background process but again I'm ignoring this for now.

If you have a putty session open when you run this you'll just end up with a session logged in to the remote server than you can just use as normal.

However what it's cleverly done in the background is create this reverese tunnel - lets dissect what the command options have actually done.

Well above you can see the mention of 2 separate ports and 2 separate servers - lets cover each one separately.

1)  ssh -R 4444:on-prem-server:1521 azure-server

Here port 4444 is the endpoint on the azure server that I will later be able to use to connect through - after the ssh command is established you will see port 4444 listed as listening in a netstat output

netstat -an |grep 4444
tcp        0      0 127.0.0.1:4444          0.0.0.0:*               LISTEN
tcp        0      0 ::1:4444                :::*                    LISTEN
(Note here that by default the ssh daemon will only start the port on loopback addresses - this is configurable though shoud you want to change that and have the tunnel accessible from other servers in Azure via this initial server)

2) ssh -R 4444:on-prem-server:1521 azure-server

Here on-prem-server is the endpoint that the tunnel will come back to - in this simple case its the same place that I initiated the ssh from - but it doesn't have to be - as long as the initiating server can get to the endpoint then that will work too)

3)  ssh -R 4444:on-prem-server:1521 azure-server

Here 1521 is the local endpoint that we want the traffic to come back to - so in this case we want the remote endpoint of 4444 to send traffic back to 1521 o the on-prem server - i.e. the listener of course

4) ssh -R 4444:on-prem-server:1521 azure-server

And finally azure-server is the server that we want connections to come back from - as mentioned earlier if this is the actual server that needs to come back then the tunnel will work fine - if it's some other Azure server then the ssh daemon config needs to be changed to allow ssh to act as a gateway - i.e. listen on a non localhost address

Ok so now that hopefully explained lets show how you can connect back through this tunnel

I'll demo it just using sqlplus with ezconnect syntax as its the simplest thing to use - so to establish a connection back over the reverse tunnel I just need to point my sqlpus connection at the new locally listening endpoint on the azure server over 4444 - so that just looks like this and is executed on the azure server


sqlplus user/password@//localhost:4444/DBNAME

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 29 10:41:12 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Thu Jun 29 2017 10:41:03 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
And there you have it - it connects just fine through port 4444 which gets reverse tunnelled back to 1521 on prem - very neat and also quite terrifying at the same time - I can hear firewall admins screaming from here.

I attempted to show the picture of that here - not quite as slick as the last one (the vague purple line is meant to show the connection coming back up the tunnel)

And there you have it - for some reason this whole technology reminds me of the parasitic fish that swims up your urine if you piss in a river- more on that here  https://www.animalplanet.com/tv-shows/river-monsters/videos/fan-favorite-fish-swims-up-urine-stream