Forum Discussion
Power BI Postgres connection issues (credentials/invalid certification)
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.
13 Replies
Try unchecking the encrypt connection option in connection settings. This can be done by going to edit permissions.
Hey, I've already tried that... That's what fixed a smiliar issue a few months ago but no luck this time unfortunately
Try deleting the connection and start again.
Hey, I've tried that too - even on different devices. Restarted my laptop - all the basic ones... I've been on it since yesterday
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
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?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
