Connection to MySQL from .NET using SSH.NET Librar

问题:

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:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using Renci.SshNet;
using Renci.SshNet.Common;
namespace WebApplication1
{
    public partial class About : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            MySqlConnection conn = null;
            SshClient client = null;
            ForwardedPortLocal port = null;

            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";

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

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

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

Code after applying the suggestion from the answer by @Martin Prikryl, but it is still not working.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using Renci.SshNet;
using Renci.SshNet.Common;
namespace WebApplication1
{
    public partial class About : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            MySqlConnection conn = null;
            SshClient client = null;
            ForwardedPortLocal port = null;

            MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
            connBuilder.AllowBatch = true;
                        connBuilder.Server = "127.0.0.1";
        connBuilder.Port = 3306;
        connBuilder.UserID = "remoteUsername";
        connBuilder.Password = "remotePassword";
        connBuilder.Database = "databasename";

        ConnectionInfo conInfo = new ConnectionInfo("portal.remoteserver.edu", 22, "remoteUsername", new PasswordAuthenticationMethod("remoteUsername", "remotePassword"));

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

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

回答1:

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...");
    }
}

回答2:

1. You have to forward a local port to the remote MySQL port (3306), not the SSH port 22 (that would create a loop).

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