Tunnel Connection

Introduction

If your database is behind a firewall/closed network you don't want to modify any firewall rules, you can consider setting up a reverse SSH tunnel.

This will open a dedicated secure connection from within your private network to our Holistics network. Holistics BI will connect to the your database via this active secure connection (tunnel).

In term of security:

• No Exposing of Private Network: This method doesn't require customers to expose any database/server to the public internet (e.g either via IP whitelisting or changing firewall rules). Not even the server that runs the tunnel script.
• Data Encryption in Transit: Data transferring between the connection is encrypted

Setting Up

Holistics provides 2 methods to set up reverse SSH tunnels:

• Automated Script
• Manual Setup

Unless you have special requirements, we strongly recommend you follow the automated tunnel script generation right below.

Tunnel Servers

Holistics provides tunnel service from these tunnel servers according to your region:

• For APAC: tunnel.holistics.io / apac-tunnel.holistics.io
• For EU: eu-tunnel.holistics.io
• For US: us-tunnel.holistics.io

When dealing with the tunnel servers manually, please make sure you are connecting to the correct tunnel server of your region.

Set up Reverse SSH Tunnels Automatically

With our automated bash script, connecting Holistics to your data source using a reverse tunnel is much faster and more convenient.

Common got-chas

• The script only fully supports Debian and RHEL based distros, and partially supports MacOS (see this section for notes on MacOS). For other systems, please see this doc on setting up the tunnel manually.
• The tunnel service only runs as one user per machine. Thus, within a single bastion server, please use the same user to setup all tunnel connections.

Step-by-step

1. Enter the Display Name of the data source and choose your Database Type.
2. Choose Use reverse tunnel
3. Fill in the Host and Port where your database can be accessed from your proxy server. NOTE: If your database in on an AWS machine, specifying localhost may not work, so use the machine's local IP instead (e.g. '172.31.2.1')

4. Click Generate Script and wait for the script to be generated

5. Copy the command and execute it on your proxy/bastion server. Note that you should not prepend the command with sudo, because some packages will be installed for current user only. You will be asked for superuser privileges to install packages and set up system services later. See this section to know all the actions that the script carries out.

6. Read the script introduction and follow the script execution
7. Return to your browser when this message appears:

8. The database configurations will now appear on your form. Fill in and click Test Connection

9. If the test is successful, you can finish by clicking Save Data Source

10. After the data source is saved, the reverse tunnel will be running in a system service called h_autossh. Note that the display of the status varies between systems.

The h_autossh service

As described in Script Operation / Final steps below, the reverse tunnel script creates a service for you to manage all tunnel connections to Holistics, and runs them automatically on startup.

How to use the service:

• to start all Holistics tunnels, run sudo service h_autossh start
• to stop all Holistics tunnels, run sudo service h_autossh stop
• to restart all Holistics tunnels, run sudo service h_autossh restart
• to see the status of Holistics tunnels service, run sudo service h_autossh status

Removing Reverse Tunnel

For data sources using reverse tunnel connection, when you click Delete, we will generate another script for you to execute on the proxy server.

This script removes the reverse tunnel (associated with the chosen data source) from h_autossh and restarts the service. If the reverse tunnel being removed is the last tunnel in h_autossh (i.e. you do not have any other reverse tunnel), the h_autossh service will also be removed. Finally, the data source will be deleted.

Updating Host/Port of Reverse Tunnel

Currently, it is not possible to directly update the Host/Port of a Reverse Tunnel Data Source.

Instead, please follow this workaround:

1. Create a new Reverse Tunnel Data Source with your new/updated configs
2. Send an email to [email protected] to request swapping the 2 Data Sources
   • In your email, please make sure to:
     • Include the name/ID of the old Data Source and new Data Source
     • Send using a valid email of a Holistics Admin in your workspace

Note

You can still update other configs such as Database name, Username, Password via Holistics UI.

Troubleshooting

caution

Note that these are Linux commands. If you are using a different OS, please substitute these with your OS's native equivalent ones.

When there is an error with the Data Source that you connected using the automated script, please perform these checks:

1. Check on the proxy server whether it can connect to Holistics tunnel server at port 50022

   • With netcat: nc -w 10 tunnel.holistics.io 50022 && echo 'success' || echo 'fail'
   • Or with telnet: telnet tunnel.holistics.io 50022
   
   
   Possible responses

   ✔️ Example of a success response

   • With netcat approach, it should output success to your console.

     

   • With telnet approach, a successful response should look like this.

     

   ❌ Example of a failure response

   • With netcat approach, it should output failure to your console.

   • With telnet approach, a failure response would contain one of these following messages:

     telnet: Unable to connect to remote host: Connection timed out
     telnet: could not resolve tunnel.holistics.io: Name or service not known
     telnet: Unable to connect to remote host: Connection refused

