How to Securely Connect MS Access to a Remote SQL Server

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. 
[Dana Epp's ramblings at the Sanctuary]

Leave a comment