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 your bastion server to our tunnel server. This way, all connections to your DB will look as if it originates from your bastion server, and all data transfer will securely go through the tunnel.
Holistics provides 2 methods to set up reverse SSH tunnels:
- Automated tunnel script generation
- Manual step-by-step
Unless you have special requirements or configurations, we strongly recommend you follow the automated tunnel script generation right below.
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.
NOTE: 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.
- Enter the Display Name of the data source and choose your Database Type.
- Choose Use reverse tunnel
- Fill in the
Portwhere your database can be accessed from your proxy server. NOTE: If your database in on an AWS machine, specifying
localhostmay not work, so use the machine's local IP instead (e.g. '172.31.2.1')
Generate Scriptand wait for the script to be generated
- 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.
- Read the script introduction and follow the script execution
- Return to your browser when this message appears:
- The database configurations will now appear on your form. Fill in and click
- If the test is successful, you can finish by clicking Save Data Source
- 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.
C. 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
D. 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.
E. Script Operations (Automated)
The script takes the following actions (in order):
Create Holistics Directory Creates a directory to store all files related to the whole process. Full path:
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.
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.
Start the reverse tunnel The script creates a reverse tunnel SSH connection from your proxy server to our tunnel server. The port on the tunnel server will be the one assigned by Holistics, and the port on your server is the one you configured in the data source form. Afterward, the script will notify Holistics web server about the running tunnel.
h_autosshservice At the point 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.
When there is an error with the Data Source that you connected using the automated script, please perform these checks:
sudo service h_autossh statuson the proxy server to see whether the reverse tunnel service is running
Check if your proxy server can connect to the database successfully using:
telnet <database_host_ip> <database_port>
- A database CLI. For example, using
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.
G. Other Notes
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 :
- 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]'
- (Optional) Remove
-fin the command to run the command in foreground, so that you can use
Ctrl-Cto stop the tunnel
- 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]'
- Refresh Holistics page to see if the connection is working.
- 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.
B. 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:
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:
Send Us Your Public Key
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 installed 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.
2. Set Up Tunnel
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:
Now that you're inside a screen, run:
Before you run, change the second line to reflect the connection string to your database server:
Example of a command:
In the above example:
- Your DB host is:
- The tunnel port you choose is:
[tunnel_port]is the port we will use when connecting to
tunnel.holistics.io. We have opened up the range
20000:20100for tunneling purposes, so if you create a second tunnel, please pick a port in this range.
- We have restricted traffic for
20000:20100to only the Holistics web server, so connecting to these ports from other computers will not be allowed.
About GNU screen:
- To exit the screen:
- 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:
Select Direct connection because we are not using the automation script
You should be good to go.
C. Setup in Windows environment
- Download Putty via https://www.putty.org/
- Open PuttyGen
- Generate new key pairs
- Enter passphrases and save private key
- Copy the public key and save it in a text file
- Send the public key to Holistics Support at [email protected], and then receive the port range
- Download Bitvise SSH Client (Tunnelier) via https://www.bitvise.com/download-area
- Open Tunnelier
- Fill in the ‘Server’ section as in the next screenshot 10. Click ‘Client key manager’
- Click ‘Client key manager’
- Import the private key (.ppk) file which was saved in step 4
- Fill in the Authentication section as above
- In the tab Options, disable everything in On Login section
- Go to S2C tab, add a new rule:
- Listen Interface: 127.0.0.1
- List. Port: one of the ports received in step 6. Later you should input this port in Holistics Data Source form (step 17)
- Destination Host: host of SQLServer
- Dest. Port: listening port of SQLServer (default is 1433)
- Save Profile
- 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