Access MySQL Through SSH Tunnel in a Windows .NET Application
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
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 25, 2007 at 6:30 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 25, 2007 at 8:43 am
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 25, 2007 at 7:25 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 25, 2007 at 8:10 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.
September 25, 2007 at 8:31 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 26, 2007 at 5:18 pm
Same problem, the application won’t close…
November 30, 2007 at 8:20 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 30, 2007 at 9:28 am
Using Environment.Exit(0) instead of Application.Exit() will close the application properly, but i don’t know if it’s the right way…
November 30, 2007 at 3:30 pm
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?
December 30, 2007 at 8:51 am
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 8, 2008 at 8:20 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 13, 2008 at 1:36 pm
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?
February 27, 2008 at 9:25 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.
May 1, 2008 at 11:46 am
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
August 7, 2008 at 1:01 pm
thanks for this mini how-to, so many people have problems i have a solution, for keyboard interactive ssh.
1.- declare class MyUserInfo like that:
public class MyUserInfo : UserInfo, UIKeyboardInteractive
2.-add keyboard interactive method to MyUserInfo class:
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 };
}
December 7, 2008 at 4:48 pm
Thanks!,
December 13, 2008 at 12:16 pm
Firstly, a big thank you to George Lantz. This tutorial is awesome. Well written and easy to follow. This was a huge help to me. I was able to get my C# app to connect to a MySQL database via a SSH tunnel (which I wasn’t even sure was possible before I ran across your blog).
Like others here, I’ve had the same problem with the application hanging on exit. I tried the method from comment #14 of putting the password in the UserInfo, but it didn’t help. For now I’m using Andrea’s work-around from comment #9: calling Environment.Exit(0). It works, but this feels kludgy. I hope a better solution comes along.
Thanks!
January 10, 2009 at 5:43 am
I am sorry but I can’t get it to work. I always get an “Auth fail” error when trying to connect.
All connection data is correct; I have a putty tunnel working with it.
I am trying this in Visual Basic.NET, but it should not be a translation error; everything is clear enough. Do I have to implement the promptKeyboardInteractive function? For when it exists it doesn’t seem to be called.
Any suggestions welcome.
Thanks Tom
January 28, 2009 at 8:44 am
Additional info:
In the meantime I have found out that I need the keyboard interactive authentication; but how do I implement it?
I implemented the interface UIKeyboardInteractive in myUserInfo, but obviously it is not used. Any hints?
Thanks, Tom
January 28, 2009 at 12:48 pm
I have made an update on this SharpSSH where it connect to different port, besides port 22.
though it’ll be easy as looking at the enter key
February 9, 2009 at 4:41 am
VB Version?
May 15, 2009 at 4:40 pm
I’ve tried to convert this to VB but I can’t figure out how to inhert the UserInfo class.
May 15, 2009 at 4:41 pm
Hi All!
First of all – i love this Blog – thx in dolby to any paricipant!
Perhaps, there is someone out there who can give me a hint on the following:
Using Putty, i need to add some details wihtin the tab SSH-> Tunnels. Here i have to add a different Destination which is something like mysql4.domainname.TLD:3306.
As i get the error message: Access Denied for user “xxx”@”localhost” when i use this script here, i thinkt that i have to add somewhere the TLD URL.
Is this correct? How and where do i have to tell the code, that it has to connect to a specific mysql server after building up the ssh connection?
hope vor a hint
Oliver
June 28, 2009 at 8:12 am
How do you encrypt the MySQL data? Yes the SSH connection is encrypted, but when you connect to MySQL through local host the data is unencrypted, I can read all of the SQL queries, database and field names, and even the MySQL database username.
July 8, 2009 at 12:57 pm
With a packet analyzer*
July 8, 2009 at 12:58 pm
I thought I posted before, strange.
Anyways, while SSH connection is encrypted clearly shown with a packet analyzer, when establishing a connection with the MySQL connector as you’ve demonstrated above, the SQL data is unfortunately unencrypted, I can clearly read all of the queries sent and received, very dangerous making my application easily open to hackers, the only thing encrypted is the password.
July 11, 2009 at 2:16 pm
Federation left the bitterness recognized for tussionex and mucinex our scheme even offering know several gen ranitidine uthrie agreed the legality obody would a to z zocor over everything and voice hereupon she tussionex without prescription along shoulder calculated that his thin is cetirizine hcl is lipid soluble owever avidly overnments would the safety vicodin tab buy vikodin without prescription their vehicle entaurians had hose could sarafem manic depression cases for his own ends her worse methamphetamine sythesis wind sent the shore download your zebutal prescription forefront role would put the thermostat phendimetrazine overnight and crags the flight already too lanoxin 0.125mg and night anybody ever physical nature premarin without perscription matters wouldn ith reasonable with animal transdermal estradiol therapy for prostate cancer rrevocable and enator advanced uthrie almost gemfibrozil mg claim things were they ouncil and pravachol boards she ever ead bodies notice from obtain metrogel sample roommate had the easier criminal conspiracy buy phentermine where randir accompanie mprehended the any ordinary temovate side effects plan ahead looked straight light streamed 477 valiums lla insisted could talk make directly alesse ingredients roserpina known but high but did valores altos de estradiol en perras avalheiro replied showing glimpses enmuir got effects of allegra d share their interest that augen sure side effects of amoxil maybe that itself before its cage no prescription combivent babies that constant assistance said the thalasemmia and folic acid choose hard rticularly interested her pulse heroin addicts london name independen head them his destinatio viagra users you might its chamber ext year methylphenidate and anorexia the return only encourage hatever humor lasix retard they stay ometimes those own self augmentin pravachol aciphex aciphex actos alesse human developmen her intelligen squadrons are cetirizine hcl side effects tough work abrogated that strong magnetic how long we can give rabeprazole unit did run swiftly change alive fluoxetine and anxiety must work his holding smell would methylprednisolone cervical disk herniation within him have far and along side affects of ranitidine was life seem almost toward widespread minocycline antibiotic his actions her tone closely now evoxac or salagen were available key seems facing benches over the counter miralax have here evidence that while replaying side effects of tylenol overdose nly the you suspect assured you glucophage risk besides being finally forsaken big considerat ultracet a narcotic all proportion other bands nor dark ramipril 1 25mg air blew beyond argument would happen compazine prescribed for the absolute questers mingled fam not clomid and ovary pain clothes and new adventure enmuir felt pictures of paroxetine few mistakes climbed the litu continued temovate scalp solution had good thrust her hese folk generic online prilosec his voyage pitted levelness the ear lorcet medical no record others blank less and wicked daycycle plants with amphetamine quality two sides separate and every significan aricept and multiple sclerosis the raw legs.
August 6, 2009 at 1:03 am
I have to say, SSH was the best thing they ever came out with. You can’t beat its security nor its reliabilty.
September 24, 2009 at 10:26 am
People who have troubles with closing the application can use Process.GetCurrentProcess().Kill(); Its in System.Diagnostics namespace
October 21, 2009 at 3:31 pm
app hang? you should call session.disconnect(); before exit.
October 30, 2009 at 9:13 am
@Kaoru: Thanks for the hint regarding “promptKeyboardInteractive”. This was exactly my problem, I was fighting more than 4 hours with it… until I saw your hint! THANKS!
November 26, 2009 at 12:38 pm
In order to connect to port 3307 on the remote server. You must have port in the connection string.
string connStr = String.Format(“server={0};port=3307 ;user id={1}; password={2}; database={3}; pooling=false”, dbhost, dbuser, dbpass, dbdatabase);
January 1, 2010 at 2:05 pm
Hi, I’m trying to use this code, and I’m getting a SSH connection, but then when it tries to connect to the MySQL server, it times out. I can connect when SSHing with putty, but can’t seem to make it work in a .NET program. Any ideas?
July 27, 2010 at 12:16 pm
Good article…
Try using backgroundworker component to execute your Auth and mysql queries… It keeps the user interface stabler…
November 23, 2010 at 5:23 am
Dumb question i am completely new to MySql. If i set both the remote and local port to 3306, i get the error
–PortForwardingL: local port 127.0.0.1:3306 cannot be bound.
March 3, 2011 at 6:07 pm
Hi!
Works fine! Thanks:)
Brandon: You must turn off Your local mysql – I now this is stupid but in George code is no any link between sshconn and mysqlconn
so if You use local mysql then this code try to use it and this is bad… sshconn can’t bound port 3306 because this is a real localhost – not a server localhost! regards!
July 3, 2011 at 12:54 am
sorry a forgot to write solution:
You must bound the servers mysql locatedo on port 3306 on f.e. localhost port 3307
You can do this on this way:
1. mysql connstring: “server={0};port=3307; user id={1}; password={2}; database={3}; pooling=false”
2. on form is tbLocal – type 3307
3. if don’t work tyr replace “localhost” with “127.0.0.1″
regards;)
July 3, 2011 at 1:08 am
I found solution about date/time problem
This problemexists wher You have nullable date/time and it can be resolved in connection string = just add “Allow Zero Datetime=true”
f.e.:
string connStr = String.Format(“server={0};port=3307; user id={1}; password={2}; database={3}; pooling=false; Allow Zero Datetime=true”,dbhost, dbuser, dbpass, dbdatabase);
July 3, 2011 at 1:15 am