How can I connect to a private Amazon RDS DB instance from a local machine using an Amazon EC2 instance as a bastion host?

Short description

To use an Amazon EC2 instance as a jump server to connect to a private Amazon RDS DB instance from a local machine, follow these steps:

1.    Launch and configure your EC2 instance, and then configure the network setting of the instance.

2.    Configure the RDS DB instance's security groups.

3.    Connect to the RDS DB instance from your local machine.

Important: To connect to a private Amazon RDS or Amazon Aurora DB instance, it's a best practice to use a VPN or AWS Direct Connect. If you can't use these options, then use a bastion host. The following example configuration uses security groups to restrict access. However, you can restrict the network access control list (network ACL) of subnets to make the connection more secure. You can also restrict the route scope of your internet gateway to use a smaller range instead of 0.0.0.0/0. For example, when you add the internet gateway, add only the required CIDR range in the routing table for the destination. For more information, see Example routing options.

Resolution

The following example configuration is for an Amazon RDS for MySQL instance that's in an Amazon Virtual Private Cloud (Amazon VPC). The instance has security groups set up for an EC2 instance.

Launch and configure the EC2 instance

1.    Open the Amazon EC2 console, and then choose Launch instance.

2.    Select an Amazon Machine Image (AMI).

3.    Choose an instance type, and then choose Next: Configure Instance Details.

4.    For Network, choose the VPC that the RDS DB instance uses.

5.    For Subnet, select the subnet that has an internet gateway in its routing table. If you don't already have an internet gateway, then you can add it to the subnet after the EC2 instance is created.

6.    For Auto-assign public IP, make sure that Enable is selected.

7.    Choose Next: Add Storage, and then modify storage as needed.

8.    Choose Next: Add Tags, and then add tags as needed.

9.    Choose Next: Configure Security Group, choose Add Rule, and then enter the following: Type: Enter Custom TCP Rule. Protocol: Enter TCP. Port Range: Enter 22. Source: Enter the IP address of your local machine. By default, the source IP address is open to all, but you can restrict access to your local public IP address.

10.    Choose Review and Launch.

11.    Choose Launch.

Configure the RDS DB instance's security groups

Note: To connect one or more EC2 instances to an RDS database automatically, see Automatically connect an EC2 instance to an RDS database.

1.    Open the Amazon RDS console, and then choose Databases from the navigation pane.

2.    Choose the name of the RDS DB instance. Or, create an RDS DB instance if you don't already have one.

3.    Choose the Connectivity & security tab.

4.    From the Security section, choose the link under VPC security groups.

5.    Select the security group, choose Actions, and then choose Edit inbound rules.

6.    Choose Add rule, and then enter the following: Type: Enter Custom TCP Rule. Protocol: Enter TCP. Port Range: Enter the port of your RDS DB instance. Source: Enter the private IP address of your EC2 instance.

7.    Choose Save.

This configuration for the security group allows traffic from the EC2 instance's private IP address. If the EC2 instance and the RDS DB instance use the same VPC, then you don't need to modify the RDS DB instance's route table. If the VPC is different, then create a VPC peering connection to allow connections between those VPCs.

Note: If you use a more scalable solution, then be careful. For example, if you use the security group ID in a security group rule, then make sure that it doesn’t restrict access to one instance. Instead, configure the rule to restrict access to any resource that uses the specific security group ID.

Connect to the RDS DB instance from your local machine

Depending on the client that you use, the steps for connecting to the RDS DB instance can vary. For more information, see Connecting to an Amazon RDS DB instance. If you use MySQL, it's a best practice to use SSL to encrypt the connection between the client application and Amazon RDS.

The following example uses the MySQL Workbench client to connect to the bastion host:

1.    Start a new connection, and then select Standard TCP/IP over SSH for the Connection Method.

2.    For SSH settings, enter the following details about the EC2 instance: Auto-assign Public IP: Make sure that Enable is selected for the DNS Hostnames option. SSH Hostname: Enter the public DNS name of the EC2 instance or its public IP address. SSH Username: Enter the user name for your EC2 instance. For example, ec2-user is the user name for EC2 Linux machines. SSH Key File: Select the private key that you used when you created the EC2 instance. Note: An EC2 instance that's launched with a public IP address has a public DNS if the VPC where it was created has DNS Hostnames activated.

3.    Enter the following details for the MySQL instance settings: MySQL Hostname: Enter the RDS DB instance endpoint. MySQL Server port: Enter 3306. Or, if you use a custom port, then enter the custom port number. Username: Enter the user name of the RDS DB instance. Password: Enter the password of the RDS DB instance.

4.    Choose Test Connection.

5.    After the connection is successful, enter a connection name, and then save the connection.

To connect from your local MySQL client to a private RDS instance using an SSH tunnel, see the following commands.

Linux or macOS:

1.    Run the following command to configure an SSH tunnel:

ssh -i "YOUR_EC2_KEY" -L LOCAL_PORT:RDS_ENDPOINT:REMOTE_PORT EC2_USER@EC2_HOST -N -f

Note: Replace YOUR_EC2_KEY, LOCAL_PORT, RDS_ENDPOINT, REMOTE_PORT, EC2_USER, and EC2_HOST with your relevant information.

After the custom fields are completed in the preceding command, the command looks similar to the following example:

ssh -i "ec2Key.pem" -L 3336:rdsinstance.xxx.xxx.rds.amazonaws.com:3306 ec2-user@ec2-34-244-136-223.xxxcompute.amazonaws.com -N -f

2.    Run the following commands to test and confirm that the tunnel is listening on the local port:

lsof -i4 -P | grep -i "listen" | grep LOCAL_PORT
nc -zv 127.0.0.1 LOCAL_PORT

Note: Replace LOCAL_PORT with the number of your local port.

3.    If your tunnel is successfully listening on your local port, then you see an output similar to the following one:

lsof -i4 -P | grep -i "listen" | grep 3336
ssh       17692 user    8u  IPv4 0x3bc46bcfeffce12f      0t0  TCP localhost:3336 (LISTEN)

nc -zv 127.0.0.1 3336
Connection to 127.0.0.1 port 3336 [tcp/directv-tick] succeeded!

4.    Run the following command to connect to your RDS instance from your local machine using your EC2 instance as a bastion host:

mysql -h 127.0.0.1 -P LOCAL_PORT -u RDS_USER -p

Note: Replace LOCAL_PORT with the number of your local port and DB_USER with your RDS DB user name.

---

Related information

How do I resolve problems when connecting to my Amazon RDS DB instance?