MrZander MrZander - 4 years ago 347
MySQL Question

MySQL ODBC - How to connect Access 2016 to remote server via SSH

I have a Linux-based web server running with the LAMP package. For security reasons, the sever only allows logins via SSH and denies regular password login attempts. I want to use the MySQL ODBC driver within Microsoft Access 2016 to connect to this server for data analysis purposes, but I do not see an option in the ODBC Data Source configuration to add a source that works with SSH.

My question is, is there a way to configure the ODBC tool to work with SSH, or is there a tool that can bridge the gap? Thanks.

Additional Facts:

  • The server runs Ubuntu Server 16.04.

  • As far as I know, the server is listening to port 22 and 80 only.

  • The local computer runs Windows 10.

  • The computer can connect to the server terminal with PuTTY using private key SSH.

  • The computer has MySQL Workbench that connects to the server's database with the same SSH authentication.

  • I have the full suite of PuTTY tools installed, and some Googling around indicated that PLINK would be useful for a similar scenario, but I'm unsure how to go about that route (if PLINK is the answer, then a step-by-step answer would be much appreciated).

  • Both the server and the computer are on the same local network

  • My end goal is to have linked tables in Access on my local machine that link to a particular MySQL database's tables on the server.

Answer Source

Open PuTTY, in the left navigation panel, Go to Connection > SSH > Tunnels, add a new fowarded port(let's suppose your MySQL server is listening on 3306): PuTTY - add a new fowarded port

From now on, anything sent to localhost:3306 on your local computer will be forwarded to your MySQL server, it looks as if the MySQL server is installed on your local computer, happily coding!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download