Use SSH tunnel when connecting to databases

Overview

Databases may be configured to now allow direct access from remote servers for security reasons. Using SSH Tunnel is a good security practice to prevent unwanted third parties from interfering with the connection and damaging the database.

When you connect to a remote database instance, an SSH tunnel will link a port on Etlworks cloud instance to a port on a remote host and encrypt that Connection. Any data transported between Etlworks cloud instance and the remote server will go through the SSH first, be encrypted, and then pass securely to the other end.

Keys for public-key authentication

The use of public-key authentication isn’t required, but using it is highly advised.

SSH public-key authentication relies on asymmetric cryptographic algorithms that generate a pair of separate keys (a key pair), one private and the other public. When it comes to generating keys, you have the following options: 

1. You can generate a pair of keys yourself and use the private key to configure the SSH tunnel. 
2. You can ask Etlworks to generate a pair of keys for you.

Configure SSH bastion

This diagram illustrates the concept of using an SSH bastion host to provide access to on-premise databases running inside the corporate network behind the firewall. 

Step 1. Ensure you have public key authentication properly configured, both on the bastion host as well as the remote instances. You can use the same key for both the bastion host and the remote instances or different keys. 

Step 2. Ensure that name resolution is working — both from the Etlworks instance to the bastion as well as from the bastion to the remote instances. The bastion host is going to use the hostname specified on the ssh command line, so if it can’t resolve the name, the Connection will fail. In most cases, you want to configure the SSH bastion to accept inbound Connections only on the SSH port (typically 22) and only for Etlworks instance public IP address.

Configure database Connection with SSH tunnel

If you need to access a database that can only be accessed via an SSH tunnel, you need to specify additional information in the Connect over SSH tunnel section of the database Connection screen.

• SSH Host: the name or IP address for the host accepting SSH Connections.
• SSH Port: the port accepting SSH Connections. The default value is 22.
• SSH User: the user name.
• SSH Password: the optional password.
• Private Key File: the private key file in the pem or ppk Format used for SSH authentication. Click the Upload/Select ssh key to manage SSH keys using GUI.
• You can also upload the private key file manually and use the token {app.data} as a part of the filename, for example, {app.data}/keys/secret.pem. 

• SSH Passphrase: the optional passphrase (password) used together with a private key file.

Use actual database hostname and port when configuring the URL for the database Connection. Etlworks will automatically replace them with the localhost and the available port.