Forum Discussion
Power BI Postgres connection issues (credentials/invalid certification)
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
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
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