George Lantz

In this tutorial I will explain how I managed to access MySQL through SSH tunnel in my .NET application. This could come in handy for many reasons and is not limited to just MySQL. You could potentially tunnel almost any remote server daemon running behind a firewall. I originally created this technique for a Windows Desktop application so I could sync our company website inventory with the ERP software. For security reasons I only left the SSH and Apache ports open when I originally setup the web server.

Explanation of SSH
First, maybe I should explain a little about SSH (secure Shell). SSH is a network protocol that allows you to make a secure encrypted connection between two computers on a network or over the internet. SSH uses public-key cryptography to authenticate the remote computer and allow the remote computer to authenticate the user. Normally SSH is used to execute commands on the remote computer from an SSH client installed on the client computer, however, it also allows tunneling, forwarding of TCP posts and X11 connections as well as secure file transfer. The SSH server running on the remote computer listens for remote connections on port 22 by default. A client then establishes a connection and is authenticated using a username/password and/or shared key. SSH is most commonly used on unix/Linux operating systems, but because it's open source, it can run on most operating systems.

How the tunneling technique works
So how do we access a server daemon behind a firewall? We will need to create a tunnel through SSH. In order to connect our .NET app to the SSH server our application needs make an SSH connection to the to the remote computer, authenticate using password or shared key, then tunnel the MySQL port back to our client computer. Once we have done that our application can connect to the remote MySQL server just as if it was a port listening locally on our client computer.

How to use SSH in your application
I used the sharpSSH library for .NET to make SSH connections. This library is a C# port of the JSch project from JCraft Inc. Which means you could duplicate this same technique for Java as well. Here is how to do it.

1. Download sharpSSH and MySql Connector/Net 5.0
   A. First we need to download and unzip sharpSSH - http://sourceforge.net/projects/sharpssh. There are two versions to download, either the binary or source. For this tutorial you only need the binary version.
   B. We also need the MySql Connector/Net 5.0 - http://dev.mysql.com/downloads/connector/net/5.0.html
2. Create a new project
   Open Visual Studio and create a new Windows Application. I used C# for this demonstration. Since it is a .NET lib, you can use VB if you prefer. I named my project SSHTunnel.
3. Add the DLL Library References
   Right click on References in the Solution Explorer and click on Add Reference, Click on the Browse tab. Browse first to the sharpSSH directory. There are three dll's - DiffieHellman.dll, Org.Mentalis.Security, and Tamir.SharpSSH. Next browse to bin in the MySql Connector directory and add the MySql.Data.dll.
4. Add UI component.
   A. Add two TextBoxes and two Labels to the form. The text for the first label should be remote port. The socond should be local port. Name your TextBoxes "textBoxRemote" and "textBoxLocal".
   B. Add two buttons to the form. Set the text of one button to Connect, the other disconnect.
   C. Add a StatusStrip to the bottom of the form. Add a status label to the StatusStrip. Change the text to "Not Connected".
   D. Add a label and change the text to "SQL QUery"
   E. Add a Textbox and name it "textBoxSQL.
   F. Add a Button and change the text to "Execute"
   G. Add a DataGridView to the form and resize to fit.

   You should have a screen like below.
   

