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
AWS System Manager IAM Users and Groups
First we will need to create an EC2 Instance
Next up we will create our RDS instance
Checkpoint 🎉
So right now we have
- Private EC2 instance
- 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