Integrate.io
  • Integrate.io
  • Platform
    ETL
    ETL & Reverse ETL
    CDC
    ELT & CDC
    API
    API Generation
    API
    Data Observability
    API
    DWH Insights
  • Pricing
  • Connectors
  • More
    Blog
    Compare Us
    SUPPORT
    Support & Resources
    Security
    COMPANY
    Customers
    White Papers
    Webinars
    Documentation
    About
    Partners
    Join Us
  • Sign In
  • GET A DEMO
  • (888) 884 6405
  • Sign In
  • GET A DEMO
Press Enter to see full results

DWH Insights
Knowledge Base

DWH Insights Knowledge base

Documentation

11 Articles
  • Connecting to intermix.io - Creating a Redshift Account for Intermix
  • Connecting to intermix.io - Whitelisting IP Addresses for Redshift in AWS
  • Connecting with SSH Tunnel Server
  • Product Concepts - Cohorts
  • Product Concepts - SQL groups
  • Product concepts - Jobs
  • Query Annotations - Generate Query Annotations
  • Query Annotations - Query Annotation Specification
  • Query Annotations - Supported Apps
  • Query Annotations - intermix.io - Apache Airflow Plugin
  • Query Annotations - intermix.io - Python Plugin

Connecting with SSH Tunnel Server

Introduction

For the strongest encryption between intermix.io and your database, you can create a SSH tunnel to a tunnel server.

 

Related Articles

Whitelisting IP addresses for Amazon Redshift in AWS 

Creating an Amazon Redshift User Account for Intermix

When to Use the SSH Tunnel

Use the SSH Tunnel if your data warehouse is not accessible from the public internet. For example if:

1. for security reasons, you are unable to add an inbound firewall rule to your data warehouse, or

2. your data warehouse IP address is on an internal network (no outside network access).

 

Step 1: Choose a Host on which to terminate the Tunnel

Using a Tunnel Server

Terminating the tunnel on a separate server has the advantage of keeping your database server inaccessible from the Internet. If the tunnel server is compromised it is one step removed from the database server. We recommend that you remove all non-essential software and users from the tunnel server and closely monitor it with tools such as an IDS.

The tunnel server can be any Unix/Linux host that:

  1. Can be accessed from the Internet via SSH
  2. Can access the database

 

Step 2: Create 'intermix' user on Amazon Redshift

Following the steps in this knowledge base article to create an appropriate user in your Amazon Redshift database. You will need this when configuring your database in the intermix.io product.

Creating a Redshift Account for Intermix

 

Step 3: Create IP Whitelist

The second step is to allow network traffic to reach the tunnel server or database host via SSH, which is generally on TCP port 22.

Please allow network traffic from the IP address where intermix.io is hosted:

52.40.120.246

 

Step 4: SSH Tunneling

Provide the following information to your intermix.io representative:

  • IP address / DNS name of the database server (as seen from the tunnel server)
  • Database port number (as seen from the tunnel server)
  • IP address or DNS name of the tunnel server (as seen from the public internet)
  • SSH port of the tunnel server as seen from the public internet
  • Username on the tunnel server for the SSH connection

 

Step 5: Preparing the Tunnel Host

Your intermix.io representative will provide you with a unique public key, which will be used to authenticate the SSH tunnel session (we do not support logins via password). You will need to prepare your host (either the database server or tunnel server) by creating an intermix user and adding the intermix.io public key to the intermix .ssh/authorized_keys file. Here’s how:

  1. Create group intermix.

    sudo groupadd intermix
    
  2. Create user intermix and its home directory:

    sudo useradd -m  -g intermix intermix
    
  3. Switch to the intermix user:

    sudo su - intermix
    
  4. Create the .ssh directory:

    mkdir ~/.ssh
    
  5. Set permissions:

    chmod 700 ~/.ssh
    
  6. Change to the .ssh directory:

    cd ~/.ssh
    
  7. Create the authorized_keys file

    touch authorized_keys
    
  8. Set permissions:

    chmod 600 authorized_keys
    

Using your favorite text editor, add the SSH key provided by your intermix.io representative to the authorized_keys file. The key must be all on one line. In some cases, when you retrieve the key from your email, line breaks will be inserted by your email client. If you do not remove them it will be impossible to establish the SSH tunnel.

 

Tunnel Security Notes

By default, opening SSH access also allows forwarding of any ports, circumventing any firewalls between intermix.io and the database host that is terminating the SSH tunnel. For better security, limit port forwarding and the ability to log in to your tunnel server. This can be controlled by properly configuring the .ssh/authorized_keys entry for the intermix.io public key.

For example, the following text could be prepended to the intermix.io SSH key in your authorized_keys file. Please note that this text MUST be customized for your environment.

no-pty,no-X11-forwarding,permitopen="localhost:3306",permitopen="localhost:3307",
command="/bin/echo Login Not Permitted"

See the man ssh and man authorized_keys pages for examples and full details.

 

Next Steps

Notify your intermix.io representative when the tunnel server is all set up and we'll confirm that we are able to connect.

After that, simply log back into intermix.io and set up your cluster. In your "Cluster Credentials" section of the sign-up flow, enter the IP / DNS name of the Amazon Redshift server (as seen by the tunnel server) into the Host field.

Free 7-day trial. Easy setup. Cancel any time

See Also

Integrate.io

hello@integrate.io
+1-888-884-6405

Ensure Data Quality Ensure Data Quality Ensure Data Quality Ensure Data Quality Ensure Data Quality

©2023 Integrate.io

SIGN UP FOR “THE STACK” - OUR MONTHLY NEWSLETTER
Solutions
  • Solutions Home
  • Connectors
  • Marketing
  • Sales
  • Support
  • Developers
Support
  • Blog
  • Live Chat
  • Support & Resources
  • Developers
  • Documentation
  • Documentation API
  • Security
  • Service Status
  • Privacy Policy
  • Terms of Service
  • Glossary
Company
  • Customers
  • White Papers
  • Webinars
  • About
  • Partners
  • Join Us
Language
  • English
  • 日本語
  • 한국어

©2023 Integrate.io

Get the Integrate.io Newsletter

Don’t worry, we hate spam as much as you do.
I'm already subscribed. No thanks.

Choose Your Free Trial

Ensure Data Quality
ETL & Reverse ETL
Formerly Xplenty

Low-code ETL with 220+ data transformations to prepare your data for insights and reporting.

Ensure Data Quality
ELT & CDC
Formerly FlyData

Replicate data to your warehouses giving you real-time access to all of your critical data.

Ensure Data Quality
API Generation
Formerly DreamFactory

Generate a REST API on any data source in seconds to power data products.