It is best practise to place your database servers into a private subnet. By definition a private subnet in Amazon Web Service (AWS) is not reachable from the internet. So there is no internet gateway assigned to it. With proper security groups configured you restrict the database access to that (web) servers which need access only.
But that configuration makes it more complicated for managing the database servers, e.g. connecting with SQL clients. Instead of putting your database instance into a public subnet you can configure a bastion host (aka jump box) for acting as an intermediate server. The following picture gives you a quick overview:
You place a small EC2 instance (e.g. t2.nano) into a public subnet within your VPC. After that you can connect with e.g. Putty (for Windows) to establish a SSH connection and configure it to create an SSH tunnel for the database port.
Please note your security group settings. The bastion host has inbound access for port 22 and your source IP address only (or more which is not recommended). The security group for the RDS instance will allow inbound access for port 3306 (for MySQL) with restriction to the security groups which needs access to the database server (in our case the bastion host). With that configuration you limit the database access to the minimum needed.
At first enter the hostname with ec2-user. This is the public IP address of your bastion host:
After that you define your private key for authentication:
In the last step you enter the SSH tunnel settings for your database instance. In this example we create a tunnel for port 3306 on your local computer to port 3306 on the RDS instance host (DNS name). This is possible, because the bastion host and the database instance are placed within the same VPC and the routing table allows the communication between both subnets.
After establishing the putty connection we can connect to our database on localhost, port 3306:
Making it more convenient …
This is all fine but we can do it even more convenient. One solution is to place all Putty settings into a batch file:
@ECHO OFF SET PUTTY_EXE=C:\Putty\putty.exe start %PUTTY_EXE% firstname.lastname@example.org -i d:\my_private_key.ppk -L 3306:demo.abc.eu-central-1.rds.amazonaws.com:3306
After saving it to a batch file we can start the SSH tunnel by a double-click.
Another solution depends on your SQL Client. In some clients like e.g. DBWeaver or the MySQL Workbench you can configure a TCP connection over SSH directly. With that option you can configure it all in that client. No Putty configuration or batch file to be started.
Be sure to select „Standard TCP/IP over SSH“ and the correct private key format.