Setting up an SSH Tunnel for a database in Amazon Web Services

If a database is in a private subnet in your Amazon Web Services (AWS) account, you can use an SSH tunnel to connect Stitch. The approach in this tutorial will use a publicly accessible EC2 instance, or SSH server, instance to create the connection. The SSH server will act as an intermediary, forwarding the traffic from Stitch through an encrypted tunnel to the database in the private subnet.

The approach outlined in this guide is applicable to the integrations and destinations listed in the next section.

Before you get started, note that an SSH tunnel isn’t necessarily more secure than a direct connection. An SSH tunnel is only as secure as the monitoring and hardening you perform on the SSH server hosting the tunnel.

If you have questions or concerns about Stitch security, refer to the Security FAQ.

---

Databases this guide applies to

This guide is applicable to the following integrations and destinations:

Integrations	Destinations
Amazon Aurora MySQL RDS Amazon Aurora MySQL RDS (v2) Amazon Aurora PostgreSQL RDS (v2) Amazon Microsoft SQL Server RDS Amazon MySQL RDS Amazon Oracle RDS Amazon PostgreSQL RDS Amazon PostgreSQL RDS (v2)	Amazon Aurora MySQL Amazon Aurora PostgreSQL-RDS Amazon PostgreSQL-RDS Amazon RDS for MySQL Amazon RDS for SQL Server Amazon Redshift

For SSH for self-hosted databases, refer to the SSH for self-hosted databases guide.

For SSH for Microsoft Azure databases, refer to the SSH for Microsoft Azure guide.

---

Prerequisites

• Privileges in AWS that allow you to:

  • Create/manage EC2 instances. This is required to create the SSH server.
  • Create/manage Security Groups. This is required to whitelist Stitch’s IP addresses.
  • View database details. This is required to retrieve the database’s connection details.

• Some familiarity with Linux and the command line. In this tutorial, you’ll create a Linux user for Stitch to ensure access via SSH. While we’ve provided the commands you’ll need to create the user, you should know how to access a server using the command line and feel comfortable running commands.

• An SSH client, if using a Windows operating system. Some versions of Windows don’t include SSH commands, so you may need to install a Windows-based SSH client before proceeding. Windows 10, however, does include OpenSSH, which is suitable for this tutorial.

---

Step 1: Verify your Stitch account's data pipeline region

First, you’ll log into Stitch and verify the data pipeline region your account is using. Later in this guide, you’ll need to grant Stitch access to the SSH server by whitelisting our IP addresses.

The IP addresses you’ll whitelist depend on the Data pipeline region your account is in.

1. Sign into your Stitch account, if you haven’t already.
2. Click User menu (your icon) > Edit User Settings and locate the Data pipeline region section to verify your account’s region.

3. Locate the list of IP addresses for your region:

   • North America region IP addresses

   • Europe region IP addresses

Keep this list handy - you’ll need it later.

---

Step 2: Verify the database's VPC

Next, you’ll log into AWS and verify the Virtual Private Cloud (VPC) the database is in. The SSH server you’ll create in Step 2 must reside in the same VPC as the database.

First, log into your AWS account. Then use the instructions below for the type of database you’re connecting to locate the VPC.

Keep the name of the VPC handy - you’ll need it to complete the next step.

---

Step 3: Create an SSH server in your VPC

In this step, you’ll launch an EC2 instance to serve as the SSH server. This publicly accessible instance will act as an intermediary, forwarding the traffic from Stitch through an encrypted tunnel to the database in the private subnet.

Note: This instance must reside in the same VPC as the database. Refer to Step 1 if you aren’t sure which VPC to use.

• Step 2.1: Configure the SSH server

• Step 2.2: Configure the SSH server’s Security Group

• Step 2.3: Review and launch the SSH server

Step 3.1: Configure the SSH server

The first part of creating an SSH server in your VPC is configuring the instance.

1. Navigate to the EC2 Management Console in AWS. If you use the Services menu (top left corner), click the EC2 option under the Compute section.
2. On the EC2 Dashboard, click the Launch Instance button.

