Thanks to this post as a starting point, I managed to connect my Access 2007 app directly to a back end MYSQL in the cloud via a secure SSL connection (not on top of SSH tunnel).
Here's the basics
Step 1: Generate and Enable SSL certs on the backend mysql server
See Reference here: lowendbox.com/blog/getting-started-with-mysql-over-ssl/
Step 2: Download and install the MYSQL odbc connector
(dev.mysql.com/downloads/connector/odbc/]) Make you u download the 32 bit version (64 bit driver don't work on Access)
Step 3: Configure ODBC in windows
Goto Control Panel, Administrative Tools, ODBC Data Sources 64 Bit.
- a. Goto System DSN tab
- b. Add Mysql connector (Unicode) 32 bit
- c. Put in the client cert, client key and ca cert
- d. Give it a name (e.g. MySQL_backend)
Step 4: Connect Access to the MYSQL
In Access, Go to External Data, ODBC Database, choose Machine Data Source tab, and point to the name in 3d. above.
i. When generating the CA cert in Step 1, make sure the Common Name is different
from the ones used for the Server and Client certs
ii. To ensure your certs are ok, verify it has no errors, otherwise it will fail to connect in Step 3.
The command line to verify in linux:
openssl verify -CAfile /etc/mysql/ca-cert.pem /etc/mysql/server-cert.pem /etc/mysql/client-cert.pem
iii. You also need to ensure SSL for your MYSQL is enabled using this MYSQL command:
SHOW VARIABLES LIKE '%ssl%';
It should show ENABLED not DISABLED.
iv. If it is DISABLED, make sure the certs file owner and group are 'mysql'. Use
chown mysql *.pem
chgrp mysql *.pem
and restart your mysql service.
Hope this helps!