Connecting to databases via SSH Tunneling is something developers and IT professionals often need to do. And HeidiSQL is one of the more popular open-source database tools – with support for all the major databases including MySQL, MariaDB, Microsoft SQL Server, PostgreSQL, etc.
While HeidiSQL fully supports SSH tunneling, there doesn’t seem to be any documentation about how to accomplish this. And if you’re looking for instructions to do this for Amazon RDS specifically, you’d find even less information/documentation online.
So, after struggling with this for a few hours and figuring it out, I decided to put these instructions together to help others who may be attempting similar stuff. The instructions here can easily be adapted to any database provider (local or remote/cloud). But I will be explaining this for Amazon RDS particularly.
Keep reading or jump straight to the steps below.
First, Why SSH Tunneling At All?
Note that it is very possible to access your databases WITHOUT SSH tunneling – especially if you are the database administrator or have full control over all the connected systems. You just need to open the right ports to the right IP addresses (or even to the world). But this is generally not recommended.
SSH tunneling has become an industry standard security practice. And I usually recommend it in most cases.
While I won’t attempt to do a thorough explanation of SSH tunneling in this post (that’s a subject for a future article), the basic reasoning behind SSH tunneling for database access is that you don’t need to open any database ports to the world on the database machine at all (for obvious security reasons). Instead, you simply grant database access to one machine on your network. This machine would be one that already connects to the outside world via SSH. It acts as a middle-man between clients and the database. Now with SSH tunneling, you can let any outside clients connect to your database by first connecting to the “middle-man” machine via SSH.
RELATED READING: Securing Your Amazon EC2 Instance
This technique prevents the database from being a subject to brute force attacks. For MySQL for example, the default port is 3306 and if left open to the world, tons of bots would often try multiple username and password combinations targeting MySQL on port 3306 in an attempt to gain control over the database. SSH on the other hand, is a much more secure form of server access. And since clients need SSH access already for other applications besides the database, using SSH for database access as well helps avoid opening more ports to more endpoints.
Setting Up SSH Tunneling On HeidiSQL For Amazon RDS
For this example, I will be accessing a MariaDB instance on Amazon RDS.
When you created your RDS database instance, you probably either added it to an existing VPC security group or a new VPC security group was created for you automatically. If the VPC security group was automatically created, the name would be something like “rds-launch-wizard”.
NOTE: The “rds-launch-wizard” security group will only show up on your EC2 dashboard (under security groups). It will NOT show under RDS security groups.
The assumption here is that you already have an Amazon EC2 instance which you will be using for SSH. This instance also belongs to its own VPC security group. Let’s call this security group “ec2-vpc-security-group”.
Step 1: Go to Amazon EC2 Dashboard >> Security Groups. Click on the “rds-launch-wizard” security group (or whatever the security group of your database is named).
Step 2: View the inbound rules and click to edit them.
Step 3: Add a new rule. Type should be “MySQL/Aurora”. Protocol and Port Range would be TCP and 3306 (automatically selected based on Type).
Step 4: In the Source dropdown box, leave “Custom” selected. And in the Source text box, start typing the name of the security group of your EC2 instance (ec2-vpc-security-group). A list of security groups will be provided when you start typing. Select your EC2 security group.
Alternatively, you could also just enter the IP address of your EC2 instance. But I prefer using the security group method because the name of the security group of my EC2 instance is not likely to change. However, over the course of time, I may assign different Elastic IP addresses to the same EC2 instance – which would mean I need to also edit any rules that use the old IP address specifically. Using a named security group saves us from the extra potential work.
Step 5: From a terminal connected to your EC2, confirm that you have connectivity to MySQL with the following command:
mysql -u USERNAME -h HOSTNAMEORIP DATABASENAME -p
Of course, supply the values accordingly.
Step 6: If the above command connects you to MySQL without issues, proceed to the actual SSH tunneling steps below.
Step 7: Create a new session in HeidiSQL.
Step 8: On the settings tab…
For Network type, select “MySQL (SSH tunnel)”. This will make another tab – “SSH Tunnel” – available.
Step 9: For Hostname/IP, enter the RDS endpoint (you will find this url on your Amazon RDS database instance information).
Step 10: For “User” enter the RDS user.
Step 11: Enter the RDS user’s password.
Step 12: Leave the port as “3306”.
Step 13: On the SSH Tunnel tab…
Notice the “plink.exe location” field. By default, this field is blank. HeidiSQL uses plink.exe for SSH tunneling and plink.exe is a small application that comes with the popular PuTTY application.
So, you may need to install PuTTY if you don’t already have it. And then enter the appropriate value in the “plink.exe location” field. See an example on my local machine below.
Step 14: For “SSH host + port” enter the IP address of your Amazon EC2 instance. The port is usually port 22. If you did not specifically change this port, then enter “22” in the port field.
Step 15: Username and password will the same used to connect to your EC2 instance over SSH (these may not be the same as your database username and password).
If you use a private key file for connection instead of a password, enter that instead.
Step 16: Set the “plink.exe timeout” value to 15.
Step 17: For “Local port”, leave the default value of 3307.
Step 18: On the Advanced tab…
Set the “Ping every X seconds” value to 15.
Step 19: Save the settings and test your connectivity.
I think one of the really cool features of Amazon security groups is the ability to grant access to another security group as demonstrated in step #4 above. Just make sure that the EC2 security group used in step #4 is properly set up. Because, if for example, you open port 3306 to the world on “ec2-vpc-security-group”, then the whole purpose of SSH tunneling as described in this article, would be defeated.
Have you used SSH tunneling as a secure method of accessing Amazon RDS instances via EC2? Did this tutorial help you accomplish that? Please feel free to share your comments and experiences in the comments section below.