Power BI Postgres connection issues (credentials/invalid certification)

[koralka]

Hey all, 
I'm having trouble connecting Power BI Desktop to our Postgres database. The issue is - majority of dbs are Postgres and other ones have no problems, it's just this new one that won't connect. It throws this error: 

"Details: "An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure"
And then once it lets me go further, it thorws a credential error (I can connect to the db with those credentials using several different tools so they're 100% correct).

I checked everything that fixed simialr issues before (privacy settings, encryption settings, VPN is in use...). 
Help would be much appreciated. 

I had a look at a few forums and I feel like I tried everything at this point. 

[v-dineshya]

Hi @koralka ,

Thank you for reaching out to the Microsoft Community Forum.

You are facing issue while connecting Power BI to PostgreSQL, especially the "remote certificate is invalid" and credential errors.

Please try below two options.

Fixing the Remote Certificate Error: This error typically occurs when Power BI cannot validate the SSL certificate presented by the PostgreSQL server.

1. Download the SSL Certificate, If you are using AWS RDS, download the public key from AWS RDS CA bundle.

2. Use SSL Shopper’s converter to convert the .pem file to PKCS#7/P7B format.

3. Import the converted certificate into the Trusted Root Certification Authorities on the machine running Power BI Desktop.

4. Check the server name used in Power BI matches the name in the certificate exactly.

5. In Power BI Desktop, Transform Data --> Data Source Settings --> Edit Permissions --> Uncheck "Encrypt connections".

Fixing Credential Errors: Even if credentials are correct, Power BI might reject them due to cached permissions or mismatched settings.

1. Clear Cached Credentials, Go to File --> Options and Settings --> Data Source Settings --> Global Permissions, then clear old permissions and re-enter credentials.

2. Check pg_hba.conf allows connections from your IP: host all all 0.0.0.0/0 md5

3. If the DB requires SSL, make sure SSL is enabled in Power BI and the certificate is trusted.

4. Install the latest version of Npgsql and check GAC installation is checked.

5. Restart both the PostgreSQL server and Power BI Desktop after making changes.

Note: Power BI Service requires an on-premises data gateway to handle SSL certificates properly for scheduled refresh. Check port 5432 is open and not blocked by VPN or firewall.

Please refer below links.

Solved: PostgreSQL Connector - The remote certificate is i... - Microsoft Fabric Community

Solved: Unable to connect to PostgreSQL with SSL - Microsoft Fabric Community

Solved: Power BI cannot establish secure connection to the... - Microsoft Fabric Community

Solved: Add an SSL Certificate to PostgreSQL Database Conn... - Microsoft Fabric Community

Solved: How to fix Invalid Certificate on postgresql serve... - Microsoft Fabric Community

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

Solved: Re: Issue with Power BI Service Refresh for Postgr... - Microsoft Fabric Community

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[koralka]

Hello,
Thank you for answering. I manage to 'fix' the "remote certificate is invalid" with unticking the 'encrypt connection' box.

Power BI keeps throwing credentials issues, I cleared permissions, restarted everything so many times, tried on different devices - still the same. I can connect to that database with many other tools (pgAdmin, Datagrip, Metabase) - all work fine. It's clearly not a credential issue. What else can I do please? 

[v-dineshya]

Hi @koralka ,

Please try below things to fix the issue.

1. Sometimes Power BI fails to resolve the server correctly unless the port is explicitly specified. Try using the below thing in the server field when connecting.

your-db-hostname:5432

2. Check the PostgreSQL server allows connections from your IP and uses the correct authentication method.

host all all 0.0.0.0/0 md5

Note: If you are using SSL, change md5 to scram-sha-256 or cert as needed. And then restart PostgreSQL after editing this file.

3. Power BI uses the Npgsql .NET driver to connect to PostgreSQL. If it's outdated or misconfigured. Download Npgsql v4.0.17 latest supported by Power BI. During installation, check "GAC Installation" to ensure it's registered system-wide.

4. If native connector fails, use an ODBC connection. Install PostgreSQL ODBC driver.
Set up a DSN and connect via Power BI using ODBC instead of the native PostgreSQL connector. This bypasses some of the native connectors.

5. If other databases work but this one doesn’t, Check if the user role has access to the specific database. Check the user has CONNECT privileges on the database.

GRANT CONNECT ON DATABASE your_db TO your_user;

6. Check you are using the latest Power BI Desktop. Older versions may have compatibility issues with newer PostgreSQL setups or Npgsql versions.

7. Enable Logging in Power BI to get more insight. Enable diagnostic logging in Power BI Desktop.

File --> Options --> Diagnostics --> Enable tracing

Check logs in below path.

C:\Users\<YourUser>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @koralka ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[koralka]

Hello,
Thank you for checking in me. So - there were a few issues - turned out that the infastructure had SSL turned on on the database side - hence the certificate issue. 

I have to be on VPN to connect to the DB - I managed to publish the ready to use model to power BI Service but now I can't connect to the database from there. I assume it's to do with privacy setting on the DB's side again. What would be the best option here? To use a gateway? 

Thank you so much for your help 

[v-bmanikante]

Hi @koralka,

Thanks for your response.

I am also a member of the CST team and I recommend raising a support ticket so our support team can assist you directly.
Please refer to the following link for instructions on how to create a support ticket:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
B ManikanteswaraReddy

[v-dineshya]

Hi @koralka ,

We are following up to inquire whether you have raised the support ticket. If you have already done so, we kindly request you to share your feedback regarding the issue raised.In case a solution has been provided, we would be grateful if you could share it with the community. This will assist others facing similar challenges and benefit the wider community. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Regards,

Dinesh

[koralka]

​Hello,

Thank you for reaching out. 
I have not created the ticket yet as I am struggling to do so - I believe the UI has changed a little bit.

Thank you for you support,
Karolina

[v-dineshya]

Hi @koralka ,

Thank you for the update. Once you have created the Support ticket,  Please do let us know.

And, if you have any further query do let us know.

Regards,

Dinesh

[ChiragGarg2512]

Try deleting the connection and start again.

[koralka]

Hey, I've tried that too - even on different devices. Restarted my laptop - all the basic ones... I've been on it since yesterday 

[ChiragGarg2512]

Try unchecking the encrypt connection option in connection settings. This can be done by going to edit permissions.

[koralka]

Hey, I've already tried that... That's what fixed a smiliar issue a few months ago but no luck this time unfortunately