Setting up the SSH tunnel using Putty
We will first need to configure a Putty session for port-forwarding to connect via the SSH protocol and specified server address:
Once we have configured our host and protocol, we can move on to setting up the tunnel, by specifing a destination host and port. SSH tunnelling has the unique ability to forward ports beyond the SSH server machine, which may be useful for situations where the MySQL server does not have SSH installed but has a private link to the SSH machine:
Our local machine connects to the SSH host through the firewall and the SSH host then forwards the session on to the database. When specifying port forwards, use a host:protocol syntax to specify where the traffic should end up. Assuming that you have SSH on the MySQL server machine, as all Zolotek machines do, this would mean the use of localhost:3306.
In this example, we are specifying that port 3306 on the local client machine should be forwarded to port 3306 on the server machine. It should be noted that the two port numbers may be different. For example, if you have a MySQL running on port 3306 on your local machine then you need to specify a different port for the start of the tunnel - port 3307 is very commonly used on the local machine for this reason.
Once the port forwarding directives have been specified, Putty can save the session to make it available for repeated use. Back on the session menu simply specify a saved session name and click 'save' to add this to the list of saved sessions:
Once the session has been created and saved, it is easy to add a shortcut to your Windos desktop. Right-click on the desktop and choose New > Shortcut. Configure the shortcut and assign the target as x:\path\to\putty\putty.exe -load MySQLTunnel, for the example above.
Then simply double-click the icon and provide a username and password when prompted. A shell session will open and the tunnel will be established - close the Putty window to close both tunnel and shell session.
Using the SSH tunnel with MySQL
Using your new SSH tunnel is as simple as pointing your application at the local port on the client side of the tunnel. Lets say the following is your connection string:
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& "PORT=3307;" _
& "DATABASE=test;" _
& "UID=testuser;" _
& "PWD=12345;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
The application then connects to the remote server through the tunnel, ensuring security and allowing connections through firewalls where SSH connections are available. You may also reconfigure DSN based connections using the same approach in order to allow ODBC applications such as Microsoft Access to also connect to the remote database.