2. Run sudo service h_autossh status on the proxy server to see whether the reverse tunnel service is running.

   Possible responses

   ✔️ Example of a success response

   • A success response tells you that the h_autossh service is running.

   

   ❌ Example of a failure response

   • A failure response tells you that it cannot find h_autossh service.

   

3. Check if the tunnel process is actually running using sudo ps aux | grep tunnel.holistics.io. Alternatively, use sudo ps aux | grep tunnel.holistics.io | grep -v grep to exclude grep from the search result.

   Possible responses

   ✔️ Example of a success response

   • A success response will list out running tunnel processes.

   ❌ Example of a failure response

   • A failure response will return 0 result (or 1 result if you don't exclude the grep result).

4. Check if your proxy server can connect to the database successfully using:

   • telnet <database_host_ip> <database_port>
   • A database CLI. For example, using mysql: mysql -h <database_host_ip> -P <database_port> -u <db_username> -p.
   
   
   Possible responses

   ✔️ Example of a success response

   • If you are able to log in to psql shell (Postgres), the success response would look like this.
   • If you are using Telnet, its success response should look like this.

   ❌ Example of a failure response

   • A failure response from psql should look like this.
   • A failure response from Telnet should look like this.

5. Check if you are using snap-installed autossh. Holistics users have experienced some issues with snap-installed autossh. Thus, we would recommend against using snap-installed autossh.

   1. Check where is autossh installed:
      • For example, you can use which autossh or command -v autossh
        • If the result is something like /snap/bin/autossh then it's installed via snap
   2. Uninstall autossh in snap: sudo snap remove autossh
   3. Recreate a new Data Source. Holistics script will automatically re-install the appropriate version of autossh

If all the above checks return successful results, please send the output of the above commands to [email protected] and we will help you with further troubleshooting.

Other Notes

How the automated script works

For your reference, the script takes the following actions (in order):

Details

1. Create Holistics Directory

Creates a directory to store all files related to the whole process.
Full path: $HOME/.holistics

2. Install Packages

The script will try to install two main packages:

• curl: Used to communicate with Holistics web server. nss is also updated if possible so that curl can handle SSL connection properly.
• autossh: Used to handle the tunnel connection and keep the connection persistent. For distros using yum package manager, autossh is not always available (such as in RHEL 6). In this case, gcc and make will also be installed to compile autossh from its source. If you want to install autossh by yourself instead, please install it prior to the script execution, and the script will not try to install it again.

3. Generate RSA key pair and submit public key to Holistics

A new key pair will be generated in Holistics directory using ssh-keygen. If the key pair exists, for example when you have already set up another tunnel, the existing key pair will be used. Then, the public key is submitted to Holistics web server. Holistics will add your public key to the tunnel server so that SSH connection can be established, and also assign a specific port on the tunnel server for your new connection.

4. Start the reverse tunnel

The script creates a reverse tunnel SSH connection from your proxy server to our tunnel server. The remote port will be the one assigned by Holistics, and the local port is the one you configured in the data source form. Afterward, the script will notify Holistics web server about the running tunnel.

5. Create/Update h_autossh service

At the point when the script tells you to return to the browser, it starts waiting for your data source result. If the data source is saved successfully, the script will stop the reverse tunnel that was made previously. Then, it creates a new service called h_autossh, which is stored in /etc/init.d/ and contains the command to run the reverse tunnel. If the service exists, the script will update it to include the new tunnel configuration. The service is also enabled to run on startup.

Running automated script on MacOS

The script should work fine up until the last step of setting up automated service h_autossh (see this section for info about h_autossh), because it has not supported MacOS services. Thus, if you are using MacOS, after your data source has been created successfully on Holistics and the script has finished, you will need to run and manage the tunnel manually :

Steps

1. In the terminal output of our automated script, you should see an ssh command right after Running temporary tunnel. It should be something like: sudo su <user> -c 'ssh -f -M -S <socket_path> -NT -o ExitOnForwardFailure=yes -o ServerAliveInterval=60 -o ServerAliveCountMax=3 -p 50022 -i /home/<user>/.holistics/id_rsa -R <remote_port>:<client_host>:<client_port> [email protected]'
2. (Optional) Remove -f in the command to run the command in foreground, so that you can use Ctrl-C to stop the tunnel
3. Run the modified command, e.g. sudo su <user> -c 'ssh -M -NT -o ExitOnForwardFailure=yes -o ServerAliveInterval=60 -o ServerAliveCountMax=3 -p 50022 -i /home/<user>/.holistics/id_rsa -R <remote_port>:<client_host>:<client_port> [email protected]'
4. Refresh Holistics page to see if the connection is working.
5. Keep the command at step 3 running to keep the tunnel connection. You will have to manage and maintain the process manually. For example, you will need to run the command again after restarting your machine.

Set up Reverse SSH Tunnels Manually

In case your system does not use Debian or RHEL based distros or use Windows, please follow these steps to set up reverse SSH tunnels manually.

Set up in Linux environment

We will use autossh to create the reverse SSH tunnel. autossh manages the SSH connections automatically and restarts it when it dies/drops off. In other words, we will have a more persistent tunnel with it.

1. Generate SSH Key Pair

Generate a new SSH key pair with:

ssh-keygen -t rsa -b 4096

When asked for a passphrase, press 'Enter'. A pair of files will be created as per the path you entered.

Already Have A Key Pair

If you already have a key pair, please ensure your private key is only readable by your user account:

chmod 400 <path_to_your_private_key_file>

2. Send Us Your Public Key (and receive your authorized tunnel ports)

After that, send us the public key (file ~/.ssh/id_rsa.pub) at [email protected] so we can add it to our tunnel server. In the mail, please also include the reason why you would like to set up the tunnel manually instead of using our automated script.

We will confirm with you after we have authorized your public key and send you the available secured ports to connect to. Make sure you receive the confirmation from us before you continue with the steps below, otherwise the commands will fail.

3. Set Up Tunnel

Install autossh:

sudo apt-get update
sudo apt-get install autossh

We will run autossh in a screen. You can also run it as a background daemon, but that is more difficult to manage. Install screen package with sudo apt-get install screen, then run:

screen -S holistics

Now that you're inside a screen, run:

autossh -M 0 -N -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" \
-R *:[tunnel_port]:[yourdb.server.com]:[db_port] \
[email protected] -p 50022 \
-i <path_to_your_private_key_file>

Before you run, change the second line to reflect the connection string to your database server:

-R *:[tunnel_port]:[aws.ip.to.redshift.com]:[redshift_port]

Example of a command:

autossh -M 0 -N -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" \
  -R *:20032:db.somehost.com:5432 \
  [email protected] -p 50022 \
  -i ~/.ssh/id_rsa

In the above example:

• Your DB host is: db.somehost.com, port 5432 (PostgreSQL)
• The tunnel port you choose is: 20032 (this must be one of the authorized ports you received in step 2)

About GNU screen:

• To exit the screen: Ctrl-a d
• To enter the screen again: screen -x holistics

3. Add New Data Source

Now open Holistics to add a data source using the credentials you have. Enter the following host & port:

• Host: tunnel.holistics.io
• Port: [tunnel_port]

Select Direct connection because we are not using the automation script

You should be good to go.

Setup in Windows environment

1. Download Bitvise SSH Client (Tunnelier) via https://www.bitvise.com/download-area
2. Open Bitvise SSH Client
3. Click ‘Client key manager’

4. Click 'Generate New'
5. Enter passphrases and generate new keypair
6. Export public key in OpenSSH format

7. Send the public key to Holistics Support at [email protected], and then receive the port range
8. Fill in the Server and Authentication sections as in the above screenshot
   • For Server, please make sure you are inputing the correct Tunnel Server according to your region
9. In the tab Options, disable everything in On Login section
10. Go to S2C tab, add a new rule:
    • Listen Interface: 127.0.0.1
    • List. Port: one of the ports received in step 7. Later you should input this port in Holistics Data Source form (step 13)
    • Destination Host: host of SQLServer
    • Dest. Port: listening port of SQLServer (default is 1433)
11. Save Profile
12. Login
13. Add new Data Source using the tunnel connection you have just setup

Set up SQL Server Security for User login

Login to your server in SQL Server Management Studio Right-click on server -> Properties -> Security

In Server Authentication, choose SQL Server and Windows Authentication Mode -> OK

FAQs

How about Forward SSH tunnels?

Reverse tunnels already cover the most important feature of Forward tunnels: securely connect to a (typically) firewall-protected database.

Besides, Reverse tunnel offers some advantages:

• Neither your bastion machine nor database needs to be accessible on the internet, i.e. it does not need a public IP.
  • Example perk: With Reverse tunnel, it's feasible to connect to databases via your localhost. This is not the case with Forward tunnel.
• While maintaining the Reverse tunnel connection means more work, it also provides you more control. For example, you can freely combine multiple tunnels into one process, terminate/restart the connection when needed, etc.

However, if your use case really needs to use Forward tunnels, please send us a feature request at [email protected].

I cannot connect to tunnel.holistics.io:[my_tunnel_port] directly to test my connection

The tunnel ports are restricted to be directly accessible from Holistics servers only, hence you cannot connect to tunnel.holistics.io:[my_tunnel_port] directly from your side.

Instead, you can

• Test the full connection using Holistics UI
• Check the ssh tunnel process
• Check the connection from your bastion server to your database

You can also reference the Automated Tunnel's Troubleshooting guide, but please ignore number 2 which checks the h_autossh service.