• Skip to main content
  • Skip to primary sidebar

Technical Notes Of
Ehi Kioya

Technical Notes Of Ehi Kioya

  • Forums
  • About
  • Contact
MENUMENU
  • Blog Home
  • AWS, Azure, Cloud
  • Backend (Server-Side)
  • Frontend (Client-Side)
  • SharePoint
  • Tools & Resources
    • CM/IN Ruler
    • URL Decoder
    • Text Hasher
    • Word Count
    • IP Lookup
  • Linux & Servers
  • Zero Code Tech
  • WordPress
  • Musings
  • More
    Categories
    • Cloud
    • Server-Side
    • Front-End
    • SharePoint
    • Tools
    • Linux
    • Zero Code
    • WordPress
    • Musings
Home » AWS, Azure, Cloud » Connect To Amazon RDS (Or Any Database) Using HeidiSQL Via SSH Tunneling

Connect To Amazon RDS (Or Any Database) Using HeidiSQL Via SSH Tunneling

By Ehi Kioya 2 Comments

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.
HeidiSQL SSH Tunneling Settings

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.
PLink.exe Location Setting (HeidiSQL SSH Tunneling)

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.

Found this article valuable? Want to show your appreciation? Here are some options:

  1. Spread the word! Use these buttons to share this link on your favorite social media sites.
  2. Help me share this on . . .

    • Facebook
    • Twitter
    • LinkedIn
    • Reddit
    • Tumblr
    • Pinterest
    • Pocket
    • Telegram
    • WhatsApp
    • Skype
  3. Sign up to join my audience and receive email notifications when I publish new content.
  4. Contribute by adding a comment using the comments section below.
  5. Follow me on Twitter, LinkedIn, and Facebook.

Related

Filed Under: AWS, Azure, Cloud, Cloud Computing, Database, Linux & Servers Tagged With: Amazon RDS, Cloud Computing, Database, HeidiSQL, Linux, SSH Tunneling

About Ehi Kioya

I am a Toronto-based Software Engineer. I run this website as part hobby and part business.

To share your thoughts or get help with any of my posts, please drop a comment at the appropriate link.

You can contact me using the form on this page. I'm also on Twitter, LinkedIn, and Facebook.

Reader Interactions

Comments

  1. Afapoker says

    July 26, 2018 at 4:19 am

    You should be a part of a contest for one of the finest sites on the net.
    I am going to recommend this website!

    Reply
    • Ehi Kioya says

      July 26, 2018 at 4:31 am

      Thanks for the kind words 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

26,207
Followers
Follow
30,000
Connections
Connect
14,641
Page Fans
Like

POPULAR   FORUM   TOPICS

  • How to find the title of a song without knowing the lyrics
  • Welcome Message
  • How To Change Or Remove The WordPress Login Error Message
  • The Art of Exploratory Data Analysis (Part 1)
  • Replacing The Default SQLite Database With PostgreSQL In Django
  • Getting Started with SQL: A Beginners Guide to Databases
  • The Best Web Safe Fonts In Web Development
  • What’s the fuss about HMAC Validation?
  • Data Science Insights: Regression Analysis
  • How to Implement Local SEO On Your Business Website And Drive Traffic
  • Recently   Popular   Posts   &   Pages
  • Actual Size Online Ruler Actual Size Online Ruler
    I created this page to measure your screen resolution and produce an online ruler of actual size. It's powered with JavaScript and HTML5.
  • Allowing Multiple RDP Sessions In Windows 10 Using The RDP Wrapper Library Allowing Multiple RDP Sessions In Windows 10 Using The RDP Wrapper Library
    This article explains how to bypass the single user remote desktop connection restriction on Windows 10 by using the RDP wrapper library.
  • WordPress Password Hash Generator WordPress Password Hash Generator
    With this WordPress Password Hash Generator, you can convert a password to its hash, and then set a new password directly in the database.
  • Forums
  • About
  • Contact

© 2021   ·   Ehi Kioya   ·   All Rights Reserved
Privacy Policy