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