Access MySQL data source via SSH Tunnel (Windows)

NineData supports adding data sources through multiple connection methods. This article introduces how to add data sources through SSH Tunnel in detail.

Prerequisites

The MySQL service has been installed and started on the server where the target data source is located.

Environment description

This article takes adding a self-managed data source as an example to introduce how to use SSH Tunnel. The environment used in this case is as follows:

• Data source server version: Windows 11 (10.0.22621.963)

• SSH version: OpenSSH_for_windows_8.6p1

• Data source: MySQL 8.0 self-managed data source

tip

• Different versions of Windows may operate slightly differently.
• OpenSSH is not included on Windows systems released before April 2018, user may need to install it manually. The latest version of the installation package download address: Official Github.
• For a Linux system, please refer to Access self-managed MySQL data source via SSH Tunnel (Linux).

Step 1: Add and start SSH service

The SSH service includes two services OpenSSH Authentication Agent and OpenSSH SSH Server, and OpenSSH Authentication Agent is installed by default, so installation of OpenSSH SSH Server is necessary, please refer to the following operation step and start the two services.

1. Type the key combination Win+R to open the Run window, enter fodhelper, and click OK to open Optional Function window.

2. Click View Features on the right side of Add Optional Features, enter SSH in the search box, then select the check box on the right side of OpenSSH Server, click Next step.

   tip

   If no results are found, user may have added the OpenSSH server service, please follow this step and continue.

3. Click Install on the confirmation page, and wait for the service installation to complete. After the installation is complete, restart the computer.

4. After restarting and entering the system, type Win+R key combination to open the Run window, enter services.msc, and click OK, to open the Services window.

5. Find OpenSSH Authentication Agent and OpenSSH SSH Server services in the service list, respectively change the Startup Type of the two services to Automatic and start the services.

   Operation steps

   1. Double-click the target service, in the pop-up Properties window (General tab), click the drop-down box on the right side of Startup Type, and select Automatic from the drop-down list .

   2. In the Service Status area, if the status is Stopped, click Start, and then click OK. If the status is Running, just click OK.

6. Type the key combination Win+R to open the Run window, enter cmd, and click OK to open the command line, enter netstat -an, check whether 22 port is in the state of being monitored. When the following message shows in the returned results, the SSH service has started successfully.

   TCP [::]:22 [::]:0 LISTENING

Step 2: Configure the sshd_config file

1. Open the sshd_config file in the C:\ProgramData\ssh\ directory with a text editor (such as Notepad).

   tip

   The ProgramData directory is hidden by default, please click View > Show > Hidden items in the file explorer to show the hidden directory.

   

2. Configure according to the table below:

   Parameter	Value	Description
   AuthorizedKeysFile	.ssh/authorized_keys	The directory where the key file is located. Description: Please ensure that this parameter is not commented out.
   PubkeyAuthentication	yes	Whether to support connecting via public key. Description: Please ensure that this parameter is not commented out.
   Match Groupa administrators	AuthorizedKeysFile __PROGRAMDATA__/ssh/ad ministrators_authorized_keys	The public key file path of the administrator user. Description: Please comment out this parameter, otherwise the authentication will not be completed. Since the parameters and values are on two lines, both lines need to be commented out.

3. Restart the SSH Server service.

   tip

   Restart method:

   1. Type Win+R to open the Run window, enter services.msc, and click OK.
   2. Find OpenSSH SSH Server service in the service list and double-click it. In the pop-up Properties window (General tab), click Stop, Start in sequence That's it.

Step 3: Generate the SSH Tunnel key file and add the public key to the whitelist file

1. Type the key combination Win+R to open the Run window, enter cmd, and click OK to open the command prompt character window.

2. Execute the ssh-keygen -f <key file name> command to generate a key file, when the system prompts Enter passphrase (empty for no passphrase):, user may choose to set a passphrase for the key file or do not set a passphrase (press the Enter key directly).

   tip

   This command will generate the following two files:

   • <key file name>: private key, used to verify the identity of the login.
   • <key file name>.pub: public key, installed on the server, used to match the private key provided by the registrant.

