Measurable key results are critical aspect of any inspirational objective.
Quantive supports fully automatic Key Results and KPIs by gathering data from external datasources. Some are SaaS based solutions that provide a unified way of authorizing Quantive on your behalf, involving OAuth 2.0 authorization flow and HTTPS TLS > 1.2 encryption for the data during transmission.
On premise solutions, database servers and warehouses however, require additional security considerations to achieve the same democratization of the stored data. Those solutions are behind firewalls and use framework specific protocols for data access (like JDBC, ODBC, etc) where the encryption in transit is often an afterthought. This requires IT administrators and security personal to enforce a secure channel for transmitting the data on a lower transmission layer than the concrete application protocol. This is where network tunneling comes into play and the following article describes the details you need to share with your IT administrator in order to establish access for databases behind a firewall using an encrypted tunnel.
Secure database connections
The approach for secure encryption tunnel that Quantive supports facilitates SSH Remote Forwarding where a local datasource networking port is forwarded to Quantive infrastructure designated to your account. The traffic between those endpoints is completely encrypted using the current best known encryption algorithms. For linux based systems this is achieved by using autossh client. There are persistent SSH clients available for Windows as well.
NOTE: This setup is available for Quantive Results customers on an active subscription for the Enterprise edition. For more information see the Enhanced Security section under Quantive Results platform features on our Pricing | Quantive Results Strategy Execution Software | Quantive page.
Setting up secure tunneling connection to Quantive servers
1. First you need to identify the server that is going to make its datasource port available on the Quantive remote machine and provide Quantive the IP address to whitelist it in our firewall. In return Quantive will provide the IP range that Quantive will use as allowed destination for the SSH connection.
2. You need to choose secure key for establishing the tunnel connection. There are a lot of considerations to be made so check our "SSH key generation suggestions" in the bottom of this article. Then contact Quantive for a secure link that you can use to give us your public key.
3. Install autossh on the server that is going to port forward the database connection
Debian / Ubuntu
CentOS / Fedora / RHEL
Contact Quantive for a suggestion
4. Connectivity check with Quantive's infrastructure
telnet [account][.us].gtmhub.com [remote port]
If you have successfully completed step 1. you will have the required information to configure your firewall and fill in the command
[remote port] and successfully establish a telnet connection.
5. SSH connection test
ssh [account]@[account].gtmhub.com -i ~/.ssh/gtmhub_id_ed25519
The idea is to verify that an SSH connection can be established with Quantive's server. However this connection does not share your datasource port and is not a reliable way to do so as any interruption will break the connection and you have to reestablish it again. This is where the full autossh command comes into play:
6. Start autossh
autossh -M 0 -f -N -R [remote port]:127.0.0.1:[local database port] [account]@[account][.us].gtmhub.com -g -i ~/.ssh/gtmhub_id_ed25519 -o ServerAliveInterval=10 -o ServerAliveCountMax=3 -o ExitOnForwardFailure=yes
7. Create a read only user for the desired datasource and complete the datasource connection at https://[account domain].gtmhub.com/#/data-sources/add-data-source/connectors/
SSH key generation suggestions
Choose your SSH client
Based on the operating system you need to chose which SSH client to use. Linux based systems mostly use OpenSSH that uses OpenSSL for the cryptography algorithms. Therefore the version of your OpenSSL library is a critical security aspect of the desired tunnel connection.
Check your version
That should list the version of your SSH client and the cryptography framework currently installed and used.
We suggest you get the latest stable version for both components but always > OpenSSH 7.
Using existing SSH key
Check your local server for existing ssh keys that you may want to reuse.
ls -al ~/.ssh
If a key that you would use exists, skip ahead to the Retrieve your public key step. We suggest you generate a separate key for connecting to Quantive.
Creating SSH key
Run the following command to generate an SSH key. There are different key algorithms. Our research suggests that ED25519 to be the best option. Furthermore RSA with key size smaller than 2048 is not considered secure. Bigger keys on the other hand have a performance penalty.
ssh-keygen -t ed25519 -C "Your comment about this SSH key target use goes here" -f ~/gtmhub_id_ed25519
Next, you’ll be prompted to enter a passphrase. Do not enter a passphrase for now. Press
Enter twice to continue. Password protecting your key for autossh requires additional considerations explained in another section of this article.
Retrieve the public key
Navigate to your SSH folder, or run the following command to find your public key:
ls -la *.pub
Retrieve the contents of your public key file with the following command. Edit the file location as needed.
Keep your public key handy - you’ll need to provide it to Quantive.
SSH file permissions
Permissions should be set up correctly by default, but you can use the following commands to correct permissions if needed:
chown -R [username] \~/.ssh
chmod 700 \~/.ssh
chmod 600 \~/.ssh/gtmhub_id_ed25519
This ensures that only the specified [username] has access to the private key and is required for most ssh clients to accept the key and establish the connection.
VPN vs SSH
VPNs are useful to bridge two different private networks. They require additional configuration of network routing rules and matching firewall rules.
SSH tunnels, on the other hand, map a single IP and port combination in one private network into another. Very little network routing setup is required and firewall rules can only add security to the solution.
Conversely, a poorly configured VPN could give Quantive access to every server in the customer private network and vice versa. This is a big security concern for both parties involved. Furthermore, all cloud providers place limitations on the number of VPNs or peer-ed VPN networks. Therefore, it would be difficult to scale VPNs as easily as we can SSH connections.
There are number of VPN connection protocols available which makes it very difficult for Quantive to implement one-size-fits-all, self service functionality - achieving the desired security requirements. In addition, SSH is as secure from an encrypted tunnel perspective as any industry standard VPN protocol.