How to establish ODBC connection through ssh tunnel?

Question

I have SSH access to an iSeries (DB2 Database) through a firewall. I am trying yo make queries to a db in the machine using ODBC.

I have found several tables explaining what ports are used by the protocol but don't know what to do with this information. Is there any way of redirecting traffic to the ports used by odbc if I have ssh access to the machine?

http://search400.techtarget.com/answer/What-TCP-ports-are-used-by-ODBC-to-connect-to-the-DB2-400

Solution

So, assuming you have a database client running on your PC, then you can create an ssh tunnel that sets up ports on your PC to reflect the ports that are running on the database server. Then direct your client to query your local host. Here's how to set up the tunnel for one of the ports, say 8471:

ssh -L 8471:localhost:8471

In this case "localhost" refers to the server, not your PC. You can replace "localhost" with the server ip address if you want.

After setting up this tunnel, if you "telnet" to port 8471 on your PC, it will connect you to the database server port 8471.

You'll have to repeat this for each of the ports in the list.

You may run into a problem if the database server requires its own hostname in the connection string. If it does, you will need to trick your client into thinking that the hostname of the PC is the same as the hostname of the db server.

Comments

[cabe56]

The problem is that the server containing the DB is behind a firewall. I would have to redirect all ports in the firewall to those on the server, which would not allow for any other odbc connections through the firewall. Dunno about security issues, if anyone cares to comment that would be appreciated.

[Univ426]

As Michael has described, the idea here is that you're going to send/redirect the ODBC traffic - which would typically be going over the ports you found listed - over a port that isn't blocked by your firewall - in this case over the SSH protocol on port 22. You shouldn't have to make any modifications to your infrastructure beyond that SSH redirect

[cabe56]

Right now I'm fwding a port to the firewall port, which fwds to the db port: <my port> -> firewall port -> machine w/ db port 22. What you mean is that if I tell my odbc client to use localhost through <my port> the db machine will handle the rest?

[Michael Martinez]

@cabe56: (1) establish the ssh connection to dbserver in whatever way you like (2) the -L option on ssh will forward whatever PC port you want, to whatever dbserver port you want. (3) for testing, telnet PC port X. this will connect you to dbserver port Y. (4) firewall has nothing to do with it