3. Next, you’ll be asked to select the Amazon Machine Image, or AMI, that will be used to launch the instance. For this tutorial, we’ll be using a Linux-based AMI.

   Click the Select button next to the AMI you want to use.

4. On the next page, you’ll select the instance type. Generally, a small instance will work just fine. For example: t2.small. You can find more info about instance types on Amazon’s website.

   After you select the instance type, click the Configure Instance Details button in the lower right corner of the page to continue.

5. On the Configure Instance Details page, fill in the following fields:
   • Network: Select the same VPC that the database is in. Refer to Step 1 if you aren’t sure which VPC to use.
   • Subnet: Select the public subnet you want to associate with the EC2 instance. Note: This must be a public subnet - that is, a subnet with an Internet Gateway - as this will automatically assign a public IP address to machines in the subnet.

   • Auto-assign public IP: Select Enable from the drop-down. This is required to ensure the machine has a public IP address.

     Here’s a look at our setup:

     

6. Click the Next: Add Storage button in the lower right corner of the page to continue.
7. If you’re only using the machine as a bastion (which is what we’re doing in this tutorial), adding storage and tags are unnecessary. Skip over these pages until you reach the Configure Security Group page.

Step 3.2: Configure the SSH server's Security Group

The second part of creating an SSH server in your VPC is configuring the security group. During this step, you’ll add Stitch’s IP addresses to the security group, which will allow traffic from Stitch to access the SSH server.

You can create a new Security Group or update an existing one. For this tutorial, we’ll create a new group.

1. In the Assign a security group field, select Create a new security group.
2. In the Security group name field, enter a unique name for the Security Group. For example: Stitch Bastion
3. In the table below the Description field, you’ll add Stitch’s IP addresses to the security group:

   • Type: If the default SSH port for your server is 22, set this to SSH.

     If it’s something else, set this to Custom TCP Rule.

   • Protocol: This will default to TCP - leave it as-is.

   • Port Range: This is the number of the SSH port associated with the server. If you selected SSH as the Type, this will default to 22.

     If you selected Custom TCP Rule, enter the number of the SSH port in this field.

   • Source: This should default to Custom. In the field next to the Source drop-down menu, paste one of the Stitch regional IP addresses you retrieved in Step 1.
4. Click the Add Rule button to add another rule.

5. Repeat steps 3 and 4 until all of the Stitch IP addresses for your region have been added to the Security Group.

   For example: This is the Security Group with Stitch’s North America region IP addresses:

   

6. When finished, click the Review and Launch button in the lower right corner of the page.

Step 3.3: Review and launch the SSH server

The last step is to review the settings for the SSH server and launch it.

Review the instance’s settings, paying particular attention to the fields highlighted in the image below:

• The Security Groups section should list either a new Security Group for Stitch OR an existing group that contains group rules for Stitch’s IP addresses. If it doesn’t, refer to Step 2.2 for instructions.
• In the Instance Details section:
  • Network: This field should contain the ID of the same VPC that the database is in.
  • Subnet: This field should contain the ID of a public subnet.
  • Assign Public IP: This field should have a value of Yes.
  • Assign IPv6 IP: This field should contain the value Enable. This indicates that the Auto-assign public IP setting from Step 2.1 was defined correctly.

After you’re reviewed the instance’s settings, click the Launch button in the lower right corner to launch the instance.

Note: It may take a few minutes for the instance creation process to complete. The status in the EC2 Dashboard page will change to Available when the instance is ready.

---

Step 4: Enable the SSH server to access the database

After the EC2 instance has finished initializing, you can move onto configuring the access rules for database. In this section, you’ll create a VPC Security Group that will forward traffic from the SSH server (EC2 instance) to the database in the private subnet.

• Step 3.1: Retrieve the SSH server’s connection details

• Step 3.2: Create a database Security Group and whitelist the SSH server

• Step 3.3: Associate the Security Group with the database

Step 4.1: Retrieve the SSH server's connection details

