cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ecate New Member
New Member

Connect PowerBI Desktop with PostgreSQL

I'm tring to connect power BI to a Posgres 9.4 database installed into the same machine (my windows 10 laptop).

I've overcome the error about Npgsql intalling Npgsql.dll with GACutil and modifying the file machine.config.

However now I've the following error:

"Ssl connection requested. No Ssl enabled connection from this host is configured."

Why a ssl connection is required?

Can I force Power BI not to use ssl?

Or is that a problem with Postgres and I have to configure it differenly?

Does Anyone fixed it?

Thanks!

51 REPLIES 51
BlancaHernandez Frequent Visitor
Frequent Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Hi, 

I have faced exaclty the same problem and I tried to set up Postgres as the documentation explains, but I get still the same message, any hints?

 

Thanks!

mfairchild Frequent Visitor
Frequent Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Im also having the same problem.  hopefull

ashishrj Senior Member
Senior Member

Re: Connect PowerBI Desktop with PostgreSQL

@mfairchild This blog in Power BI Community might help click here

mfairchild Frequent Visitor
Frequent Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Thanks for that link ashishrj.   I was actually trying to connect excel to postgres, and from there export to Power BI.

The doc at https://support.office.com/en-za/article/Connect-to-a-PostgreSQL-database-Power-Query-bf941e52-066f-... seems straight forward, except its not clear how to specify additional options for the Npgsql driver.   Plenty are mentioed at http://www.npgsql.org/doc/connection-string-parameters.html.  I need to set the `Trust Server Certificate=true` value.  The dialog in excel does not seem to offer fields for raw connection string tho.  Any ideas how to specify more details, or jsut provide a raw connection string?

 

jason_rmh Occasional Visitor
Occasional Visitor

Re: Connect PowerBI Desktop with PostgreSQL

I have had the same issue, connecting to a local instance of PostgreSQL.

I think thePostgreSQL instance will need to be configured for SSL using OpenSSL, although Im not yet sure how to do that.

 

As a work around, I am using an ODBC connection.

 

Install latest npgsql ( I installed v3.0.5).

Make sure you check the options to install to GAC when installing npgsql.

This will place the relevant assemblies in the windows assembly cache.

 

I then created a DSN connection to my postgres database (Control Panel > Admin Tools > Data Sources (ODBC)).

Remember, if on 64 bit machine you will need to use the ODBC tool from '%SystemRoot%\SysWOW64\odbcad32.exe' rather than the one from admin tools, other wise your DSN will not show in the application later on.

 

From Power BI, select 'ODBC' from the list when connecting to your data, you should then be able to select your DSN from the drop down that appears in Power BI.

 

Click OK, you will be prompted to enter username / password for the database connection.

Power BI will then scan the database and list the tables available for you to use.

 

Maybe not ideal using ODBC, but will get you going for now.

 

Hope this helps.

Jason

gum Frequent Visitor
Frequent Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Having the same issues, thanks for the info so far.

 

From what I can tell after version 3.x of Npgsql the behaviour changed from:

warn about non-encrypted connections and allow connection anyways after prompt

to:

require encrypted connection

and no one knows how to change connection params in Excel.

 

Excel 2010 works when using https://github.com/npgsql/npgsql/releases/tag/v2.2.5 (rc3 https://github.com/npgsql/npgsql/releases/download/v2.2.5/Setup_Npgsql-2.2.5.0-r3-net45.exe) and installing only the 1st, 3rd, and fourth check boxes.

Doing the above does not require any messing about with GACUtil and machine.config.

 

Power Query will then warn about the connection to postgresql not being encrypted but allow you to connect anyways.

 

Can't get any of this to work in Excel 2016 though...

Npgsql_2.2.5.PNG

 

vvanasperen Frequent Visitor
Frequent Visitor

Re: Connect PowerBI Desktop with PostgreSQL

The "solution" from jason_rmh works in the sense that I can reach the Postgres database tables. But what if I want to use a query to import certain information from multiple tables into one dataset? Could anyone please advise. Thanks.

 

Vincent

arify Established Member
Established Member

Re: Connect PowerBI Desktop with PostgreSQL

Hi everyone,

 

Npgsql drivers have a different behavior in the last few months' versions, that's why this is happening. We're fixing this now, it'll be in PBI releases in the next few months.

 

For the workaround, you can change the encryption setting of your database credential:

pg.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Sorry about the inconvenience and thanks for reporting this to us!

Oguz

Highlighted
B129 Member
Member

Re: Connect PowerBI Desktop with PostgreSQL

Hi to everyone,

 

   I've faced the same issue and I've found the solution thanks to @arify.

1) Install the latest release of Npgsql (by now the 3.0.5 version -> Npgsql-3.0.5.msi and Setup_NpgsqlDdexProvider.exe)

2) Restart the pc

 

If you try to access into the database it might give you this error:

 

ERRORE CONNESSIONE DATABASE POSTGRESQL (3).png

But if you follow the indications provided by @arify you can solve this problem.

 

3) You have to enter in the "Data source settings"

 

postgresqlConnectionProblem (1).png

 

4) and then edit the "Encryption" option.

 

postgresqlConnectionProblem (2).png

 

p.s. For Npgsql I provide the installers instead of the .zip because it's more easy to use.

 

#I'M Not An Expert#

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 35 members 952 guests
Please welcome our newest community members: