October 22, 2004

How to Securely Connect MS Access to a Remote SQL Server

Today I had an interesting conversation with a contract programmer that had me thinking a bit. I have used an interesting technique for a while now that most people don't know that makes data manipulate on a SQL server quite nice... and secure. For this conversation I am going to talk about bridging MS Access to connect to a mySQL database securely... over SSH.

The conversation started on a tangent when I made the comment I don't need to write a web front end to massage some data as I could Access. The obvious question from the programmer (and rightfully so) was 'umm... thats not really secure is it?'. I kind of brushed it off and said ya, but didn't explain myself very well. So I figure why not do that now with a useful blog entry on the subject. :)

Although more recent versions of most DBMS are now offering options for connecting over SSL, that hasn't always been the case. And in my case, I'd rather use SSH anyways... I have better control over it as its allowed through the firewall as normal SSH traffic.

So how do you do it? How do you get a Windows machine running MS Access to connect up to a Unix/Linux server running mySQL to access data? It's pretty simple actually.

  1. Install putty, or any of your favorite SSH clients that support forwarding through the tunnel.
  2. Install mySQL ODBC driver
  3. Create the right permissions on the database that you wish to access.

    GRANT SELECT,INSERT,UPDATE ON yourdb.* TO 'bob'@'localhost' IDENTIFIED BY 'your_password';

  4. Start Putty and create a new connect to the server using a local port forward through the tunnel and point it to localhost (ie: port=3306, destination=localhost:3306... see screenshot below)



  5. Connect to the server with SSH
  6. Start MS Access
  7. Create new blank database. Call it something resembling the mySQL database so you can remember it.
  8. Go to the File menu and select: File->Get External Data->Link Tables, and change the "Files of type" to ODBC Databases()
  9. When the Select Data Source dialog pops up, select Machine Data Source and click the "New" button
  10. Create a new System Data Source and click Next
  11. Select the MySQL ODBC driver from the list and click Next, and then Finish
  12. When the MySQL ODBC driver dialog pops up, enter in a unique datasource name, set the host to 'localhost' and the database name to the foreign database you set the GRANT perms to. Set the user to 'bob' (or whatever you set the username to) and enter the password.
  13. Click the 'Test Data source' button. It should have worked.
  14. Click Ok
  15. When the Link Tables dialog pops up, select the Tables you want.
  16. Click Ok
  17. Query and manipulate the data as you like!

Now what happened? Well, what you did is set the Access database to connect to localhost... which was then forwarded across the SSH tunnel to the remote server, which then also made a localhost connection, which you allowed in the system.

Now here is a trick for some people that don't get this working first time. Depending on how you do name resolution, when setting the GRANT perms you may need to set the host as the fully qualified name as the system sees it. ie: hostname.domain

Thats it. Now you can securely use all the features in Access to massage the data on mySQL without the data being snooped on the wire.

Posted by SilverStr at October 22, 2004 05:53 PM | TrackBack