Data source connection instructions for SSH tunnels under Windows (AutoSSH with Cygwin)

If you are at all comfortable running Linux, this will all be much simpler under a Ubuntu Linux, that said, running AutoSSH via Cygwin on Windows is also supported.  This tutorial will show you how to install Cygwin and autossh, generate an SSH keypair, test the SSH connection to Chartio and once everything is confirmed working, configure AutoSSH to run as a Windows Service.

Pre-requisites

We will need a WIndows machine which has access to your database server and is available to be running 24/7.  This can run on on the same machine as your Database Server (SQL Server, PostgreSQL, MySQL, Oracle, etc) or on it's own host.  Before beginning we will need the following:

  • A Chartio account
  • A windows machine where you have administrative rights to install software
  • Network connectivity to "connect.chartio.com on port 22 (SSH) and to your database server/port.
  • Database server hostname & port number
  • Database database name & schema
  • Read-only username and password for your database

Download and install Cygwin

Download the current Cygwin Installer, either for Cygwin for 64bit Windows or Cygwin for 32bit Windows.

Run the installer and when prompted select "Install from Internet" and "Install for All Users." For our purposes here, we will assume you are installing to the default location of "C:\cygwin64" under 64bit windows.  Please adjust accordingly if you've installed elsewhere.

cygwin-install.jpg

When prompted, choose a mirror. We've found "mirrors.xmission.com" to be a reliable US based mirror, but any of the listed download sites should do.

When prompted to select packages, search for 'ssh', expand the "Net" group and click "Skip" next to AutoSSH and OpenSSH to toggle their installation. Click next twice to begin the installation.

Verify SSH and AutoSSH have been correctly installed

Open a Cygwin Terminal and run: 'ssh -V' and 'autossh -V' to confirm the installed versions of SSH and AutoSSH.

Generate a private key for SSH

Open a Cygwin Terminal and run "ssh-keygen" accepting all of the default choices by pressing enter three times.

Copy your SSH public key to the clipboard with "cat /home/IEUser/.ssh/id_rsa.pub | tee -a /dev/clipboard" (substituting your username for IEUser).

cygwin-ssh-keygen.jpg

Add a datasource to Chartio

Navigate to Chartio Connect New Datasource (Settings->Datasources->Add New Datasource) and select your datasource type.

Check the box that says "Is Tunneled" and paste your public key from the clipboard and press connect.

Test the SSH connection

Find the username from the bottom the that page, it will be in the form of tunnelXXXXX.

Test the connection by running: ssh tunnelXXXXX@connect.chartio.com.

You will be prompted to trust the public key fingerprint, reply 'yes'.

If you have been successful, you will see the Chartio Logo like above.  Now let's test the connection with SSH:

Run the following command:  ssh -N -R XXXXX:db.domain.com:YYYY tunnelXXXXX@connect.chartio.com -g -i ~/.ssh/id_rsa

Where:

  • XXXXX is your tunnel number
  • db.domain.com is the hostname of your database server (use 127.0.0.1 if you're running this locally on your database server)
  • YYYY is your database port (e.g. 5432 for PosgreSQL, 1433 for SQL Server, 3306 for MySQL, etc).

If this is successful, you will see nothing.  Go back to the Chartio Datasource view and click "Test Connection." If you have provided all the correct details above (username, password, schema, database name, etc) this will succeed and it will begin reflecting your tables.  To set this up persistently, skip to the AutoSSH section below.

Troubleshoot an SSH session:

Try connecting again, but this time, add a -v flag to your command string to show verbose output so:

ssh -v -N -R XXXXX:db.domain.com:YYYY tunnelXXXXX@connect.chartio.com -g -i ~/.ssh/id_rsa

Try and "Test Connection" again and you can see the verbose output as it tries to connect.

AutoSSH Setup

Test AutoSSH, from a Cygwin prompt run:

autossh -M 0 -N -R XXXX:db.domain.com:YYYY tunnelXXXXX@connect.chartio.com -g -o ServerAliveInterval=10 -o ServerAliveCountMax=3 -o ExitOnForwardFailure=yes

Try a query from Chartio and if it works properly quit AutoSSH (ctrl-c) and now we can get AutoSSH running as a service.  

AutoSSH Service Setup

Close the Cygwin prompt and open an Administrative Command Prompt (cmd.exe) by doing the following:

Start->Run, type 'cmd' and then press ctrl+shift+enter, and press Yes at the UAC prompt:

 

In the Administrative Command Prompt run the following to create a Windows Service for AutoSSH.  Please make sure you remember to substitute your your own tunnel number (XXXXX), your database hostname (db.hostname.com), your database port (YYYY). Note the Tunnel number (XXXXX) appears three times.   

cd c:\cygwin64\bin

cygrunsrv -I AutoSSH_XXXXX -p /usr/bin/autossh -a "-M 0 -N -R XXXXX:db.hostname.com:YYYY tunnelXXXXX@connect.chartio.com -g -o ServerAliveInterval=10 -o ServerAliveCountMax=3 -o ExitOnForwardFailure=yes"

Open the Services Administrative Tools application (Start->Run services.msc). Find your newly created AutoSSH service and open the properties panel.

  Go to the LogOn tab and select "This Account", browse for the correct user and specify the password necessary to logon to the current account which  has the SSH private key we generated and have tested works.

Now right click on your service and select start!

You're all set!