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