An exception occurred: Please install Npgsql version 4.0.10.0 or earlier.

[luqui_maps]

Hello,

I am trying to connect to use postgresql from app.powerbi.com (i guess that function was from powerquery)

However it seems that some configuration has to be done by your side that I have searched here in forums.

Please help me out

But also my question is below and my perception. 

Please correct me if it is wrong

Using gateway (ODBC) : Slow data loading / refresh

Not using gateway(ODBC), native query : much faster than using gateway (ODBC)

In that case, the above reason, I didn't use gateway option (we have it)

Issue #1)

Error messages) 

An exception occurred: Please install Npgsql version 4.0.10.0 or earlier. (Session ID: 246149e0-a390-498b-8c6c-4d0ff383348f, Region: brazil)

Issue #2)

I have connection thru powerbi desktop and connected to postgresql (not using obdc) using native query.

All data gathering has been done succesfully.

However, I have uploaded to app.powerbi.com and tried to refresh dataset.

I got below message! How can I fix it?

Can you guide me?

[lbendlin · Accepted solution]

You need a gateway, and you need to install the indicated PostGreSQL driver on the gateway.  There is a well documented bug that the gateway installer is missing that driver.  You need to borrow the driver files from your desktop setup and paste them into the ADO folder on the gateway.

[Anonymous · Accepted solution]

Hi @luqui_maps ,

Please refer to the following steps: 

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

More details: Solved: The remote certificate is invalid according to the... - Microsoft Fabric Community

Solved: AWS RDS Postgres refresh from PowerBI service - th... - Microsoft Fabric Community

How to Get Your Question Answered Quickly 

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

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

[luqui_map]

Dear Rongtie,

Thank you for your prompt response.

My problem was not fix yet.

The problem is not using PowerBI desktop to update dataset.

The real problem is the following.

1. Native Query in Power BI Desktop Connect - Success

2. Updating Power BI Desktop to update dataset in my notebook - Success

3. Uploading PBIX file to Power BI Workspace - Success

4. Update dashboard in Workspace by using automatic or manual update - FAIL

   The error message : 

   Failed to update data source credentials: An error happened while reading data from the provider: 'The   remote certificate is invalid according to the validation procedure.'

How to fix the #4 ? 

Regards,

Lucas

[lbendlin · Accepted solution]

You need a gateway, and you need to install the indicated PostGreSQL driver on the gateway.  There is a well documented bug that the gateway installer is missing that driver.  You need to borrow the driver files from your desktop setup and paste them into the ADO folder on the gateway.

[luqui_map]

Hello,

Thank you for your prompt response.

The question was 

"I dont have any problem to use ODBC and Gateway.

But it's too slow." 

So I would like to use "Native Query" to update the dashboard in Workspace automatically.

The problem is not using PowerBI desktop to update dataset.

The real problem is the following.

1. Native Query in Power BI Desktop Connect - Success

2. Updating Power BI Desktop to update dataset in my notebook - Success

3. Uploading PBIX file to Power BI Workspace - Success

4. Update dashboard in Workspace by using automatic or manual update - FAIL

   The error message : 

   Failed to update data source credentials: An error happened while reading data from the provider: 'The   remote certificate is invalid according to the validation procedure.'

How to fix the #4 ? 

Regards,

Lucas

[lbendlin]

As I said you need to install the ADO drivers on all gateway cluster members.

[luqui_map]

How can I install the ADO drive at PowerBI workspace (Azure) ?

I dont have any direct access to Power BI Workspace.

Can you explain it?

[lbendlin]

Not in the workspace, in the gateway cluster VMs.

[luqui_map]

If I install the ADO drive in the gateway cluster VMs, I can update in workspace by using schedule update? 

As I mentioned above, I can update and connect to dataset by using direct query thru PowerBI desktop. 

My question is the second one. 

Update thru ODBC in Power BI Cloud Workspace  : success

Update thru Native Query in Power BI Cloud Workspace  : fail.

[lbendlin]

If I install the ADO drive in the gateway cluster VMs, I can update in workspace by using schedule update? 

Yes.  We do that in our environment and we know that it works.

[luqui_map]

Great.

So how can I install the AOD drive in the VMs of Workspace of powerBI?

Can you guid me more details?

[lbendlin]

NOT workspace.  Gateway.  On each gateway VM install the latest version of Power BI Desktop.  Then copy the npgsql files from the ADO folder of the desktop install to the ADO folder of the gateway install.  Then you can remove the desktop again.  Lastly restart the gateway service.

[johnbasha33]

  i followed the same steps, but still i am facing the same issue, unable to create a connection for postgres sql in power bi service. 

Please install Npgsql version 4.0.10.0 or earlier.



Unable to create connection for the following reason: Unable to connect to the data source. Either the data source is inaccessible, a connection timeout occurred, or the data source credentials are invalid. Please verify the data source configuration and contact a data source administrator to troubleshoot this issue.

@lbendlin

[lbendlin]

 i followed the same steps

which steps?  Did you remember to restart the gateway service after copying the files?

[johnbasha33]

@lbendlin  
quick Update: its a simple fix i made it work.
i had to install this in my Gateway cluster VM Npgsql version 4.0.10.0
and then restarted the gateway server. 

this is a supported file for postgres sql, after that i am able to create connection successfully in power bi Service. 🙂 

[luqui_map]

Understood.

I have an AWS VM where PowerBI desktop installed.

Where would be the ADO folder? I guess, the gateway side would be in AWS VM.

Am I right?

[lbendlin]

Desktop:  C:\Program Files\Microsoft Power BI Desktop\bin\ADO.NET Providers

Gateway: C:\Program Files\On-premises data gateway\m\ADO.NET Providers