skip to Main Content

I was looking for a workaround for configuring my database connection.
I saw that opening 3306 port is dangerous and we should be using SSH Tunnel instead to connect to the database.

I configured my MySQL server using docker and successfully connected it using MySQL Workbench
enter image description here

Now I have to configure and connect it to Visual Studio 2022 to be able to query to the database.

Visual Studio 2022 is only supported by MySQL Data thru NuGet packages which doesn’t have a gui connection setup.

enter image description here

I installed Visual Studio 2019 which is officially supported by MySQL Database and can be configured thru Data Source.

How can I setup MySQL Database connection to my Visual Studio if it’s SSH Tunnel configured.
Add Connection window only shows basic information about the connection. I’m not sure how to configure this over a SSH Tunnel.

enter image description here

Thank you in advance.

2

Answers


  1. You can fill in the following information to configure the connection to the MySql database.

    enter image description here

    Server name: Enter the IP address of MySQL, which is 127.0.0.1 as seen in your SSL connection information.

    User name: Enter the user name of Mysql

    Password: Enter the password of Mysql

    Database name: Enter a test database

    Hope it can help you

    Login or Signup to reply.
  2. For security reasons, sometimes the database server can only be accessed through SSH. For example, the MySql service is installed on server A, and machine A can only be accessed by machine B, and the deployment environment may be on machine C. In this case, C The server connects to the A server through the B server. At this time, SSH connection is required, and the SSH.NET class library is required:
    code show as below:

    using MySql.Data.MySqlClient;
    using Renci.SshNet;
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    
    namespace SSHMySql
        {
            public partial class Form1 : Form
            {
                public Form1()
                {
                    InitializeComponent();
                    SSHConnectMySql();
                }
    
                public void SSHConnectMySql()
                {
                    string SSHHost = "*.*.*.*";        // SSH address
                    int SSHPort = ;              // SSH port
                    string SSHUser = "user";           // SSH username
                    string SSHPassword = "pwd";           // SSH password
    
                    string sqlIPA = "127.0.0.1";// Map addresses  In fact, it is possible to write other MySql on Linux My.cnf bind-address can be set to 0.0.0.0 or not
                    string sqlHost = "192.168.1.20"; // The IP address of the machine installed by mysql can also be an intranet IP, for example: 192.168.1.20
                    uint sqlport = ;        // Database port and mapping port
                    string sqlConn = "Database=mysql;Data Source=" + sqlIPA + ";Port=" + sqlport + ";User Id=user;Password=pwd;CharSet=utf8";
                    string sqlSELECT = "select * from user";
    
                    PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo(SSHHost, SSHPort, SSHUser, SSHPassword);
                    connectionInfo.Timeout = TimeSpan.FromSeconds();
                    using (var client = new SshClient(connectionInfo))
                    {
                        try
                        {
                            client.Connect();
                            if (!client.IsConnected)
                            {
                                MessageBox.Show("SSH connect failed");
                            }
    
                            var portFwdL = new ForwardedPortLocal(sqlIPA, sqlport, sqlHost, sqlport); // map to local port
                            client.AddForwardedPort(portFwdL);
                            portFwdL.Start();
                            if (!client.IsConnected)
                            {
                                MessageBox.Show("port forwarding failed");
                            }
    
                            MySqlConnection conn = new MySqlConnection(sqlConn);
                            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
                            myDataAdapter.SelectCommand = new MySqlCommand(sqlSELECT, conn);
    
                            try
                            {
                                conn.Open();
                                DataSet ds = new DataSet();
                                myDataAdapter.Fill(ds);
                                dataGridView1.DataSource = ds.Tables[];
                            }
                            catch (Exception ee)
                            {
                                MessageBox.Show(ee.Message);
                            }
                            finally
                            {
                                conn.Close();
                            }
    
                            client.Disconnect();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                }
            }
        }
    

    Note: If an error occurs, you can stop the MySql service on the local (development machine).

    Required dll: SSHDLL.rar

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search