How to connect to mysql from C # via SSH

Question

How to connect to mysql database through C #,

Now this is my connection string:

connectionString="server=localhost;port=3306;user id=root;Password=*****;database=Data" providerName="MySql.Data.MySqlClient" 

How to put an SSH string in this form since it should be something like:

SSH hostname, SSH username, SSH password, Mysql host name, Mysql username, Mysql password, port

Answer 1

I do not think that MySql and MySqlClient support such a thing. The connection string is designed specifically for the database. You will need an SSH client for the first connection to an SSH server, and then find a way to route the Sql connection over this tunnel.

http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/

I don’t think there is a Microsoft.Net library for handling SSH connections, but there is an open source Code Plex project that can help.

http://sshnet.codeplex.com/

Answer 2

 PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo(hostAdres, hostNaam, wachtwoord); connectionInfo.Timeout = TimeSpan.FromSeconds(30); client = new SshClient( connectionInfo); client.Connect(); ForwardedPortLocal portFwld = new ForwardedPortLocal ("127.0.0.1", Convert.ToUInt32(hostpoort), DataBaseServer, Convert.ToUInt32(remotepoort)); client.AddForwardedPort(portFwld); portFwld.Start(); // using Renci.sshNet connection = new MySqlConnection("server = " + "127.0.0.1" + "; Database = database; password = PWD; UID = yourname; Port = 22"); connection.Open(); 

Answer 3

You cannot specify an SSH proxy or SSH credentials in the connection string, you first need to establish an SSH connection, and then use the standard connection string, like what you have in your question.

To establish an SSH connection through C #, you can use a library like sharpSsh .

Answer 4

this is the final code :)

  PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo("host", "user", "password"); connectionInfo.Timeout = TimeSpan.FromSeconds(30); var client = new SshClient(connectionInfo); client.Connect(); var x = client.IsConnected; ForwardedPortLocal portFwld = new ForwardedPortLocal("127.0.0.1"/*your computer ip*/, "127.0.0.1" /*server ip*/, 3306 /*server mysql port*/); client.AddForwardedPort(portFwld); portFwld.Start(); //// using Renci.sshNet var connection = new MySqlConnection("server = " + "127.0.0.1" /*you computer ip*/ + "; Database = DataBaseName; UID = ?; PWD =?; Port = " + portFwld.BoundPort /*very important !!*/); connection.Open(); var k = connection.State; connection.Clone(); client.Disconnect(); 

Answer 5

 After so much of research the below code worked for me Hope it may help you also public static string GetRDSConnectionString() { string Database = "<yourdb>"; string value = ""; string mysqlport = "3306"; uint sqlport = Convert.ToUInt32(mysqlport); string mysqlhostname = "<aws-hostname.com>"; string ssh_host = "100.1.1.1"; int ssh_port = 22; string ssh_user = "ubuntu"; var keyFile = new PrivateKeyFile(@"C:\Automation\LCI\harvest-dev-kp.pem"); var keyFiles = new[] { keyFile }; var uname = "ubuntu"; MySqlConnection con = null; MySqlDataReader reader = null; var methods = new List<AuthenticationMethod>(); methods.Add(new PasswordAuthenticationMethod(uname, "")); methods.Add(new PrivateKeyAuthenticationMethod(uname, keyFiles)); ConnectionInfo conInfo = new ConnectionInfo(ssh_host, ssh_port, ssh_user, methods.ToArray()); conInfo.Timeout = TimeSpan.FromSeconds(1000); using (var client = new SshClient(conInfo)) { try { client.Connect(); if (client.IsConnected) { Console.WriteLine("SSH connection is active"); } else { Console.WriteLine("SSH connection is inactive"); } string Localport = "3306"; string hostport = "3306"; var portFwdL = new ForwardedPortLocal("127.0.0.1", Convert.ToUInt32(hostport), mysqlhostname, Convert.ToUInt32(Localport)); client.AddForwardedPort(portFwdL); portFwdL.Start(); if (portFwdL.IsStarted) { Console.WriteLine("port forwarding is started"); } else { Console.WriteLine("port forwarding failed"); } string connectionstring = "Data Source=localhost;Initial Catalog=<DBNAME>I;User ID=<USERNAME>;Password=<PASSWORD>;SslMode=none"; con = new MySqlConnection(connectionstring); MySqlCommand command = con.CreateCommand(); command.CommandText = "<YOUR QUERY>"; try { con.Open(); } catch (Exception e) { Console.WriteLine(e.Message); } reader = command.ExecuteReader(); while (reader.Read()) { value = reader["<db_col_name>"].ToString(); } client.Disconnect(); } catch (SocketException ex) { Console.WriteLine(ex.Message); } finally { Console.WriteLine("SSh Disconnect"); } } //Console.ReadKey(); return value; } } 

Answer 6

I tried all the previous steps and it did not work, the method that worked for me was as follows:

  try { using(var client = new SshClient("ssh server id", "sshuser", "sshpassword")) // establishing ssh connection to server where MySql is hosted { client.Connect(); if (client.IsConnected) { var portForwarded = new ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306); client.AddForwardedPort(portForwarded); portForwarded.Start(); using (MySqlConnection con = new MySqlConnection("SERVER=127.0.0.1;PORT=3306;UID=someuser;PASSWORD=somepassword;DATABASE=DbName")) { using (MySqlCommand com = new MySqlCommand("SELECT * FROM tableName", con)) { com.CommandType = CommandType.Text; DataSet ds = new DataSet(); MySqlDataAdapter da = new MySqlDataAdapter(com); da.Fill(ds); foreach (DataRow drow in ds.Tables[0].Rows) { Console.WriteLine("From MySql: " + drow[1].ToString()); } } } client.Disconnect(); } else { Console.WriteLine("Client cannot be reached..."); } } } catch (Exception e) { Console.WriteLine(e.Message); }