R SSH Tunnel MySQL

Question

I'm looking for a way that will allow me to use an SSH Tunnel to connect to a MySQL Server (as opposed to a file) within R; I'm assuming it'll require a combination of RCurl and RODBC, but I can't seem to get it to work properly.

I came across this post and this post that talk about utilizing SSH to connect to specific files or tables, but I'm hoping to use it as part of a Shiny app that will execute different SQL queries based on input from the user, which would require connecting into the server as opposed to specific files.

I'm assuming the code would look something along these lines x = scp("remote.ssh.host.com", "/home/dir/file.txt", "My.SCP.Passphrase", user="username"), but would I replace the "/home/dir/file.txt" piece with an odbcConnect() statement or replace it with the port number for the specific database I want to access?

Edit: The line I use for a regular odbcConnect() is odbcConnect(dsn, uid = "userid", pwd = "password"). Part of the problem is, I am developing it on Windows, but it will be deployed to a Linux server (handled be someone else) so I'm struggling to figure out what exactly will need to be used in my server.R code for connecting to the database.

Answer 1

Okay, so to test this on Windows, either grab Cygwin, or install OpenSSH so you can run ssh from the command line in Windows, like you would do in Linux.

Once you have ssh running on your Windows box, then try first making a tunnel through SSH. Run this from the command line:

ssh -f <server_user>@<server_ip>  -L <unused_local_port>:localhost:<database_remote_port> -N 

Obviously, replace everything in '<>' with the appropriate information. It will ask for the password, and remember that this isn't the database password, but the password to the server itself. Notably, the server_ip doesn't have to be the server with the database on it, just any server that is inside the proper subnet and that runs an SSH server, which is pretty much all Linux machines.

Now, setup an ODBC connection, except make the IP localhost, and the port unused_local_port. Now, try connecting to your new ODBC connection in R. If this works you're halfway there.

The next problem is the password, because you will have to enter a password to connect via SSH, but in R you won't be able to input it after a simple system command. So you have to setup some a public/private rsa key pair. Notably, this will make it so that anyone with access to your user/pass on your Windows box will now have automatic access to your server, so be careful. First, generate a SSH key:

ssh-keygen -t rsa

Don't make a passphrase, and save it in the default location. Now, create the directory for your public key on the remote host, and drop your public key in there.

# This creates a directory on the other machine if it wasn't already there. (Type in your password on the remote machine)
ssh <server_user>@<server_ip> mkdir -p .ssh
# This adds your public key to the list of accepted ones:
cat ~/.ssh/id_rsa.pub | ssh <server_user>@<server_ip> 'cat >> .ssh/authorized_keys'

Now try creating your tunnel again from the command line:

 ssh -f <server_user>@<server_ip>  -L <unused_local_port>:localhost:<database_remote_port> -N 

If it doesn't ask you for the password, you have succeeded in creating your keypair. Now you are ready to run your ssh command from the command line. But before you do that, try and kill your ssh command, so you can make sure that R is actually creating the tunnel, and you aren't just reusing an old one. You can do it through Windows Task Manager (Ctrl+Alt+Esc), and just right click and End Process the ssh.exe.

So, just run:

system('ssh -f <server_user>@<server_ip>  -L <unused_local_port>:localhost:<database_remote_port> -N')

And then connect to your new tunneled ODBC connection.