1

I'm trying to connect to a MySQL DB running on Ubuntu using C# and SSH.Net.

Our MySQL DB only allows connections from a webserver and going by what I can see from the code below, the client object connects to the webserver and then I open a port to map to 3306 on the DB server. Finally the MySQLConnection object opens a connection to the MySQL DB.

However I get with exception in sql.Open() statement:

Unable to connect to any of the specified MySQL hosts

I've checked other threads on the same subject and tried their suggestions, but none of them seem to help me.

and many more.

Here is some test code that I'm using - 

var ci =
     new ConnectionInfo(
         "server", "userid", new PasswordAuthenticationMethod("userid", "userpwd"));
using (var client = new SshClient(ci))
{
    client.Connect();
    if (client.IsConnected) //This part works fine - I can connect to my Webserver.
    {
        //not sure if this is correct in our context.
        var local = new ForwardedPortDynamic("127.0.0.1",3306); 
        client.AddForwardedPort(local);
        try
        {
            local.Start();
        }
        catch (SocketException se)
        {

        }
        string connStr =
            "Server = dbserver;Port = 3306;Database = dbname;Uid = dbuserid;Pwd = dbpwd;";
        MySqlConnection sql = new MySqlConnection(connStr);

        try
        {
            sql.Open();
            local.Stop();
        }
        catch (MySqlException se)
        {
        }
    }
}

All of the credentials I've put in my code was taken off a working MySQLWorkbench connection to the MySQL DB.

I work exclusively in a Windows environment, so if you want me to check for something on the Ubuntu server please also tell me, if possible, how to check for it.

Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992
Rakesh
  • 654
  • 4
  • 10
  • 23
  • Please check and make sure of the following: is the server name correct? is the server reachable by host-name (i.e. can the host be resolved)? Is the port correct? Is the firewall open? Is the server running? Is the username valid? Is the password valid? – Stefan Mar 19 '18 at 18:36
  • So, what happens when you connect with ssh directly with `ssh -L 3306:dbHostName:3306 sshuser@sshhostnameorip` then try to connect to localhost:3306? – spender Mar 19 '18 at 18:40
  • Could be many things, have you checked the dbserver firewall? – Jim W Mar 19 '18 at 18:42
  • I use SQLWorkbench to connect to the MySQLDB and all the data I've plugged into the code is based off of my SQLWorkbench connection. I can connect using SQLWorkbench to the db. – Rakesh Mar 19 '18 at 18:43

1 Answers1

1

You are connecting via an SSH tunnel.

So your primary problem is that the Host in the connection string must be localhost (the local end of the SSH tunnel), not dbserver. Or even better, use ForwardedPort.boundHost (and ForwardedPort.boundPort).

The second problem is that you cannot use ForwardedPortDynamic, but ForwardedPortLocal.

var forwardedPort = new ForwardedPortLocal("127.0.0.1", "127.0.0.1", 3306);

client.AddForwardedPort(forwardedPort);
forwardedPort.Start();

string connStr =
    string.Format(
        "Server = {0};Port = {1};Database = dbname;Uid = dbuserid;Pwd = dbpwd;",
        forwardedPort.BoundHost, forwardedPort.BoundPort);

See also Connection to MySQL from .NET using SSH.NET Library.

If your database does not run on the webserver, but on dbhost, you have to use that in the ForwardedPortLocal constructor:

var forwardedPort = new ForwardedPortLocal("127.0.0.1", "dbhost", 3306);
Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992