Cannot connect Power Bi Service to PostgreSQL hosted in Azure

[Rnaqawa]

Suddenly we started to face this issue, we are unable to connect Power Bi Service directly to PostgreSQL Database hosted in Azure.

we started seeing this error:

An exception occurred: DataSource.MissingClientLibrary: Please install Npgsql version 4.0.10.0 or earlier. (Session ID: abb6d1a5-0364-408e-a546-a5c1881b05c2, Region: uk)

Knowing that an on Prim data gateway is not needed to connect PostgreSQL as a source, referring to Microsoft documentation.

all of discussions and threads are saying that i have to download NPGsql v4.0.10 but in our case we are not using any on prim data gateway.

[Cumaali]

@Rnaqawa Did you find the solution. I have been trying for days, but I could not succeed. Can you please let me know the details?

[michaeluls33]

Hello,

Has there been any update on this issue? I am receiving this same error when trying to connect to postgres through an on-prem data gateway using Power BI Online. 

I can connect on Power BI desktop using Npgsql, however, this doesn't appear to be an option on Power BI Online...

[AdamSabourin]

Yes! I got the dataflow to save. One of the tables had a column with the name "TSV" 

Once that was removed, it saved. 

[Anonymous]

Just got an update from the support team that they have fixed the issue, which I can confirm seeing my datafloiws running again. 

[AdamSabourin]

I'm getting an issue still. In power query i see the data and can perform cleaning steps on PostreSQL data but upon save and close I get this on refreshes:

"Error: An error happened while reading data from the provider: 'We don't support CLR type 'NpgsqlTypes.NpgsqlTsVector'.' Request ID: 3249c2a8-7b04-89f6-9e2d-38e0db0838ec Activity ID: 92c2b05f-2e76-4a2b-ad4c-ceb9116171ad"

[Anonymous]

Also an update from my side, I havber reached out to the powerbi team via our support contracts and showed the issue in a call. They are further investigating it indeed. Looks like the desktop app works fine, also the refreshes when you publish and refresh the report online, so that could be a temp workaround.
As soon as I get more info back from my support contact I'll add a reply to this thread.

[Rnaqawa]

Thank you so much Rutger, Fingers crossed.

[v-xiaoyan-msft · Accepted solution]

Hi @Rnaqawa ,

Sorry for my lose. We have submitted to internal icm 311546402 , engineers are doing their best to fix this issue . please be patient and I will give you feedback here once there is any progress.

Best Regards,
Community Support Team _ Caitlyn

[fglunt]

Hi, Community Support Team _ Caitlyn.

Can you give us an update on  internal icm 311546402?  Has this been resolved, or should I start looking at another solution?

[Rnaqawa]

Thank you, Caitlyn 
Looking forward... 

[Loan]

Hello, same thing for me, since Tuesday (Mar 26th) too.
All my Dataflows using Postgresql connection returnes this error (Error: DataSource.MissingClientLibrary: Please install Npgsql version 4.0.10.0 or earlier)

The connection is directly in PowerBi Service (app.powerbi), online, so it's not possible to install or downgrade the Npgsql version.

Looking for a solution too !

[Anonymous]

I can relate, same issue here since Tuesday (Mar 26th) where one of my dataflows using Postgres returnes the same error (Error: DataSource.MissingClientLibrary: Please install Npgsql version 4.0.10.0 or earlier). As it was created in the MS Managed PowerBI environment (https://app.powerbi.com/) I cannot install anything.

[v-xiaoyan-msft]

Hi @Rnaqawa ,

The PostgresSQL connector doesn't work out of the box, you need to do quite a few things first

1. Install Npgsql which allows .NET access to PostgresSQL.  I installed it using the Pakagae Manager Console in Visual Studio; detailed instructions are here: https://www.nuget.org/packages/Npgsql/

You need to connect over SSL to AWS which isn't setup by default (on my Windows 10 machine anway)

2. Download the AWS public key from here https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

3. Convert that to a certificate using https://www.sslshopper.com/ssl-converter.html (convert to PKCS#7/P7B)

4. Import that certificate to the Trusted Room Certificate described here http://www.cs.virginia.edu/~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm

5. Test access from Power BI desktop using the PostgresSQL connector and it should work

This was resolved after following the steps below ( Original link on - https://community.powerbi.com/t5/Integrations-with-Files-and/Connecting-to-PostgresSQL-hosted-on-AWS... )

Please check if it helps.

Best Regards,
Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Rnaqawa]

Hi Caitlyn 

Thank you for your message 

I totally understand what you are saying, its just that our PostgreSQL is hosted in Azure not AWS. And as you know when hosting in Azure there is no need for a data gateway to connect from Power bi service to the DB.

And we have been working like this with no issues for the past 6 months.

It looks like suddenly PowerBI service has upgraded their version of npgsql 

[Rnaqawa]

Hi @HenriqueReis  Can you please elaborate more ?

[HenriqueReis]

Hi,

Have you already check all the data format inside the program?