Connecting to PostgreSQL server credential / certificate errors

[Tjeerd]

Hi all,

Since a couple of days we are facing a challenge that we cannot connect to a PostgreSQL server anymore. It was working fine still on tuesday morning, as from wednesday we had these issues. On my desktop, I get the notification that it cannot setup an encrypted connection. If I select OK to connect unencrupted, I get the message "Could not authenticate with credentials provided. 

Still, I think this credential issue is caused by not being able to setup an encrypted connection. On one of my colleagues desktops, I get the following message:

DataSource.Error: An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'

Details:

    DataSourceKind=PostgreSQL

    DataSourcePath= server:port;DataBasename

I have the feeling it has to do with this release, but I'am not sure: https://powerbi.microsoft.com/nl-be/blog/on-premises-data-gateway-october-2024-release/

In this article, I cannot find the steps to take to fix the issue for my desktop version. I cannot find C:\Program Files\On-premises data gateway. or Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config. file. 

Really in need for a solution as we need to get our dashboards up-to-date again! Thank you in advance!

[Tjeerd · Accepted solution]

Hi, 

I've checked with the company that hosts the DB, according to them, the DB settings haven't been changed, so I have the feeling it has to do with PBI itself. 

Meanwhile I have used another method to connect to the DB, rather than using the native PostgreSQL connector, I am using the connector 'ODBC'. 

= Odbc.DataSource("database=[DB_name];driver={PostgreSQL Unicode(x64)};port=xxxxxx;server=[Domain or IP];sslmode=require;", [HierarchicalNavigation=true])

This took some additional work to re-configure our dashboards but it dit the trick. It isn't a solution to fix the PostgreSQL issue itself, but a good workaround for people encountering the same issues. Still curious how this PostgreSQL issue could be fixed though.

[Anonymous · Accepted solution]

Hi all,

New version with fix has been published to download center Download Microsoft Power BI Desktop from Official Microsoft Download Center (version 2.137.1102.0). It will be updated/available in Microsoft Store in few hours.

Best Regards,
Gao
Community Support Team

[Tjeerd]

Hi @Anonymous, thanks for the update. I must say I find it a very big nuisance that PBI just implements these changes resulting in daily used dashboards not functioning correctly. It consumed many hours for us to investigate this issue, finally resolving it ourself by an alternative approach using ODBC. 

Still, highly appreciate your efforts!

[Anonymous]

Hi @Tjeerd ,

Try creating a Postgre SQL connection using Npgsql version 4.0.10 and check if this resolves the connection issue at the desktop.

The blog refers to SQL Server certificates, which should have nothing to do with your issue.

Are you using Npgsql 4.0.16 version? There is a issue related to this, if I hear anything I will reply below.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Tjeerd]

Hi @Anonymous ,

How do I connect using Npsql v4.0.10? I installed it using the .msi, but it still is not working unfortunately.

[Anonymous]

Hi @Tjeerd ,

Try rolling back the PBI Desktop version and check if this solves the problem. You can find download links for previous versions of the desktop here.

Previous monthly updates to Power BI Desktop and the Power BI service

Best Regards,
Gao
Community Support Team

[Tjeerd · Accepted solution]

Hi, 

I've checked with the company that hosts the DB, according to them, the DB settings haven't been changed, so I have the feeling it has to do with PBI itself. 

Meanwhile I have used another method to connect to the DB, rather than using the native PostgreSQL connector, I am using the connector 'ODBC'. 

= Odbc.DataSource("database=[DB_name];driver={PostgreSQL Unicode(x64)};port=xxxxxx;server=[Domain or IP];sslmode=require;", [HierarchicalNavigation=true])

This took some additional work to re-configure our dashboards but it dit the trick. It isn't a solution to fix the PostgreSQL issue itself, but a good workaround for people encountering the same issues. Still curious how this PostgreSQL issue could be fixed though.

[billadee]

Hi, it is clarified in this thread

Our team spent a day to find out if this was the certificate/db change. September version worked right away! 

Anyone who has installed it via Microsoft store - PowerBI tend to be updated automatically. So the update awarness might be low.

They should mention it on their main issues page too.

[zenisekd]

Hi, are you sure that your certificates (on the database side) are up to date? It has happened to me a few times, that it has expired and I had to talk to my colleagues to install up to date ones. This was something that had to be done outside of Power BI.