3. Open the <key file name>.pub file with a text editor (such as Notepad), copy whole content inside, and paste to C:\Users\<your system login name>\.ssh\authorized_keys file (if it does not exist, please manually create a file with the same name in this directory, please note that there is no suffix).

   tip

   • Please ensure that the permission subjects of the authorized_keys file are only SYSTEM, Administrators, current logged-in user, otherwise the connection will fail. File permissions can be viewed by:
     1. Right-click the authorized_keys file in the C:\Users\<your system login name>\.ssh\ directory, click Properties.
     2. Click the Security tab, and check the authority subject in the box under Group or user names. If there are redundant principals, they need to be deleted manually.
   • How to delete the permission subject (continue to view file permission operation):
     1. Click Advanced at the bottom right of the Security tab, click Disable inheritance, in the pop-up window, click Convert the inherited permissions to the display permissions of this object .
     2. Select unnecessary entries in Permission Entries, click Remove, and ensure that the remaining permission subjects are SYSTEM, Administrators, current logged-in user, click OK.

Step 4: Add data source in NineData console

1. Log in to NineData Console.

2. In the navigation pane on the left, click Datasource>Datasource.

3. On the Create Datasource page, configure parameters according to the table below.

   Parameters	Description
   Name	Enter the name of the data source, in order to facilitate follow-up search and management, use the meaningful name to the extent possible.
   Data source Location	Select On-Premise/Other Cloud
   Environment	PROD(Production) and DEV(Development) environments are provided by default, and also allow customerized environments. Note : In organizational mode, the database environment can also be applied to permission policy management. For example, the production environment administrator role by default only supports access to data sources in the production environment and cannot access data sources in other environments. For more information, see Managing Roles.
   DB Type	Select MySQL.
   Connection	Select SSH Tunnel.
   SSH Host	Enter the IP or domain name of the server in which the target data source is located, and the corresponding port number (the default port number for SSH service is 22).
   SSH Authentication Method	Password: Connects via SSH Username (i.e. server login name) and SSH Password (i.e. server login password). Key (recommended): Connects with SSH Username and Key File. This option requires generated key files. For how to generate, see Step 3: Generate the SSH Tunnel key file and add the public key to the whitelist file.
   SSH Username	Enter the login username of the server where the target data source is located.
   SSH Password	Only displayed when SSH Authentication Method is Password, enter the login password of the server where the target data source is located. Note: Click Connection Test on the right, and the following two results may appear: Prompt Connection Succeeded: indicates SSH Tunnel is open. Prompt error message: indicates that the connection fails, please check the userid and password.
   Key File	Displayed when SSH Authentication Method is Key, click Upload Upload the private key file, that is, the key file without the suffix. please see Step 3: Generate the SSH Tunnel key file and add the public key to the whitelist file, for instruction to generate the key.
   Password	Displayed when SSH Authentication Method is Key, enter the password set when generating the key file.If you did not set a passphrase in key generation, leave it blank here. Note: Click Connection Test on the right, and the following two results may appear: Prompt Connection Succeeded: indicates SSH Tunnel is open. Prompt error message: indicates that the connection failed, please troubleshoot the cause of the failure according to the prompt and try again.
   Host	Connection address and port for data source. Note: Since it is a connection to the server of data source is located via SSH Tunnel, user can directly enter localhost or 127.0.0.1, and fill in the port number of the target data source.
   DB Account	Username for login data source.
   DB Password	The password corresponding to the user name used to log in to the data source. Note: Click Connection Test on the right, and the following two results may appear: Prompt Connection Succeeded: Indicates that the data source connection is successful. Prompt error message: Indicates that the connection fails, please check the account and password.
   Access Region	Select the region/location closer to data source for better network performance.
   Encryption	Whether to access data sources using SSL encryption. If SSL encrypted connection is forced by data sources, this switch must be turned on, otherwise the connection failed. Click on the right switch to turn on or off encrypted transfer.Click > on the left side of Encryption to show detailed configuration. Description: Options for different types of data sources are different, please refer to the console. For how to configure SSL please see official document:Configure MySQL encrypted connections

4. Click Create Data Source to complete adding the data source.