Using AWS CLI and AWS SSM (Systems Manager) to connect to RDS instance

Overview

At the end of this article, you will be able to connect to RDS and EC2 instances without using a bastion host or have any public facing infrastructure. We will be using AWS CLI, AWS SSM and SSH to perform tunneling.

Prerequisite

AWS CLI

Installing, updating, and uninstalling the AWS CLI version 2 on macOS

AWS System Manager IAM Users and Groups

Setting up AWS Systems Manager

First we will need to create an EC2 Instance

Launch instance

Select the free tier AMI

Select t2.Mirco

This is important — select the SSM IAM Role which you have created before. FYI[https://docs.aws.amazon.com/systems-manager/latest/userguide/systems-manager-setting-up.html]

Next

Next

So by default we will see a SSH rule — remove it because we wont be using it

Make sure the security group is empty.

Launch your EC2 Instance

Next up we will create our RDS instance

Create database

Create a free tier RDS

After creating the DB — click on the security group under [ VPC Security Groups ]

Click in, we will need to edit the inbound rules

Select edit inbound rules

Make sure to add the custom TCP with ur EC2 instance private IPv4 addresses

Checkpoint 🎉

So right now we have

1. Private EC2 instance
2. Private RDS instance which accept inbound traffic from EC2

Now we need to add user & ssh key to the EC2 instance

Open terminal — follow me closely as it get messy along the way

# start an ssm session to connect to your EC2
$ aws ssm start-session --target EC2_INSTANCE_ID
# do note that youre starting a session with dummy1 - your IAM user
Starting session with SessionId: dummy1-EC2_INSTANCE_ID
Welcome ssm-user!
activity is being logged."ed in to a production instance. Note that all session
You have logged in to a production instance. Note that all session activity is being logged.
# change directory to /home 
sh-4.2$ cd /home
# add user - this will be the user you created in IAM and configure in AWS CLI
sh-4.2$ sudo useradd dummy1
# create a password for dummy1
sh-4.2$ sudo passwd dummy1
Changing password for user dummy1.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
# list all directory to check that your dummy1 is created
sh-4.2$ ls
dummy1 ec2-user  ssm-user
# login as dummy1
sh-4.2$ sudo su dummy1
# change directory to dummy1
[dummy1@ip-XXX-XX-XX-XXX home]$ cd dummy1
# create a .ssh folder with permission
[dummy1@ip-XXX-XX-XX-XXX ~]$ (umask 077 && test -d ~/.ssh || mkdir ~/.ssh)
# create a authorized_keys with permimssion
[dummy1@ip-XXX-XX-XX-XXX ~]$ (umask 077 && touch ~/.ssh/authorized_keys)
# list all directory to check that .ssh is creted
[dummy1@ip-XXX-XX-XX-XXX ~]$ ls -la
total 12
drwx------ 3 dummy1 dummy1  74 Feb  7 09:16 .
drwxr-xr-x 5 root   root    52 Feb  7 09:16 ..
-rw-r--r-- 1 dummy1 dummy1  18 Jul 15  2020 .bash_logout
-rw-r--r-- 1 dummy1 dummy1 193 Jul 15  2020 .bash_profile
-rw-r--r-- 1 dummy1 dummy1 231 Jul 15  2020 .bashrc
drwx------ 2 dummy1 dummy1  29 Feb  7 09:17 .ssh
# edit the authorized_keys - copy your local machine public key and paste it inside
[dummy1@ip-XXX-XX-XX-XXX ~]$ vim .ssh/authorized_keys
# to check that you have successfully save your public key 
[dummy1@ip-XXX-XX-XX-XXX ~]$ cat .ssh/authorized_keys
#ssh pub key will appear

Open a new terminal and start another SSM session

$ aws ssm start-session --target i-EC2_INSTANCE_ID \
--document-name AWS-StartPortForwardingSession \
--parameters '{"portNumber":["22"],"localPortNumber":["9999"]}'
Starting session with SessionId: dummy1-EC2_INSTANCE_ID
Port 9999 opened for sessionId dummy1-EC2_INSTANCE_ID.
Waiting for connections...
# this will only appear if you connected to it via ssh in the next step
Connection accepted for session [dummy1-EC2_INSTANCE_ID]

Note: The terminal will kinnda freeze as it is maintaining a tunnelling connection from port 9999 on localhost to port 22 on EC2_INSTANCE_ID

Open another terminal and SSH in

# replace dummy1 with your IAM user
$ ssh dummy1@localhost \
-i dummy \
-p 9999 \
-N \
-L 8888:RDS_ENDPOINT_URL:3306

Note: The terminal will freeze as well but its normal because it is maintaining a tunnelling connection from port 8888 on localhost to port 22 on EC2_INSTANCE_ID

Try to connect to your database now, the connection details will be

• Host: localhost
• Port: 8888
• Username / Password: as per the the database credential

You should now have access to your RDS instance.

Some error which I encountered along the way:

An error occurred (TargetNotConnected) when calling the StartSession operation: i-EC2_INSTANCE_ID is not connected.

• The IAM role is different from your AWS CLI user role
• The region is different from your AWS CLI user account

Lost connection to MySQL server at ‘waiting for initial communication packet’, system error: 60 (2013)

• did not configure security group properly for RDS to accept connection from EC2

permission denied (publickey,gssapi-keyex,gssapi-with-mic)

• ssh key in different user folder in EC2 instance

Conclusion

There are simpler way to upload the SSH public key to the server such as using ec2-instance-connect send-ssh-public-key function but i faced numerous error while using that so i did a workaround which is to upload the SSH key to the server.

I will revisit this issue again and update this article, or let me know if you know how to upload ssh public key to the instance in a more seamless way.

Reference

How to Use SSH Public Key Authentication

How to Create Users in Linux (useradd Command)