In this step, you’ll retrieve the SSH server’s public DNS (IPv4) and private IP address. This info will be used to set up the VPC security group in the next step, and eventually complete the setup in Stitch.

1. Navigate to the EC2 Management Console in AWS. If you use the Services menu (top left corner), click the EC2 option under the Compute section.
2. On the EC2 Dashboard, click the Instances option under Instances in the menu on the left side of the page.
3. In this list of instances that displays, locate the SSH server (EC2 instance) you created in Step 2.
4. Click the instance. This will display the instance’s details in the Description tab.

5. Locate the Public DNS (IPv4) and Private IPs fields in this tab:

   

6. Copy these values somewhere handy - you’ll need them to complete the setup. Make sure you know which value is which - confusing these values will prevent a successful connection.

Step 4.2: Create a database Security Group and whitelist the SSH server

In this step, you’ll whitelist the SSH server’s private IP address in a Security Group. This will allow traffic from the SSH server to access the database.

You can create a new Security Group or update an existing one. For this tutorial, we’ll create a new group.

1. In the menu on the left side of the page, click the Security Groups option under Security.
2. On the page that displays, click the Create Security Group button. A window will display.
3. Fill in the fields as follows:
   • Security group name: Enter a unique name for the Security Group.
   • Description: Enter a brief description of what the group is.
   • VPC: Verify that the VPC containing the database and SSH server is selected in the drop-down.
4. In the Security group rules section, click the Inbound tab.
5. Click the Add Rule button.
6. Fill in the fields as follows:
   • Type: Select Custom TCP Rule.
   • Protocol: This should default to TCP.
   • Port Range: Enter the port used by the database you’re connecting to Stitch. For example: For a PostgreSQL database, the port might be 5432.
   • Source: Paste the SSH server’s Private IP and add /32 at the end. In CIDR notation, /32 indicates a specific IP address. In this case, you’re creating a rule specifically for the SSH server’s private IP address. For example: 172.31.10.171/32

7. Review the settings. Here’s what our Security Group looks like:

   

8. Click Create to create the Security Group.

Step 4.3: Associate the Security Group with the database

In this step, you’ll associate the Security Group from the previous step with the database. This will allow the traffic from the SSH server to access the database.

Use the instructions below for the type of database you’re connecting to associate the Security Group with the database.

---

Step 5: Retrieve your Public Key

The Stitch public key is used to authorize the Stitch SSH user. This key is specific to your Stitch account.

Note: If the key isn’t properly installed, Stitch will be unable to access the server.

To retrieve your public key, use the instructions below:

---

Step 6: Create the Stitch SSH user

Next, you’ll create a Stitch user on the SSH server. This will allow Stitch to authenticate via a public key and connect to the server.

1. Open a new terminal window and log into your SSH server.

2. Create a group named stitch:

   sudo groupadd stitch
   

3. Create a user named stitch:

   sudo useradd -m -g stitch stitch
   

4. Switch to the stitch user:

   sudo su - stitch
   

5. Create the .ssh directory:

   mkdir ~/.ssh
   

6. Using chmod, alter the permissions of the directory to allow access:

   chmod 700 ~/.ssh
   

7. Navigate to the .ssh directory:

   cd ~/.ssh
   

8. Using touch, create the authorized_keys file:

   touch authorized_keys
   

9. Set the permissions for authorized_keys:

   chmod 600 authorized_keys
   

10. Import the Stitch public key into authorized_keys, replacing <PUBLIC KEY> with the public key. Verify after replacing the key that it is on one line and doesn’t contain line breaks:

    echo "<PUBLIC KEY>" >> ~/.ssh/authorized_keys
    

---

Step 7: Complete the setup for Stitch

The last step is to complete the setup steps required to connect the database to Stitch. The instructions vary from database to database, and whether the database is an integration (where data is extracted) or a destination (where data is loaded).

Note: Only Amazon-based databases are listed as this guide is only applicable to the databases listed below. Refer to the Setting up an SSH Tunnel for a database connection guide for instructions for non-Amazon databases.

Related	Troubleshooting

---

Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.