Qlik Load Over SSH

Connecting via SSH, create an ODBC connection and using the SSH connection.

A client of mine kept their PostgreSQL database locked away behind an SSH connection. As in most situations, convincing the IT department to change security rules for one dashboard was not a hill I was willing to climb. Instead, I used some old techniques I’d used to connect to my home servers back when I had such a thing.

I use SSH rarely now so I make use of a Chrome extension called Secure Shell for the occasional SSH connection. For this blog post, I’m going to be using the Chrome extension, but the same technique will work with PuTTY or any other SSH tool.

Connecting via SSH

In the image above, a connection was established to the PostgreSQL database through the SSH client. The connection was then routed through a port on the local machine.

  1. The username to connect via SSH to the PostgreSQL database.
  2. The address of the PostgreSQL server.
  3. -L is a flag used in Linux that tells the SSH client to forward the connection. Depending on the client you are using, the flag may not be necessary.
  4. The local port being used by the data connection we will set up next.
  5. The local address being used by the data connection we will set up next.
  6. The open port on the PostgreSQL database that is listening over SSH.

Create an ODBC Connection

Once the SSH connection was set up, I created a System DSN that connected to the PostgreSQL database by routing through the local port. (The numbers below correspond to the numbers in the image above.)

Usage

Once the connection was set up, using it was relatively simple. 

  1. I connected to the database through through my SSH client.
  2. I opened Qlik.
  3. I created a data connection using the ODBC connection.

Once the connection string was created, selecting tables was the same as connecting to any other database. The only annoyance is having to connect via the SSH client before reloads.


This article originally appeared on the Unconventional QlikView blog. 

Trey Bayne's photo
Senior Sales Engineer
Trey works to enable partners and guide the future development of QlikMaps. Trey has worked with Qlik for nearly a decade. See more from Trey on Twitter (www.twitter.com/QVgorilla) and on his blog, Unconventional QlikView (http://unconventionalqlikview.tumblr.com).

 

Contact Us

National Office Telephone | Mon-Fri 8:30am-5:30pm CT