Access MySQL data source via SSH Tunnel (Linux)

NineData supports adding data sources via multiple connections. This document details how to add via SSH Tunnel.

Prerequisite

• The SSH service is installed and enabled in the server of the target data source is located.

• The SSH configuration file (sshd_config) of the server of the target data source has following parameters setup:

  • AllowTcpForwarding yes
  • AuthorizedKeysFile .ssh/authorized_keys
  tip

  sshd_config: by default, the file resides in the /etc/ssh directory.

Environmental Description

This is an example of how SSH Tunnel is used by adding a self-managed data source.The environments used in this case are as follows:

• Data source version: Linux version 4.4180+
• SSH Version: OpenSSH_8.2p1
• Data source: MySQL 8.0

tip

If you use a Windows system, please refer to Access self-built MySQL data source via SSH Tunnel (Windows).

Steps

1. Login to NineData Console.

2. On the left navigation bar, click Datasource> Datasource.

3. Click Create Datasource in the upper right corner of the page. On the Create datasource page, configure parameters based on 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 Appendix.
   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 Appendix, 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 step 1 of 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 Datasource to complete adding the data source.

Appendix: Generates SSH Tunnel Key Files

Note: The following steps need to be executed in the command line window (terminal) of the server where the data source is located.

1. 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 can choose to set a passphrase for this key file (enter the password and press the Enter key) or not set a password (press the Enter key directly).

   tip

   The command will generate the following two files:

   • <Key filename>: Private key for authenticating logon identity.
   • <Key filename>.pub：Public key, saved on the server, used to match the private keys provided by the login.
   • :::

2. Enter the cat <key file name>.pub >> ~/.ssh/authorized_keys command to add the public key content to the end of the authorized_keys file.

   tip

   authorized_keys The permission of the file must be less than or equal to 600, that is, -rw------- (only the owner of the file can read and write), Failure to do so may result in connection failure. User can execute the ll or ls -l command in the ~/.ssh/ directory to check the permission of the file. If the permission does not meet the requirements, user can Execute chmod 600 authorized_keys to modify the file permissions.