How to connect to mysql from C# over SSH

Question

How can I connect to a mysql database trough C#,

This is my connection string now:

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

How to put SSH string in this form as it needs to be something like:

SSH Hostname, SSH Username, SSH password, Mysql Hostname, Mysql Username, Mysql Password, Port

Handfasting

I would be tempted to create an SSH proxy/forward first and then just use that... – Romeliaromelle

sure, give me some more info how that is made – Handfasting

Look at the plink program that comes as part of Putty. Perhaps not the most elegant, but it should work. The -L (forward a local port) parameter will be of interest here. Credentials can be supplied in a number of methods. I recommend using PKI. – Romeliaromelle

wow, thats quite hard to get how this putty works :) – Handfasting

Answer 1

I don't think MySql and the MySqlClient support such a thing. The connection string is specifically for the database. You will need an SSH client to connect first to the SSH server and then find a way to route the Sql connection over that 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 project on Code Plex that might help.

http://sshnet.codeplex.com/

Rh

Answer 2

        // using Renci.sshNet 

        PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo(hostAdres, hostNaam, wachtwoord);
        connectionInfo.Timeout = TimeSpan.FromSeconds(30);
        var 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();
        
        var connection = new MySqlConnection("server = " + "127.0.0.1" + "; Database = database; password = PWD; UID = yourname; Port = 22");
        connection.Open();

Paste

Answer 3

Code valid for me, using sshkeyfile (*.pem), C# Mysql -> Aurora in Amazon Web Services:

class Program
{

    static string SshHostName = "***";
    static string SshUserName = "***";
    static string SshKeyFile = @"C:\Work\pems\***.pem";

    static string Server = "***.eu-west-1.rds.amazonaws.com";
    static uint Port = 3306;
    static string UserID = "***";
    static string Password = "***";
    static string DataBase = "***";

    static void Main(string[] args)
    {

        ConnectionInfo cnnInfo;
        using (var stream = new FileStream(SshKeyFile, FileMode.Open, FileAccess.Read))
        {
            var file = new PrivateKeyFile(stream);
            var authMethod = new PrivateKeyAuthenticationMethod(SshUserName, file);
            cnnInfo = new ConnectionInfo(SshHostName, 22, SshUserName, authMethod);
        }

        using (var client = new SshClient(cnnInfo))
        {
            client.Connect();
            if (client.IsConnected)
            {
                var forwardedPort = new ForwardedPortLocal("127.0.0.1", Server, Port);
                client.AddForwardedPort(forwardedPort);
                forwardedPort.Start();

                string connStr = $"Server = {forwardedPort.BoundHost};Port = {forwardedPort.BoundPort};Database = {DataBase};Uid = {UserID};Pwd = {Password};";

                using (MySqlConnection cnn = new MySqlConnection(connStr))
                {
                    cnn.Open();

                    MySqlCommand cmd = new MySqlCommand("SELECT * FROM PostalCodes LIMIT 25;", cnn);

                    MySqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                        Console.WriteLine($"{reader.GetString(1)}, {reader.GetString(2)}, {reader.GetString(3)}");

                    Console.WriteLine("Ok");

                    cnn.Close();
                }

                client.Disconnect();
            }

        }
    }
}

Haematothermal

Trying to accomplish a similar task, but the code needs to run on kubernetes. – Redbreast

Answer 4

I tried all the previous steps and it didn't work, the method that worked for me was the following:

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

Kamasutra

I think the ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306); was the trick. When you think about it this is doing the same thing that Putty is doing. – Rojas

Answer 5

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

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

Whipsaw

Have an example of how the SSH connection can be established [first]? – Romeliaromelle

Thank you for you answer but problem is establishing that kind of connection. As pst said I need to use putty. But it is a bit complicated – Handfasting

You can use for example sharpSsh – Whipsaw

Answer 6

Here is the final code :) Works for me.

    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();

Sphinx

If you feel that a previous answer may need some updates, why not edit that answer as opposed to posting a new one and copy/pasting? – Zelmazelten

Answer 7

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

Nefarious