通过SSH隧道的mySQL连接字符串，无需密码 [英] mySQL connection string through SSH tunnel without password

问题描述

已为我提供了对运行mySQL的服务器的SSH访问权限.通过我的公共密钥对我的访问进行身份验证，不需要其他密码.

I've been given SSH access to a server running mySQL. My access is authenticated through my public key, with no further password required.

使用Putty，我能够使用我的私钥SSH到服务器，通过命令行工具直接登录到mySQL数据库并直接运行SQL.我正在通过C#服务(通过SSH连接转发端口3306)来模拟这一点，不幸的是，所有连接字符串都列在

Using Putty, I am able to SSH to the server using my private key, log directly into the mySQL database via the command-line tools and run SQL directly. I'm looking to emulate this through a C# service (forwarding port 3306 through the SSH connection), unfortunately all the connection strings listed at http://www.connectionstrings.com/mysql/ require a password.

到目前为止，我使用MySql.Data和SSH.NET编写的基本代码:

My rough-and-ready code so far, using MySql.Data and SSH.NET:

  PrivateKeyFile file = new PrivateKeyFile(@" [ path to private key ]");
  using (var client = new SshClient(" [ server ] ", "ubuntu", file))
  {

      var port = new ForwardedPortLocal(3306, "localhost", 3306);
      client.AddForwardedPort (port);
      client.Connect();

      var connection = new MySqlConnection(
           "server=localhost;database=[database];uid=ubuntu;"
      );
      MySqlCommand command = connection.CreateCommand();
      MySqlDataReader Reader;
      command.CommandText = "select * from sample";
      connection.Open();
      Reader = command.ExecuteReader();
      while (Reader.Read())
      {
          string thisrow = "";
          for (int i = 0; i < Reader.FieldCount; i++)
              thisrow += Reader.GetValue(i).ToString() + ",";
          Console.Write(thisrow);
      }
      connection.Close();
      client.Disconnect();
 }

运行不带SshClient节的代码，与本地网络上的数据库(具有已知的用户名和密码)进行通信，效果很好.插入SshClient部分，并在建立隧道后使用断点，然后可以telnet到localhost:3306并从mySQL获取响应.

Running the code without the SshClient section, talking to a database on the local network (with a known username and password), is working perfectly. With the SshClient section in, and with a breakpoint once the tunnel has been set up, I can telnet to localhost:3306 and get a response back from mySQL.

但是，在connection.Open()行上引发了以下错误:

However, the following error is thrown on the connection.Open() line:

使用方法'mysql_native_password'对用户'ubuntu'的主机'localhost'进行身份验证失败，并显示以下消息:用户'ubuntu'@'localhost'的访问被拒绝(使用密码:是)

Authentication to host 'localhost' for user 'ubuntu' using method 'mysql_native_password' failed with message: Access denied for user 'ubuntu'@'localhost' (using password: YES)

那么...我需要什么连接字符串?还是我错过了其他地方的细微之处?

So ... what connection string do I need? Or have I missed something subtle elsewhere?

推荐答案

该问题原来与SSH隧道本身有关.而是使用Putty创建隧道，并删除在该行之前运行的所有代码:

The problem turned out to be related to the SSH tunnel itself; creating the tunnel using Putty instead and removing all the code that runs before the line:

  var connection = new MySqlConnection(
       "server=localhost;database=[database];uid=ubuntu;"
  );

现在成功了.

吸取了两个教训:

• 如果mySQL不需要密码，则可以省略密码.
• mySQL的错误消息可能与更显式有关.

这篇关于通过SSH隧道的mySQL连接字符串，无需密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！