Connect to a private RDS instance using SSH & AWS SSM (Systems Manager)

Aug 1, 2020

Objective

You have an AWS RDS instance which is on a private subnet and therefore not publicly accessible and you wish to connect to this RDS instance remotely without installing a bastion server or having any public-facing ec2 instances.

For this article, we are making the following assumptions:-

There is no bastion instance to provide external access
There are no security groups which provide external access
There is an ec2 instance in a private subnet which has access to the RDS instance
This ec2 instance is running Ubuntu

Prerequisites

Install ec2-instance-connect on EC2 instance - This can be done by connecting to an existing ec2 instance using AWS Session Manager and run
```
apt-get install ec2-instance-connect
```
Note: If you are using autoscaling groups or blue/green deployments, you can add this to the ec2 user data make sure that ec2-instance-connect is installed when a new server is created.
Install session-manager-plugin locally (https://docs.aws.amazon.com/systems-manager/latest/userguide/session-manager-working-with-install-plugin.html)

Create an SSH Key

You will need an SSH to connect to the ec2 instance. If you already have one you wish to use then you can skip to the next step, otherwise, you can create one via

ssh-keygen -f my_rsa

This will create SSH private and public keys named my_rsa and my_rsa.pub.

Push your SSH Key to the target instance

To connect to the ec2 instance, you will need to send the SSH public key to the desired ec2 instance via

aws ec2-instance-connect send-ssh-public-key \
  --instance-id i-0c6e3bd52bbb2373c \
  --availability-zone eu-west-1a \
  --instance-os-user ubuntu \
  --ssh-public-key file:///my_rsa.pub

Note: The SSH public keys are only available for one-time use for 60 seconds in the instance metadata. To connect to the instance successfully, you must connect using SSH within this time window. Because the keys expire, there is no need to track or manage these keys directly, as you did previously.

Start Session

You can now start an AWS System Manager session and enable port forwarding. In this case, you are going to forward port 9999 on your local machine to port 22 on the target ec2 instance.

aws ssm start-session \
  --target i-0c6e3bd52bbb2373c \
  --document-name AWS-StartPortForwardingSession \
  --parameters '{"portNumber":"22", "localPortNumber":"9999"}'

Note: This will appear to hang because it is maintaining a tunnelling connection from port 9999 on localhost to port 22 on i-07edf50160ab3172

Open tunnel to RDS (in another terminal)

Now you need to open a tunnel to your RDS instance via the AWS System Manager session you created above.

ssh ubuntu@localhost \
-p 9999 \
-N \
-L 3388:production-database.inzy2e1e4v6s.eu-west-1.rds.amazonaws.com:3306

Note: This will appear to hang because it is maintaining a tunnelling connection between port 3388 on localhost to port 3306 on production-database.inzy2e1e4v6s.eu-west-1.rds.amazonaws.com via i-07edf50160ab3172

Connect to your RDS Instance

With the above steps complete, you can now use your favourite database client (SequelPro, HeidiSQL etc) to connect to your database. The connection details will be:-

Host: localhost
Port: 3388
Username / Password: as per the live database you are trying to access

You should now have access to your RDS instance.

References

Using Amazon EC2 Instance Connect for SSH access to your EC2 Instances - https://aws.amazon.com/blogs/compute/new-using-amazon-ec2-instance-connect-for-ssh-access-to-your-ec2-instances/
Install the Session Manager Plugin for the AWS CLI - https://docs.aws.amazon.com/systems-manager/latest/userguide/session-manager-working-with-install-plugin.html

Originally published at https://chrisshennan.com/blog/using-aws-systems-manager-and-ssh-to-access-an-rds-instance