5. Add the code.
   A. Import the libraries.

   	using System.Net;
   	using System.Net.Sockets;
   	using System.Globalization;
   	using Tamir.SharpSsh.jsch;
   	using MySql.Data.MySqlClient;

   B. Add a UserInfo class for handling login information it extends the sharpSSH UserInfo Class.

    	public class MyUserInfo : UserInfo
   	{
   		/// <summary>
   		/// Holds the user password
   		/// </summary>
   		private String passwd;
    
   		/// <summary>
   		/// Returns the user password
   		/// </summary>
   		public String getPassword() { return passwd; }
    
   		/// <summary>
   	 	/// Prompt the user for a Yes/No input
   	 	/// </summary>
   	 	public bool promptYesNo(String str)
   		{
   	 		return true;
   	 	}
    
   		/// <summary>
   	 	/// Returns the user passphrase (passwd for the private key file)
   	 	/// </summary>
   	 	public String getPassphrase() { return null; }
    
   		/// <summary>
   	 	/// Prompt the user for a passphrase (passwd for the private key file)
   	 	/// </summary>
   	 	public bool promptPassphrase(String message) { return true; }
    
   		/// <summary>
   	 	/// Prompt the user for a password
   	 	/// </summary>
   	 	public bool promptPassword(String message) { return true; }
    
   		/// <summary>
   	 	/// Shows a message to the user
   	 	/// </summary>
   	 	public void showMessage(String message) { }
    
    	}

   C. Add the class variables to store the connections

    
   	private Session session;
   	private MySqlConnection conn;

   D. Add the connection button event handler code

   	private void button1_Click(object sender, EventArgs e)
   	{
   		try
   		{
   			//Create a new JSch instance
   			JSch jsch = new JSch();//Create a new SSH session
   			string host = "yoururl.com"; //url
   			string user = "sshuser"; //ssh username
   			string pass = "sshpass"; //ssh password
   			int sshPort = 22; //ssh port
   			int rPort = Convert.ToInt32(textBoxRemote.Text);
   			int lPort = Convert.ToInt32(textBoxLocal.Text);
   			int port = Convert.ToInt32(sshPort);
    
   			session = jsch.getSession(user, host, port);
   			session.setHost(host);
   			session.setPassword(pass);
   			UserInfo ui = new MyUserInfo();
   			session.setUserInfo(ui);
   			session.connect();
    
   			//Set port forwarding on the opened session
   			session.setPortForwardingL(lPort, "localhost", rPort);
   			if (session.isConnected())
   			{
   				this.toolStripStatusLabel1.Text = "Connected";
   				this.button2.Enabled = true;
   				this.button1.Enabled = false;
   				try
   				{
   					string dbhost = "localhost";
   					string dbuser = "dbuser";
   					string dbpass = "dbpassword";
   					string dbdatabase = "dbname";
   					string connStr = String.Format("server={0};user id={1};
   						password={2}; database={3}; pooling=false",
   						dbhost, dbuser, dbpass, dbdatabase);
    
   					// Create a MySql Connection
   					conn = new MySqlConnection(connStr);
   					conn.Open();
   					conn.ChangeDatabase(dbdatabase);
   				}
   				catch (Exception ex)
   				{
   					MessageBox.Show(ex.Message);
   					session.disconnect();
   					this.toolStripStatusLabel1.Text = "Ready";
   					this.button1.Enabled = true;
   					this.button2.Enabled = false;
   				}
   				finally
   				{
   					conn.Close();
   				}
   			}
   		}
   		catch (Exception ex)
   		{
   			this.toolStripStatusLabel1.Text = "Error";
   			this.button1.Enabled = true;
   			this.button2.Enabled = false;
   			MessageBox.Show(ex.Message);
   		}
   	}

   E. Add the Disconnect event handler code

   	private void button2_Click(object sender, EventArgs e)
   	{
   		session.disconnect();
   		this.toolStripStatusLabel1.Text = "Not Connected";
   		this.button1.Enabled = true;
   		this.button2.Enabled = false;
   	}

   F. Add the Execute query button event handler code

   	private void button3_Click(object sender, EventArgs e)
   	{
   		try
   		{
   			//MySql Example
   			MySqlDataReader reader = null;
   			MySqlDataAdapter da = new MySqlDataAdapter(textBoxSQL.Text, conn);
   			MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
   			DataTable data = new DataTable();da.Fill(data);
   			dataGridView1.DataSource = data;
   		}
   		catch (Exception ex)
   		{
   			MessageBox.Show(ex.Message);
   		}
   	}

6. Run the project.
   MySQL listens on 3306 by default, unless you have changed the port your MySQL server listens on then set the remote port to 3306. For this example set the local port to 3306. Click connect and wait for the status to say connected. Type in a simple SQL query and click execute.Note: I was getting a Unable to convert MySQL date/time value to System.DateTime exception. This can be remedied by formatting the date in the query. SELECT products_id, products_quantity, products_model, products_price, DATE_FORMAT(products_date_available, '%m/%d/%Y') AS products_date_available from poducts;

You can download the sample I have in it's entirety - SSHTunnel.zip