Avatar of drewkilleen
drewkilleen
 asked on

How do I make a secure ODBC connection to a mysql server on a remote LAN using SSH

I have a headless Ubuntu mysql server on a SBS 2008 centered LAN.  I would like to use Excel to query tables on my mysql database remotely over the internet securly.  From what I can find it seems to be a two step process; establish an SSH connection to the mysql server then connect the ODBC as a local connection (127.0.0.1:3306). There is some router and firewall magic that I just can't fathom.  I have a Linksys router.  I've tried port forwarding but I didn't get it right because I can't even make the ssh connection work from a remote location.  The Ubuntu server accepts ssh logins from my LAN over port 22.  Any guidance would be greatly appreciated.
MySQL Server

Avatar of undefined
Last Comment
Nem Schlecht

8/22/2022 - Mon
Nem Schlecht

So, you can SSH to your Linux box, but the port forward doesn't work?  Is port forwarding turned on on the server (is "AllowTcpForwarding" turned on in the Linux box's sshd_config file)?

What are you using on the Windows side to SSH to your Linux box?  If you're using putty, ssh tunnels are pretty easy to set up.  You do *NOT* need to set up anything special on your router if you're already able to log into your Linux box via SSH.  All port forwarding traffic will go through port 22 (pretty much the whole point, since that's the encrypted channel).

In Putty, go to "SSH" -> "Tunnels" (left-hand side).  Under source port enter in "13306" (usually its not a good idea to use the same port as the service, in case you ever want to run MySQL on your workstation).  Under destination, enter in "localhost:3306".  Go back to the main screen, enter in the destination host and click on "Save" so you don't have to retype everything in again.  Make the connection, then leave your window alone (minimize it).  Then, just set your ODBC connection to connect to port 11306 and you should be ready to go.
drewkilleen

ASKER
Almost there!  My sbs2008 was picking up the ssh login on port 22 so I set my router to forward port 23 to my Ubuntu server (mysql server) and set openssh there to listen on 23.  It seems to work.  I set up Putty as you indicated and I was able to establish the ssh session from a remote pc (outside my LAN). So far so good.  What I don't get is how to point the ODBC on the remore PC to the ssh session I established.  I set the port to 13306, but I don't know what server/id/pw to enter in the ODBC configuration.  I tried several combinations and even downloaded an old mysql odbc (3.51) because I've had some issues with 5.1 previously.  I think I'm close.
Nem Schlecht

Yup.  You're close. ;-)

I'm attaching a screenshot.  In the Putty window, I've set up my port forward.  Note that I'm forwarding to "localhost" on the remote side.  I know this gets confusing, but think of it this way.  The local port, in this case 13306 and shown with "L13306" is the port on the current (in this case, Windows) machine.  The other host/port combo is used *on the other side* of the tunnel.  For example, if you were logging into your Linux host via ssh, but your database was on another Linux box sitting right next to it, you wouldn't use "localhost:3306" but "database-server:3306" (or whatever the name of your other Linux box is).

For MySQL/ODBC, I just need to connect to localhost 13306 (as shown).  Once you set up your connection via the MySQL ODBC connector, you should be all set.  Just remember to open up Putty and connect
mysql-odbc.png
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Nem Schlecht

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
drewkilleen

ASKER
Thanks for the great solution; no muss, no fuss, no problems.
Nem Schlecht

Yeah, SSH tunnels are awesome.  I've chained 3 different tunnels together to get from a laptop to a workstation (both at home) to a server at my office tunneled to an offsite server that (finally) had access to the MySQL database I needed to get to.  A little bit of setup work, but the SQL GUI on my laptop worked just fine. ;-)

Glad you got things working. :)