Fifteen Twenty What?

When you're working in a network environment with many servers and a maze of firewalls, you quickly learn how to use SSH tunnelling. SSH tunnelling allows you to forward TCP traffic from a specified local port, through the server you are connecting to, to some remote host reachable by the server you are SSHing to. For example, you may have a web server on a remote machine, but your firewall will only allow SSH (port 22) and not HTTP (port 80). To get around this, you could open port 8080 on your local machine and tunnel it through SSH to the remote web server: ssh -L 8080:localhost:80 username@remoteweb.

You can also use the -R option to open a port on the remote server and forward traffic to any specified host. So if you want to open port 80 on some public server and forward that traffic via SSH to an internal web server (that you are currently on), you could do: ssh -R 80:localhost:80 username@external.

This works great for most cases, but I was recently trying to connect to an Oracle database server via SSH remote tunnelling. It didn't work. My connection kept timing out. I forget the exact TNS error that I got, but it was obvious that the network connections were not working.

What I Did:
With the help of ethereal, I quickly learnt a little about Oracle's Transparent Network Substrate (TNS) protocol by looking at the network traffic. Oracle can be configured to use what I think is called Direct Connection Handoff. In this mode, a client creates an initial connection on a specified port (usually 1521) and then during the connection setup, the Oracle server opens a new port (like say, 3064), specifically for the session, and tells the client to connect on it. I saw some info saying that this parameter is set by an entry in listener.ora such as "DIRECT_HANDOFF_TTC_<listener> OFF". I also found out that you can get special proxies to handle Oracle connections using Direct Handoff, but I felt confident that with a little effort, I could manage to get such a proxy going myself.

The network security guys were doing quite a good job of making life difficult for me, and I had to do no less than 3 hops to get from my laptop to the Oracle server that I wanted to connect to. I had an SSH tunnel listening on local port 1521 that forwarded traffic through to intermediate 1 (I1). I then had intermediate 2 tunnelling from port 2521 on I1 and connecting through to the DB server on port 1521. I think I better dance now. I mean, I think I better draw a picture.

So, I jigged the tnsnames.ora file on my laptop to tell it to connect to localhost:1521 rather than DB:1521. Then I opened a second SSH tunnel from my laptop to I1 using SSH -L 1522:localhost:2522 user@I1. The first tunnel would be used for the original connection to port 1521 on the server, and the second tunnel would be used for the session hand-off. I then had to write a proxy. It had to:

1. Listen in on any connections to DB:1521
2. Parse the response to get the hand-off port
3. Open a new SSH session from I1:2522 to DB:
   
4. Rewrite the Oracle response to tell the client to connect to localhost:1522 for the session connection

I didn't want to have to write a full-blown program to manage sockets and threads because I knew that all I needed to do is get into the existing streams and put in a simple filter. The unix programming philosophy is based entirely on simple filters and using pipes to join them all together. I knew there had to be a simple tool that allows you to include TCP streams in all of this. With a little searching, I found out about netcat. I checked the cygwin packages, and sure enough it is there, so within about 20 seconds, I had netcat installed and running. I added a few lines of python code, added some named pipes, and I had a solution!

Standard unix pipes are great for chaining processing where the data flows in a single direction, but when you are dealing with TCP streams, they can't handle the 2-way flow of data. This is where named pipes are useful. You can create separate process chains for filtering the streams in each direction (using standard pipes to create each process chain) and use named pipes as the endpoints to link the 2 chains together.

For what I was doing, I created 3 named pipes:
> mkfifo c2s # this is the data from the client to the server
> mkfifo s2p # this is the data from the server to the proxy
> mkfifo p2c # this is the data from the proxy, back to the client

The python proxy script sits between s2p and p2c and rewrites the data, as well as dynamically spawning a new SSH process.

The diagram below attempts to show what is happening. It's a bit crazy, but I can't think of a clearer way to show what is happening (maybe a sequence diagram would work better).

And here is the python script:

#!/bin/python
import sys
import os
import time
import re

m = re.search(r'(.*?)\(HOST=(.*?)\)\(PORT=(.*?)\)', sys.stdin.readline())
os.spawnl(os.P_NOWAIT, '/bin/ssh', 'ssh', '-N', '-R', '2522:%s:%s' % (m.group(2), m.group(3)), 'user@I1')
time.sleep(5)
sys.stdout.write('%s(HOST=localhost)(PORT=1522)' % m.group(1))

What I Learnt:

Oracle TNS has a hand-off mode . This is useful to remember if you ever have network firewall problems connecting to Oracle.

Netcat is cool. I have used a Java utility called TCPReflector for quite a while, and I have often found it useful. I've even modified the code quite a bit to act as a HTTPS tunnelling proxy and a number of other things, but using netcat, named pipes, and a little bit of scripting is better by far. By taking advantage of the unix piping philosophy, it is simple to create a network filter pipeline and use small existing tools to construct exactly what you want very quickly.

Cygwin (specifically Unix pipes) is cool. Cygwin is always the first thing I install on a windows machine. I have always used a windows machine in my day to day work, but when I found out about and started using grep, sed, awk, find and all the others, it was easy to become a unix believer.

Python is a bit tricky to use to spawn other processes. It took a bit of fiddling to get python to spawn the ssh process that I wanted. I think that you just need to add a dummy first parameter when you call os.spawnl(). I'm guessing that it has to do with how programs (such as ssh) read in arguments starting with argv[1] (since argv[0] is normally 'ssh' or whatever the user typed to kick off ssh). Python's spawnl(mode, path, ...) must start the process that you specify and then pass in the arguments you give, with the first argument at argv[0]. If you make your first argument the same as , then things should work OK. Once I got python spawning ssh, I also had to add the -N option to ssh to make it run as a background process. If I didn't add -N, I got message "Pseudo-terminal will not be allocated because stdin is not a terminal.".