Connect Excel to PostgreSQL through SSH Tunnel (Part 2)

Now that you have setup your SSH tunnel using Putty It’s time to get excel involved. (Not there yet? go back to part 1)

You will need Office 2013 Professional to get access to the software and you will need to download Power Query from Microsoft. Here’s the current link http://www.microsoft.com/en-us/download/details.aspx?id=39379

Before you connect to a PostgreSQL database, the PostgreSQL .Net Data Provider needs to be installed. To install the PostgreSQL .Net Data Provider, see Install the PostgreSQL .Net Data Provider.

Once you’ve installed Power Query and the PostgreSQL adapter you are ready to open Excel. Navigate to the new Power Query tab and select From Database

Power Query From Database

Power Query From Database

You’ll get a popup to setup your database

Power Query PostgreSQL Database Connection Popup

 

Server will be localhost and the tunnel port that you setup before. If you’re following along closely we setup localhost and used the default value of 5432. This plugin doesn’t appreciate non-default port numbers so be sure to use the default.
Database is the name of your target database on the server

You will need to enter your database username and password also. You will likely get the following popup which is a good sign and you should accept.

Power Query Encryption Popup

Power Query Encryption Popup

If you run into trouble leave a comment and I’ll see if I can help.

Connect Excel to PostgreSQL through SSH Tunnel (Part 1)

This is a solution I managed to cobble together through trial and error and a lot of web searching. The fundamentals will be applicable to any remote database and should save you a lot of time searching.

1) Setup the tunnel with Putty.
Download Putty Here if you need it: http://the.earth.li/~sgtatham/putty/latest/x86/putty.exe

Once installed you need to create a session. It’s important to do this first as putty likes to delete configurations when you change the settings:

Host name will be the remote server that hosts the database: something like www.mywebserver.com

Putty Configure Settings

Putty Configure Settings

It’s good to test this connection before going forward.

Once you have confirmed your connection to be functional you will need to reopen putty and setup your sever connection again. This time give it a name in “Saved Sessions” and click “Save” to store the configuration.

Next navigate to the Tunnels Section of the Connection > SSH menu

Putty SSH Tunnel

Putty SSH Tunnel

This is where you are going to define your local redirect settings:
Source Port is for the port on your computer that will be used for the connection (typically called localhost)
Destination is the location on the remote server that you need to access. For me and for most databases this is going to be localhost again. Be sure to include the port number for the database. PostgreSQL uses 5432 by default. (It’s important to use the default because the excel addin only supports default port numbers) (127.0.0.1 is interchangeable with localhost and just means redirect to the computer I’m on)

Putty Tunnel Setup

Putty Tunnel Setup

Go back to Session and be sure to Save your configurations otherwise they will be lost next time you start putty. Like I said, Putty really likes to delete configurations.

You’ll get this screen to login and once complete your tunnel will be setup!

Putty Login Screen

Putty Login Screen

Continue to Part 2 – Setting up Excel

Working with a 4K display on Windows 8 (Refresh Rate)

I’ve been using my 4K monitor for a few months now and it’s changed the way I work and interact with the computer. It isn’t without some challenges. If you are accustomed to a 60hz monitor (a monitor which can refresh the screen 60 times per second) and you switch back to 30hz you’re going to feel like there’s some lag in the mouse. The technology is moving in the direction of higher frame rates as HDMI standards start to catch up with the screens but for the average user, those advances are expensive and difficult to work with. Most shows and movies are presented in 30hz so you wont notice any difference when watching movies and the 4K makes for a really stunning picture quality.

Having the mouse lag took some getting used to but it hasn’t had a major impact on my productivity and I’ve been writing software and doing 3D modeling without any major problems. When I want to play a game I switch back to 1080p to give my graphics hardware some room to breath and to get the increased frame rates that are so critical to gaming. To switch between modes follow these steps:

  1. Right click on a blank area of the desktop and select Screen Resolution.
    WorkingWith4k3
  2. You’ll need to change Resolution down to 1920 x 1080 in order to access the higher refresh rates. Windows knows enough to limit your selections in the next step.
  3. Before applying the new settings chose “Advanced settings” to get the following dialog
    WorkingWith4k4
  4. Select the 60 Hertz from the drop down and hit apply. All your changes will go into effect.
  5. To change back to 4K just do step 1 and 2 but select the 4K resolution. The hertz will automatically set to 30 hertz.