Connect PowerBI to a Remote PostgreSQL Database Encrypted with SSL

You read the brochure but you just can't get that hot tub workin'

me@jaykilleen.com wrote this over 4 years ago and it was last updated over 4 years ago.


← Back to the Posts

I recently assisted on the PowerBI Community Forum for the question I am not able to connect Postgre SQL DATABASE.

I had this same problem that most people have. Most documentation refers to the connection of PowerBI to a PostgreSQL database run locally (ie localhost:5432) however when it comes time to connect to your production server you are met with a load of errors particularly if you have your remote server encrypted using SSL (such as LetsEncrypt) or you have firewalls setup and/or have told the database to actively refuse connections via TCP/IP.

Using the built in PostgreSQL connector for PowerBI was fruitless. I attempted to tunnel an SSH connection using Putty but this still caused issues caused by my SSL Certificates. I then attempted to run MMC and add my site certificates to the trusted list of third party sites but could not figure that out. It has something to do with my server hostname not being set even though I have configured Nginx to set a hostname. I'll try and resolve this some day but for now it just doesn't work easily unlike something like PGAdmin3 or 4.

My solution was to go back to the trust ODBC connectors that PowerBI also provide connectors for and allow the PostgreSQL database to allow TCP/IP using mk5 encryption.

Slam together a simple blank query (screw the wizards) and I am up and running:

let
  Source = Odbc.DataSource(
    "
      Driver={PostgreSQL ANSI(x64)};
      Server=hostname;
      Port=5432;
      Database=database_name;
    ", 
    [HierarchicalNavigation=false]
  )
in
  Source