Using SSH tunnel when connecting to databases

Why use SSH tunnel

For security reasons, databases may be configured to now allow direct access from remote servers. Using SSH Tunnel is a good security practice to prevent some bad hands from interfering with the connection and doing harm to your 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, then passed to the other end securely.

Keys for public-key authentication

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". 

The use of public-key authentication isn’t strictly required, but it’s something you really should do.

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.

Configuring 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. 

First, you’ll want to 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. 

Next, you’ll want to 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 the Etlworks instance public IP address.

Configuring 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" button 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 Integrator will automatically replace them with the localhost and the available port.