Thursday, October 2, 2008

Accessing Production Databases (or Anything Firewalled or IP Restricted) via SSH Tunnels

If you need to access a production service/database remotely where you don't have direct access to port itself on that box, but you have ssh access to either the server itself or to another server that has access to it, then you can use ssh tunnelling to get there. First make sure you can ssh to the server you will tunnel through:
ssh username@server
assuming that works exit out again:
(or use ctrl-D to exit) Then setup the tunnel
ssh -L (local port):(target server):(target port) (username)@(server)
This basically starts a tunnel that routes all traffic through whatever local computer port you want to use "(local port)" through SSH via some server/computer "(server)" that has access to the server/port you are trying to access "(target server):(target port)". Then, instead of trying to connect (in your DB client, etc.) to "(target server):(target port)", you connect to "localhost:(local port)". This works great in OS X, Linux, or anything that has ssh client that supports the "-L" command-line option. Reasons you might not like this approach: * You have to remember that the terminal window needs to stay open. * SSH connect timeouts could end up closing the connection. This could be very, very bad if it happened while you were trying to execute updates to the database! However, it seems that it will stay alive as long as you use the default behavior of the client's ssh terminal window to stay open when processes are running, and the ssh server hasn't been configured to kill lingering connections on auto-logout. * More points of failure for the connection (+1 local ssh client/terminal window, +1 ssh server). * It makes it more difficult for DBAs, etc. to know what machines are actually connecting, since all will seem to come from the box you are tunneling through, "(server)". * You require access to SSH to some server that has production access. * Intentionally opening up SSH access for the purpose of tunnelling could make the environment less secure (except for the better encryption that could be provided by the tunnelling connection itself vs. direct connection). The reasons you might want to do this are: * You work in an environment where the admins don't mind giving SSH access to a server that has access to the other production service, but it is additional headache for them to have to maintain a large number of IPs for people to access a service/database directly. * It obscures access to the production DB just a little. * It may provide a bit more security in terms of better encryption of passwords via the tunnelled SSH connection. Sending passwords via direct connection of DB client can sometimes/often be less secure. The reasons it is important for people to know about this are: * It could be a security issue if you are letting people SSH to a server, even with very little access to do anything on the server, because they could easily circumvent IP restrictions or firewalls that block direct access to certain ports in a production server, as long as you can SSH to a server that is NOT blocked.

No comments: