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 192.168.1.xxx.
- 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.
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):
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!