Log on for operations and maintenance using the SQL Server protocol - Bastionhost

Bastionhost supports O&M and auditing for ApsaraDB RDS for MySQL, SQL Server, and PostgreSQL instances, PolarDB for MySQL, PostgreSQL, and PostgreSQL (Oracle-compatible) clusters, and self-managed MySQL, SQL Server, PostgreSQL, and Oracle databases. You can also use an O&M token to establish an SSH tunnel for O&M and auditing. This topic describes how to perform O&M on databases on a Windows system.

Prerequisites

You have installed an O&M tool for databases that supports Secure Shell (SSH) tunnels on your local host, such as DBeaver, DbVisualizer, Navicat Premium, or Navicat for MySQL.
You have obtained the O&M address of the bastion host instance. You can find the O&M address in the Bastion Host Information section on the Overview page. For more information, see Overview of the Bastionhost console.
Note
Bastionhost provides a fixed O&M address as a domain name and uses dynamic IP addresses to prevent attacks. The IP address that is resolved from the O&M address may change. To prevent O&M failures caused by IP address changes, you must use the domain name provided by Bastionhost for O&M.

Connect with a client tool via an SSH tunnel

This topic uses Navicat Premium as an example to describe how to connect to different types of databases from a Windows system.

Log on to the Bastionhost console or O&M portal to obtain an O&M token. For more information, see Obtain an O&M token.
Note
- When you obtain an O&M token from the O&M portal, if the current database account is not managed by Bastionhost, you must first configure the basic information of the database account in the O&M Token dialog box. For more information about how to create a database account, see Database management.
- O&M tokens must be used within their validity periods. An administrator can set the validity period in the Bastionhost console. If O&M approval is enabled, the validity period that is set by the administrator during the approval process is used.
- If an administrator allows O&M users to renew their own tokens, the users can renew the tokens before they expire. After a token expires, the user must request a new one. If O&M approval is enabled, users cannot renew tokens. After token settings are changed, you must request a new token or update the existing one for the new settings to take effect.
- If a token is valid but the O&M connection fails, the number of concurrent O&M connections may have reached the upper limit. In this case, contact an administrator to upgrade the instance type of your Bastionhost instance or release idle connections. Another possible reason is that an administrator has blocked O&M requests from your source IP address or during the current time period. In this case, contact the administrator to remove the restrictions.
- The audit logs record the user who requested the token, not the username or asset account that is entered on the client.

Open Navicat Premium and create a connection to the target database.

On the General tab, configure the connection parameters as described in the following table.

Parameter	Description
Host	The address of the database asset.
User name	The user name for the database asset.
Password	For MySQL, SQL Server, and PostgreSQL databases: If the password of the database account is hosted in Bastionhost, you do not need to enter a password. If the password is not hosted, enter the password for the database account. For Oracle databases: If the password of the database account is hosted in Bastionhost, enter `123456` and select the logon property specified in the O&M token details. If the password is not hosted, enter the password for the database account and select the specified logon property.

On the SSH tab, configure the basic information for Bastionhost as described in the following table.

Parameter	Description
Use SSH tunnel	Select Use SSH tunnel.
Host	The O&M address of the Bastionhost instance.
Port	The SSH O&M port of the Bastionhost instance. The default port is 60022.
User name	Your Bastionhost user name.
Password	The O&M token that you obtained.

In Navicat Premium, double-click the new database connection to log on to the database asset and perform O&M operations.

Connect with the CLI via an SSH tunnel

This topic uses the MySQL protocol as an example to describe how to connect using the command-line interface (CLI).

Note

Using the CLI to perform O&M on Oracle databases over an SSH tunnel is not supported.

Log on to the O&M portal. For more information, see Log on to the O&M portal.
In the navigation pane on the left, click Databases.
On the Database O&M page, find the target database and in the O&M Token column, click the corresponding O&M token.
In the O&M Token dialog box, select a database account and click Obtain O&M Token.
Note
- When you obtain an O&M token from the O&M portal, if the current database account is not managed by Bastionhost, you must first configure the basic information of the database account in the O&M Token dialog box. For more information about how to create a database account, see Database management.
- O&M tokens must be used within their validity periods. An administrator can set the validity period in the Bastionhost console. If O&M approval is enabled, the validity period that is set by the administrator during the approval process is used.
- If an administrator allows O&M users to renew their own tokens, the users can renew the tokens before they expire. After a token expires, the user must request a new one. If O&M approval is enabled, users cannot renew tokens. After token settings are changed, you must request a new token or update the existing one for the new settings to take effect.
- If a token is valid but the O&M connection fails, the number of concurrent O&M connections may have reached the upper limit. In this case, contact an administrator to upgrade the instance type of your Bastionhost instance or release idle connections. Another possible reason is that an administrator has blocked O&M requests from your source IP address or during the current time period. In this case, contact the administrator to remove the restrictions.
- The audit logs record the user who requested the token, not the username or asset account that is entered on the client.

Open a CLI tool and run the following command.

ssh -N -L <localport>:<databaseAddress>:<databasePort> <bastionusername>@<bastionAddress> -p <bastionPort>

Parameters

Parameter	Description
localport	The custom local listening port for the tunnel. Make sure this port does not conflict with existing listening ports on your local machine.
databaseAddress	The address of the target database asset.
databasePort	The port of the target database asset.
bastionusername	Your user name for Bastionhost.
bastionAddress	The O&M address of your Bastionhost instance.
bastionPort	The SSH O&M port of your Bastionhost instance. The default port is 60022.

When you are prompted for a password, enter the O&M token and press Enter.

[root@localhost ~]# ssh -N -L 33061:rm-xxxx.mysql.rds.aliyuncs.com:3306 testuser@bh-xxxx.bastionhost.aliyuncs.com -p 60022
The authenticity of host '[bh-xxxx.bastionhost.aliyuncs.com]:60022 ([47.x.x.x]:60022)' can't be established.
RSA key fingerprint is SHA256:ji2v1TxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxLKU.
RSA key fingerprint is MD5:61:0e:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:9e:01.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '[bh-xxxx.bastionhost.aliyuncs.com]:60022' (RSA) to the list of known hosts.
testuser@bh-xxxx.bastionhost.aliyuncs.com's password:

Open another CLI window and verify that the custom local port is in the listening state. LISTEN indicates that the port is in a listening state.

ss -an | grep 33061
tcp    LISTEN    0       128       127.0.0.1:33061          *:*
tcp    LISTEN    0       128       ::1:33061                :::*

Run the following command to connect to the MySQL database.

mysql -h 127.0.0.1 -u <accountname> -P <localport>

<accountname> is a required parameter. accountname specifies the user name for the database.
<localport> is a required parameter. localport specifies the local listening port that you defined in Step 5.

Once connected, you can run O&M commands.

[root@localhost ~]# mysql -h 127.0.0.1 -u <accountname> -P 33061
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4041
Server version: 8.0.13 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>