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.
- 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 - 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. - 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. - 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.

- 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); } }
- 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
September 25th, 2007 at 6:30 am
Hello,
Thanks for this great example. I have tried your code sample, but it won’t work for me. For some reason when I connect, it throws an exception with the ‘Auth failed’ message. My U/P are OK, since I can connect to MySQL using Navicat 7.24.
Do you know what might be the problem?
I appritiate any help you could provide…
Thanks in advance.
September 25th, 2007 at 8:43 am
Is the SSH domain, user and password correct as well? If you get the error when clicking connect, that may be an SSH authentication problem. It must connect through SSH first before you are able to run the query. Since it is kind of a complicated connection, I would suggest testing the connection with an SSH client such as Putty just to make sure you can access SSH server and set up a tunnel for using MySQL. I had to do this a couple times during the the coding process to answer whether it was my code or a connection issue. Start by just connecting to SSH server without tunneling. If you can log in then try again using the tunnel (tutorial link below). If the tunnel is working with Putty than it’s the code.
Also, you mentioned Navicat. Navicat is where I got this idea of having built in SSH support. You could also of course test this process using it, although I would recommend still using Putty so you can tell exactly what point the failure is occuring, if you get a failure. When you tested did you test over SSH, or just a straight connection?
Another thing just came to mind, I was stumped for an hour when I had trouble connecting to MySQL. It kept saying authentication failed, turns out I forgot about the MySQL version I had running locally on the same port
I stopped the local service and everything worked just fine.
Putty Download:
http://www.chiark.greenend.org.uk/~sgtatham/putty/
MySQL over Putty Tutorial:
http://www.vbmysql.com/articles/security/connecting-the-mysql-gui-tools-to-a-remote-server-through-a-firewall/
Hope this helps, if not let me know. Good Luck!
September 25th, 2007 at 7:25 pm
MyUserInfo needs to implement:
public string[] promptKeyboardInteractive(string destination, string name, string instruction, string[] prompt, bool[] echo)
{
string prmpt = prompt != null && prompt.Length > 0 ? prompt[0] : “”;
passwd=InputForm.GetUserInput(prmpt, true);
return new string[] { passwd };
}
If SSH uses keyboard-interactive Authentification, this function needs to be there, otherwise Auth fails.
September 25th, 2007 at 8:10 pm
Thanks Kaoru,
I didn’t need that when I wrote my implementation and didn’t think about that when I wrote this mini how-to, so I hope that did not cause you too many problems, my apologies if it did. That may be why krizvan was experiencing problems. I am sure this will help point others in the right direction. Thanks.
September 25th, 2007 at 8:31 pm
It gave me a minor headache, but wasn´t a big problem, merely posted it for the case if others have the same problem
What I couldn´t figure out yet, when I exit my application, the SSH Session is kept open and prevents my application from exiting, even though I called session.disconnect();
netstat on the server says the Session is still open and on Time_Wait.
November 26th, 2007 at 5:18 pm
Great job laying this out. This is exactly what I am doing currently and it works okay except a couple things. I was wondering if i could get your input.
1. Application Hang
When I make a successful connection and then want to close the program i call session.disconnect() and continue closing the program. But the program won’t close, do you have any ideas of what i could do? I have also tried calling disconnect, delPortForward, and setting the variable to nothing still all hang.
2. MySQLCOnnection.Open hang
When I create a successful SSH connection the call the “MySQLConnection.Open method it sometimes hangs on the open and won’t timeout or anything. This is easily reproduced by calling open on 2 different threads.
Any help would be great,
Jordon
November 30th, 2007 at 8:20 am
Same problem, the application won’t close…
November 30th, 2007 at 9:28 am
I will look into this guys, I have been very busy lately so i apologize for not getting back to you right away. You may try searching for the problem in Google. I am sure the problem stems from sharpSSH. As soon as I can, I will post a fix if I can find one.
November 30th, 2007 at 3:30 pm
Using Environment.Exit(0) instead of Application.Exit() will close the application properly, but i don’t know if it’s the right way…
December 30th, 2007 at 8:51 am
I can create Tunnel and connect to mysql server with Putty but i cannot connect to server with this Sample project.
I also implement code for Keyboard Interactive Auth., anyway it doesn’t work
Do you have any idea?
February 8th, 2008 at 8:20 pm
I am getting the application hang when I close the app with the system close button on the form, when I use Application.close it shuts down fine, but now when I simply close the mainform.
Does anyone know of another SSH implementation for C#?
February 13th, 2008 at 1:36 pm
I tried using the Environment.Exit(0) and it closes now when I run it on my computer, but if I run it from another computer, it still hangs.
February 27th, 2008 at 9:25 am
Hi,
thanks for the article, exactly what i was looking for
But the problem with the application hang is there, have you found a way to completely close the ssh connection?
May 1st, 2008 at 11:46 am
If you set the “passwd” variable in the UserInfo object to the actual SSH password used for connecting, the SSH connection will close properly if you call disconnect().
private String passwd;
public SSHUserInfo(string pwd)
{
passwd = pwd;
}
Something like that for example.
August 7th, 2008 at 1:01 pm
This may be a simple question to some, but I need to have Mysql running on my local machine at the same time as connecting to MySql on a remote server. Now as we know this will not work with the example above. I get the example working when I close down my local MySql so that’s OK.
How can I set the above up so that I can have my local MySql running at the same time as connecting via SSH to my remote MySql.
I’ve tried putting different values in the local port value, but I then get a ‘access denied’ error on my remote MySql.
Any suggestions or further reading appreciated.
Thanks Jonathan