Remote Connection Fun with Tunneling!

Oftentimes, we find ourselves not able to be On-Site or otherwise physically On-Console of the server we need to work on.  If time is of the essence the administrator with command line savvy can conquer just about any remote administration or troubleshooting need. How does one acheive this level of connectivity?  Where can I get some of that? Well, look no further than 'terminal' and 'ssh'.

To the seasoned admin terminal and ssh are ubiquitous and necessary daily tools that can ease the administration of local or remote systems.  Most of the GUI programs available are nothing more than fancy wrappers for the command line but often with a reduced subset of functionality.  This post will attempt to get you reasonably acquainted with creating an SSH tunnel with the available tools on a Linux or OSX system. For those of us on a Windows platform, you too should be able to take advantage of the instructions, only you will need the free program- PuTTY, available for download from: http://www.chiark.greenend.org.uk/~sgtatham/putty/ . 

When getting started sometimes the biggest hurdle is actually finding where 'terminal' is in the filesystem.  For OSX users, typing in 'terminal' in spotlight should find it quickly, if looking in finder, navigate to Applications -> Utilities -> terminal. On Linux, there is typically a menu item for terminal under 'System Tools'.

As with just about anything allowing remote connections, the firewall at both ends needs to allow traffic to and from the specific ports.  99% of the time, all that is required is the SSH port to be forwarded, which by default is port 22.  However with the prevalence of scripted attacks probing wide swaths of the internet for the default port 22, admins often move the SSH service to a differnt port i.e. 2222, or whatever they desire as long as it's not conflicting with some other applications ports.

SSH in itself is a very powerful program with very deep abilities - it has been called a 'Swiss Army Knife'.  For my purposes, I'm only going to cover the more common scenarios as they relate to creating a tunnel to a remote server and port.

Lets get started:

Scenario 1:

Suppose you need to connect to a database named 'pilot' which is at erp.xyz.com, and they have setup admin/password for you to login.  PostgreSQL is running on port 5432, but is not configured to outside access.  Their SSH service is running on port 2222.  Concidently, you are running your own postgres locally on your PC you're using to connect, so you cannot use 5432 as your local port, so you elect to use port 5433 on your local machine.

From terminal, type...

ssh -L5433:localhost:5432 -p 2222 [email protected] 
password: password

Once at the prompt type 'top', which is a resource/process monitor commonly available on most unix platforms.  We do this to keep the connection active, otherwise ssh will disconnect an inactive connection. Now, you can minimize your terminal session.  Fire up your local pgAdmin in the regular way and add a host.

For this connection, you want:

Name: XYZ Company DB 
Host: localhost (or 127.0.0.1) 
Port: 5433 
Username: postgres (or admin, mfgadmin, even a regular xtuple user) 
For the password, enter the password

And then click OK.  The server should appear in pgAdmin, and 'pilot' (and other databases) should appear.  While the tunnel is active in terminal, you can also connect your xTuple Client (or openRPT, updater, etc) to localhost port 5433, db named 'pilot' and login.  You now have as good of access as you would if you were sitting in front of the screen of the server.

Scenario 2:

ABC Company needs you to connect and apply an update to their server named ERP (with internal IP 10.0.1.60).  Server ERP's PostgreSQL ports are not forwarded to the router, and the SSH service on that server is only accessible from the inside the LAN as port 22.  However, there is ssh access via port 2222 to a server named ACCESS which does happen to sit on the same network segment as the PostgreSQL server.  SSH can still save the day.  We do this by establishing a 'double tunnel' through ACCESS, into ERP.

ssh -L5433:localhost:5432 -p 2222 [email protected]

Enter your password, but instead of running 'top' on this server, create another tunnel.

ssh -L5432:localhost:5432 -p 22 [email protected]

Now, login to ERP and run 'top'.

Alternative to the above two step process: (This is one of the more dark arts type SSH commands... magic? voodoo? IDK, but it's pretty cool.)

ssh -L5433:10.0.1.60:5432 -p 2222 [email protected]

Now, login to ACCESS and run 'top'.

Connect your xTuple Client (or other application) to localhost port 5433.  You have now made a double tunnel, which looks something like the graphic below.

Scenario 3:

Suppose that your customer is hot for the latest and greatest xTuple release.  You need to run the xTuple Updater and create a sandbox environment.  This is probably the one use of an SSH tunnel, or any non-persistent connection, that I would advise against.  It is NOT a good idea to run the xTuple Updater application remotely in this way due to the likelyhood of losing the ssh tunnel due to network drop out.  This is a case where it really is a good idea to be physically on the server, or at least the wired portion of the LAN.  However, a fairly simple solution to this is to run a VNC session on the ERP (10.0.1.60) server and for security access only via the local network, which you can access with:

ssh -L5900:10.0.1.60:5900 -p 22 [email protected]

Then, start up your VNC client and connect to localhost port 5900 (the default VNC port).  You should be greeted with the VNC password prompt, then with the desktop of the server.

Conclusion:

I hope I have given you some helpful information on how you can begin to experiment with making remote connections to the servers you manage.  Besides saving precious bandwith by not relying on remote desktop display applications, you are ensuring that all traffic over the tunnel is encrypted which is especially helpful when dealing with potentially sensitive database information over the internet.  Also of note is that ssh (in one form or another) is available on every platform you are likely to encounter.  With a little practice, you'll soon leave your mouse and wysiwyg GUI editors and ge-gaw installers and programs for the white-on-black calm that is the terminal.