Connect to Cirium Sky Data Warehouse Using SSH Tunnel

Prerequisites

Software installation

• OpenSSH

  • For MS Windows OS, please see Get started with OpenSSH for Windows

  • For Linux base OS. e.g Ubuntu, MacOS. OpenSSH is already available by default.

Connection information

The user will need the following pieces of information prior to connection:

• The Redshift URL: cirium-sky-prod-ctq.cwdfgggbtpne.us-east-1.redshift.amazonaws.com

• Username: (Provided as part of your account set up)

• Temporary password: (Provided as part of your account set up)

• SSH Host Name: ssh-alto.sky.cirium.com

• SSH User name

• SSH Private Key File

This Private Key file is part of a SSH Key Pair. Each pair has a private and public part. To authenticate you to Cirium Sky via SSH, Cirium needs to hold the public key part of the pair whereas the user will need to store the private part. To read more about how key pair authentication works see the Using Public Keys for Authentication blog by WinSCP.

How to generate SSH key pair

Open the terminal and run

Command arguments explanation:

• -t encryption type, ed25519 is recommended

• -C Comment. Please enter your database username

• -f Output filename

You will be prompted to provide a passphrase for the key being generated. This can be left blank or you can choose to add a passphrase, N.B. This passphrase will be required when connecting to Cirium Sky if it is set.

Connection Step-by-Step - Beekeeper SQL Client

The following example is given using Beekeeper, which supports SSH tunnelling within its UI.

1. Download the latest Amazon Redshift CA Certificate Bundle from https://s3.amazonaws.com/redshift-downloads/redshift-ca-bundle.crt. This file is used to let your SQL client verify that it is communicating with a genuine Redshift instance.

2. Open Beekeeper Studio. You will be asked which database type you are connecting to - choose Amazon Redshift from the list:

3. Enter the connection information. You’ll need to fill in the following fields:

• Host - cirium-sky-prod-ctq.cwdfgggbtpne.us-east-1.redshift.amazonaws.com

• Port - 5439

• Enable SSL - ensure this is turned on. You aren’t able to connect to Cirium Sky without SSL.

• CA Cert - choose the CA certificate bundle file you downloaded in Step 1.

• User- use the username you have been provided.

• Password - use the temporary password you have been provided.

• Default Database - unless specified otherwise in your account set up information, set this to ciriumsky.

4. Provide SSH tunnel details. You’ll need to fill in the following fields::

• Enable SSH Tunnel - Ensure the toggle to enable SSH Tunnel is turned on.

• SSH Hostname - ssh-alto.sky.cirium.com

• Port - 22

• SSH Authentication - Key File

• SSH Username - use the SSH username you have been provided.

• Private Key File - Select the SSH Private Key File from file directory

• Key File PassPhrase (Optional) - Enter the passphrase, if this was set during SSH key pair generation.

5. Click Test to test your connection, and you should see a notification appear that the connection was successful. If an error is shown instead, check that all the details specified above are completed correctly, and that your IP address has been granted access to Cirium Sky.

Manual Creation of an SSH Tunnel

If your SQL client of choice does not support SSH or you are using Sky in a machine to machine capacity, you can manually create an SSH tunnel within your infrastructure to forward traffic to Sky.

For example, if you had a Linux server within your infrastructure you could run thefollowing terminal command for forwarding local traffic on port 9999 to Cirium Sky:

ssh -l <your_username> -i "<your-secret-private-key>" \
-L 9999:cirium-sky-prod-ctq.cwdfgggbtpne.us-east-1.redshift.amazonaws.com:5439 \
ssh-alto.sky.cirium.com

You could then connect to Cirium Sky using the host and port <linux_server_hostname>:9999.

If your environment uses containerisation you can also run an SSH tunnel in a container (example).