SSH Tunneling (Port Forward via VBA) W/O PUTTY

Recently i faced a situation where I had to connect to MySQL server via VBA to extract the data for reporting.
(Sounds simple .. Think again..)

Standing inbetween was a technique commonly used in banks and secure insitutuions to establish secure communication to remote location called "Tunneling". In order to tunnel to remote location for establising MySQL database connection (via technique called PORT Forwarding) in which a local port of YOUR PC is MAPPED to connect to remote location port via TUNNEL using tools like PUTTY.

Confused...

Let me make it simple:

1. I need to connect to MySQL DB located in remote location via VBA.
2. The remote location details for MySQL server are as follows:

Remote Server IP : xxx.xxx.xxx.xxx
Remote MYSQL Port: 3306 (Standard MySQL Port)

3. I open putty and connect to remote server via PUTTY with password verification and forwarding my local port to Map to remote port

Local Mapped Port : 8585 (Can be any number)

This local port now represent the MySQL service on remote machine via secure tunnelled channel on which we would be required to obtain the data.

Now my trouble was the existing practise of invoking the Putty via VBA Shell  command for establishing the channel and killing the process for closing the same was unreliable as there is no way to confirm if the PORT forward had been established unless via On Error cluases in VBA for connection open menthod.

In order to over come this issue i exposed the methods from C# library (Tamir Gal SSH.NET) to VBA and also provided a Fix for the library usage for the corporate environment in which the Admin priveliges are least granted for the users to deploy this solution. This solution is deplyed and useable in the folllowing manner:

1. Save the complete unzipped folder to your C:\ drive.

2. Run the included .reg file in the package to create the entries for the dll in the registry (where in the secure corporate environments where access to the local machine branch of registry is prohibited 'HKLM' this .reg file registers the classes in the 'HKCU' branch this allowing easy deployment for secure environments.

3. Now from the VBA editior the library can be refrenced as Tools -> Refrences



4. And can be used before the connection open to establish the PORT forward via boolean to indicate the sucess/failure of the operation.

Sub TunnelExample()
    Dim SSh As SSHTunnelVBA.VBATunnel
    Dim bln As Boolean

    Set SSh = New SSHTunnelVBA.VBATunnel

    bln = SSh.TunnelConnect("UserName", "Password", "Source/RemoteIP Address",
 "LocalPort No to Forward", "Local Port IP", "Remote Port to map")

    If bln Then

        'Port forwarded succesfully.

    Else

        'Port forwarded error.

    End If

    bln = SSh.TunnelDisConnect

End Sub

5. And finally the tunnel can be disconneted via following command again returning boolean.

I hope you would find this solution very useful as it does its job very well and takes away the complexity of PUTTY shell commands via VBA and unreliability making our VBA code more robust, clean and secure.


Download solution


Refrences:
Tunneling : http://en.wikipedia.org/wiki/Tunneling_protocol
Port Forwarding : http://en.wikipedia.org/wiki/Port_forwarding
Putty : http://www.chiark.greenend.org.uk/~sgtatham/putty/
Tamir GAL SSH.NET : http://www.tamirgal.com/blog/page/SharpSSH.aspx