Session Manager is a fully managed AWS System Manager (SSM) capability that allows you to monitor infrastructure instances, edge devices, virtual machines (VMs), and on-premises servers. 

Session Manager provides a secure and auditable way to manage your infrastructure while complying with corporate policies and strict security practices across cross-platforms.

The Problem

Let’s say you are trying to use a GUI tool on your local machine (e.g., Sqlectron) to manage MySQL database instances inside the private subnet of an AWS VPC.

The scenario here is that we need to open a 3306 TCP port to allow connection to this database over the internet. However, this might not be the best way from a security perspective.

Read also: How To Use Amazon S3 For Personal Backup?

There are several ways to connect the database securely following AWS best practices, one of which has been explained below: 

The Fix

Follow the steps to connect the database more securely:

1. Please set up a Bastion (jump host) and install all its tools. [Not recommended because that would require much extra work to set up and maintain.]
2. Using port forwarding in AWS System Manager Session Manager can be used to connect to remote databases from a local client.

Prerequisite

• Session Manager can be launched using AWS CLI, so we will use AWS CLI for this process.
• AWS CLI is already set up and configured on your local machine. Also, configure the credential chain correctly.
• The AWS Session Manager plugin for AWS CLI must already be installed on your machine.
• A System Manager managed EC2 instance which is either on the same network or has a connection with the remote database.
• Check to see if your database instance can be managed remotely, meaning that no remote connections are blocked by the security group or firewall connected to it.
• Database client sqlectron is installed on your machine.

Step 1: Setup SSM managed instance

To set up the instance, create an EC2 instance, an IAM role with the AmazonSSMManagedInstanceCore policy, assign that to the model and install the SSM agent on that server.

Know more about: AWS Well-Architected Framework Security Pillar

Then, run the following command after launching your preferred terminal application. Substitute your SSM-SSM-managed instance’s EC2 instance id for .

• Note: Allow outbound traffic, and configure your managed instances to allow  HTTPS (port 443) to System Manager endpoints if you don’t use a VPC endpoint.

aws ssm start-session --target 

Since ,the connection was successful, we can move on to the next step.

Step 2: Open a connection forwarding session to a remote port on the MySQL server

Create an RDS instance or create your database server on a private subnet. And to initiate the port forwarding session, you will execute a System Manager Document.

Please run the following command on your terminal, replacing SSM-managed-instance-id and remote-database-host-name with their corresponding values based on your setup.

aws ssm start-session --target  --document-name AWS-StartPortForwardingSessionToRemoteHost --parameters '{"portNumber":["3306"],"localPortNumber":["1053"],"host":[" remote-database-host-name"]}'

Note: Since the port on local should be free, we are using local port 1053. However, as a preference, a different local port can also be chosen

Also Read: Microsoft Azure vs AWS vs Google Cloud – Comparison

Step 3: Verify connection forwarding is working (Optional)

To verify the connection forwarding is working, run the following command in a new terminal window, which will allow you to connect to the database over the CLI session:

mysql -h 127.0.0.1 --port 1053 -u admin -p

The database is connected successfully from the local using the 1053 local port.

Step 4: Connect using Sqlectron

Launch SQL Sqlectron on your local machine. Suppose you have a connection profile for this database. In that case, you may use it else create a new profile based on the connection parameters such as Username, Database type, and Server address, which are specific to your database.

• Please note that instead of using your database’s remote IP or hostname, you will use a localhost IP address or localhost in the Server field.

Conclusion:

Using AWS System Manager Session Manager to connect to a remote database from a local client is secure and effective for managing your infrastructure .In this blog, we have explored the steps to set up a port forwarding session to connect your database using a GUI tool like Sqlectron.