SSH Tunnel Setup for Hosted Database Systems
When your IT or Security team does not want to whitelist/grantlist/safelist the DLH.io IP addresses directly to your database, a secure alternative for databases that are not publicly accessible is a Secure Socket Shell (SSH) connection which DLH.io provides as an available connectivity option.
While we contend that an SSH connection is a security mechanism with great value, we believe safelisting/grantlisting our IP addresses is just as secure in most situations. However, where SSH is fundamentally desired for your organization's requirements, we recognize that your Customer hosted/enabled SSH server acts as the intermediary between the network/Internet and your data source, and as such our enhanced DLH.io security protocols are in place to support this connectivity.
Please direct any questions or requests for assistanc eto our support team.
You can see from this diagram what the architecture flow looks like when working with SSH tunnels:
The SSH setup and configuration applies to all hosted Database connectors in DLH.io unless otherwise listed below in this document. Currently DLH.io enables the Customer to incorporate their own Private Key and Public Key pair for configuring the database SSH server as part of their setup process for creating a Source connector in DLH.io. This means that a Customer can use an existing SSH user and private/public key pair. The following would be the steps a Customer would follow if they were creating an SSH Server from scratch as such once established as a pre-requisite the following information will needed during the database source connector configuration:
- Public IP Address and Port of the Customer SSH Server
- Verified User name and Private Key to connect to the Customer SSH Server
- We suggest that you create a new SSH user (ex: dlh_ssh_user and an new private and public key pair for this user
- Database IP/DNS Address and Port that the SSH Server will route to once connected
- Test from your local IDE on a command line or via IDE such as DBeaver, etc. that you can connect to your database via an SSH Tunnel without issues. This proves there is no issue with your SSH Server, your credentials (i.e.: keys), or your database receiving requests from your SSH server.
This section discusses the steps in general if your Customer hosted SSH Server is already running. If you do not yet have an SSH server or need specific SSH or Private/Public Key understanding for your SSH Server hosted on a major cloud platform such as GCP, AWS, Azure, etc. please contact DLH.io support.
Follow these steps to begin quickly integrating database as a source via your SSH Server:
- Prepare the Customer SSH Server and have ready the SSH information per the pre-requisites section
- Whitelist/Grantlist the DLH.io IP Addresses on the Customer SSH Server as described in Our IP Grantlist / Whitelist guide, based on your region, if applicable
- Create a new DLH.io SSH User on your SSH server specifically for any source connectors that will be connected on your hosted network and connected to via SSH server. We suggest creating a group (create group first), called dlh_ssh_users, and a new user called dlh_ssh_user. So in a Linux terminal, on the SSH server,
- Create a linux group named dlh_ssh_user:
- sudo groupadd dlh_ssh_users
- View groups by using a command like cat /etc/group to see if your new group is added, and to view existing groups, where the newest group is usually shown toward the bottom of the list.
- Create a new linux user named dlh_ss_user:
- sudo useradd -m -g dlh_ssh_users dlh_ssh_user
- View the list of users and confirm the dlh_ssh_user is in the list by running the command, cat /etc/passwd. Newest user additions are shown toward the bottom of the list.
- Create the Private Key
- Create an empty file, in your working folder, so that the key when created can immediately by created into an existing and known path, touch dlh_ssh_user_key
- You may wish to first create a temporary working folder such as mkdir ~/tmp_keys, then run cd ~/tmp_keys to switch into it.
- Confirm your dlh_ssh_user_key file is in the working folder using ls
- Run the command ssh-keygento initiate the private key generation
- In order to generate an OpenSSH private key and give the public key a specific username or comment -C that can be referencable amongst other potential keys, please use the following:
- ssh-keygen -b 3072 -t rsa -o -C "<email address for key>"
- For example, ssh-keygen -b 3072 -t rsa -o -C "our DLH.io key for our database, [email protected]"
- If you want to create the file in specific location, such as direct to the file that you created using the touch command then use the -f attribute which makes the creation of the key process faster, for example, ssh-keygen -b 3072 -t rsa -o -f ./dlh_ssh_user_key -C "<email address for key>"
- If you did not use the -f attribute above for the ssh-keygen command you'll need to follow on here. For the file in which to save the key, enter the path where you saved the dlh_ssh_user_key file, ex: ./dlh_ssh_user_key or the full pwd path if required, for example, /home/devteam/tmp_keys/dlh_ssh_user_key , then click the enter/return key to continue
- If asked to overwrite the file, enter y the click the enter/return key
- Click the enter key twice to bypass entering a passphrase for your key.
- This will generate an unencrypted private key. It is safe to use a non-encrypted private key. If you need more stringent requirements please contact our support team to understand and align to your security needs. Currently ed25519 encryption is not supported.
- If needing to encrypt after testing with no encryption password on your private key, you may run ssh-keygen -p -f
- You should now have a non-encrypted private key dlh_ssh_user_key with no extension and a public key in the same path with a .pub extension, dlh_ssh_user_key.pub
- The file without the .pub extension is your private key, the one with .pub is your public key
- Please note that your private key will be in RSA (PKCS#1) format and have a header of -----BEGIN OPENSSH PRIVATE KEY----- and a footer of -----END OPENSSH PRIVATE KEY----- , because you used the -o attribute of ssh-keygen
- Print out to the terminal the private key created and copy it for your later retrieval/use
- cat dlh_ssh_user_key
- Print out to the terminal the public key created and copy it for your later retrieval/use
- cat dlh_ssh_user_key.pub
- Switch to the new dlh_ssh_user user profile
- sudo -su dlh_ssh_user
- Create an .ssh directory
- mkdir ~/.ssh
- Grant chmod access permissions on the directory
- chmod 700 ~/.ssh
- Change to the ~/.ssh directory
- cd ~/.ssh
- Create the authorized_keys file using touch, if it doesn't already exist:
- touch authorized_keys
- Grant permissions on the authorized_keys file
- chmod 600 authorized_keys
- Finally, import your Public Key, replacing <PUBLIC_KEY> syntax here with your public key output copied from the step above into the ~/.ssh/authorized_keys file for the dlh_ssh_user.
- echo "<PUBLIC KEY>" >> ~/.ssh/authorized_keys
- After you've run the command here, verify that the key is in the authorized_keys file is on a single line and does not have any line breaks which would interrupt the key and cause it not to function
- cat ~/.ssh/authorized_keys
- You'll use your Private Key in the upcoming steps when setting up your DLH.io database connector. Be sure to copy the text of the private key dlh_ssh_user_key (the one without the .pub extension) to have it available when setting up or updating the source connector configuration in DLH.io.
- Navigate to DLH.io and log in to your account
- Navigate in DLH.io to your Project
- Locate an existing database source connector or create a new one, for example select a SQL Server database source connector to be created.
- Next locate the section of the database connector labeled Use SSH Tunnel and select the Yes, Use SSH Connection option.
Use SSH Tunnel option
- Choosing this option expands the SSH configuration options, which allows you to complete all of the required SSH fields:
- SSH Server/Host : This is your SSH server where you have whitelisted/grantlisted the DLH.io IP Addresses
- SSH Port : The port for your SSH server, default is 22
- SSH User: The username of the authorized_keys folder login, i.e.: dlh_ssh_user
- SSH Private Key : The field where you will paste in the private key you created in the previous steps
- For the private key you can copy and past the full key including the header and the footer of the key, which you can read more about here, Using the full SSH Key
- Save the connection by clicking on Save & Test. The connection test will reveal if the SSH configuration was set up successfully if the connection test shows that it is successful.
- If you have an error message or some indication that the connection is not succussful please confirm that your SSH key can work in standard SFTP connection using a tool like Transmit for Mac OS or WinSCP or FileZilla, because the protocols are mainly the same. Contact support if you have persisting issues.
- Done. Once the connection is saved successfully with you SSH configuration, continue on with using DLH.io such as creating a Sync Bridge (What is a Sync Bridge (Pipeline)?), a Target/Destination ( What is a Target Connection?), etc.
N/A - There are no self-hosted database connectors at this time that do not support SSH.
