Connection to MySQL from .NET using SSH.NET Library

Question

I am developing a web page (ASP.NET/ C#) that queries (MySQL) database on a remote server over SSH. I am using those two libraries (mysql-connector-net-6.9.7) and (Renci.SshNet.dll).

I can access MySQL database using MySQL Workbench on the remote server over SSH connection:
"portal.RemoteServer.edu:22" using "RemoteServerUsername" and
"RemoteServerPassword".

Here is my C# code, which doesn't return any data from Clients table on the remote server:

MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
connBuilder.AllowBatch = true;
connBuilder.Server = "127.0.0.1";
connBuilder.Port = 3306;
connBuilder.UserID = "LocalHostUserID";
connBuilder.Password = "LocalHostPassword";
connBuilder.Database = "DatabaseName";

var auth =
    new PasswordAuthenticationMethod("RemoteServerUsername", "RemoteServerPassword");
ConnectionInfo conInfo =
    new ConnectionInfo("portal.RemoteServer.edu", "RemoteServerUsername", auth);

using (SshClient client = new SshClient(conInfo))
{
    ForwardedPortLocal port = new ForwardedPortLocal("127.0.0.1", 0, "127.0.0.1", 22);
    client.Connect();
    client.AddForwardedPort(port);
    port.Start();
    MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
    conn.Open();
    conn.ChangeDatabase(connBuilder.Database);

    var command = "SELECT * FROM DatabaseName.Clients LIMIT 10";
    using (MySqlCommand cmd = new MySqlCommand(command))
    {
        using (MySqlDataAdapter sda = new MySqlDataAdapter())
        {
            cmd.Connection = conn;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }
    }
}

score 14 · Answer 1 · edited Apr 09 '16 at 13:10

Most of the code below is self explanatory. Still I have put the needful comments. I was able to connect to the MySql database with the code below. I had used SSH library from here and MySql connector for .NET.

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=somepass;DATABASE=Dbname"))
        {
            using (MySqlCommand com = new MySqlCommand("SELECT * FROM cities", con))
            {
                com.CommandType = CommandType.CommandText;
                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...");
    }
}

If your running a local mysql instance, change the port to 3307 like so `new ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3307)` then the connection string should change to `PORT=3307` — Spencer, Mar 16 '21 at 21:30

Martin Prikryl · Answer 2 · 2017-02-14T10:44:34.943

You have to forward a local port to the remote MySQL port (3306), not the SSH port 22 (that would create a loop).
You are passing 0 to the boundPort argument of the ForwardedPortLocal. That means a port number is automatically selected by the OS. Yet you are trying to connect to the MySQL via the fixed port 3306.
- Either pass the fixed port 3306 to the ForwardedPortLocal
```
port = new ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306);
```
  This won't work if the local 3306 port is already used by a local MySQL database. You can of course use any other local port (the remote port must be 3306 though).
- Or use the port.BoundPort value (after calling the port.Start()) for the connBuilder.Port.
```
port.Start();
connBuilder.Port = port.BoundPort;
conn = new MySqlConnection(connBuilder.ConnectionString);
```
  Make sure you have the latest version of the SSH.NET library. The .BoundPort was not updated in the old versions.
Note that the first 127.0.0.1 refers to your local machine (IP address relative to your local machine), while the second refers to the server (IP address is relative to the server machine). You can typically omit the first argument.

Connection to MySQL from .NET using SSH.NET Library

2 Answers2

Linked