Connect Power BI Desktop to PostgreSQL database through SSH tunnel

[MaxW]

Hi,

I'm trying to connect to a PostgreSQL datasource from Power BI Desktop. 

The database is accesed from a Linux server that I tunnel into using an SSH key file. From that server I connect to the postgres host via username and password.

I can query this database from PGAdmin or Putty Terminal on the same laptop as Power BI desktop, so in theory I should be able to query it from Power BI.

I presume I can't connect to this using the standard postgres connector, but maybe I can connect using an ODBC connection string?

[asheeshgupta2]

I am facing similar issue while connecting the Postgres DB from Power BI.. I am able to connect from Pgadmin. but from Putty and ODBC. i am not able to connect. Can you please guide how to connect Postgres DB using ODBC. Pg adming is using ssh key but how to use this key in ODBC and for putty i am getting the error host is not recognized. Please guide

[Anonymous]

Hi Max,

I'm facing the same exact same problem. Could you resolve it in the past?

Thanks for your time.

[v-yuezhe-msft]

@MaxW,

What is the error message do you get when using the standard postgre connector in Power BI Desktop?

Besides, you are able to connect to postgresql database using an ODBC connection. There is an example for your reference in the following blog.

http://jaykilleen.com/posts/connect-powerbi-to-a-remote-postgresql-database-encrypted-with-ssl

Regards,
Lydia

[AnetaK]

My report is connected to PostgreSQL database using ODBC connector and everything works fine.

Now I want to change Import Mode into Direct Query.

Is it possible to connect via ODBC in DQ mode?

[MaxW]

Hi Lydia,

Thanks for the reply.

I'm not quite sure which IP I should be using, but using the standard connector and either the db host address or the actual Postgres IP address I just get a connection timeout: 

'A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond'

I've potentially just made some progress; If I open a tunnel to the db host address using PuTTY and forward the postgres port to 127.0.0.1 I get 'PostgreSQL Exception while reading from Stream.'  I get this error instantly regardless of the command timeout setting.

For the ODBC connection I had found that blog post, but I'm still unsure how to use it, as it doesn't mention a connection string and I can't find any reference for what to do with my SSH key file..