Power BI to PostgreSQL Connection Issues - From authentication error to Successful connection
Connecting Power BI to PostgreSQL sounds straightforward - until it suddenly fails with an authentication error that doesn’t clearly explain what went wrong. That’s exactly what happened to me during a sprint.
If you’re facing similar issues connecting Power BI to PostgreSQL, I hope this blog helps you fix those.
In this blog, I will share the challenges that I faced and how I resolved to connect successfully.
The messy raw data is structured into Fact and Dimension tables with appropriate relationships
in PostgreSQL and is ready for report generation. Now it’s time to import this data into PowerBI.
On my first attempt to get data from PostgreSQL database, I faced an authorization error as below:
Instantly, I thought it could be a credential issue because I learnt that Power BI often caches "Empty" or "Windows" credentials for localhost, which fails when PostgreSQL18 demands a SCRAM-encrypted password.
So, I updated the stored credentials in PowerBI as in the below screenshot:
In PowerBI Desktop, navigate to File -> DataSource settings ->edit permissions, select Edit.. button in Credentials and enter correct username and password.
While this is a common fix, the error persisted—indicating a deeper problem.
As we’re working in Sprints, I could not research more to find the root cause and do proper fix.
With some research I did a quick workaround to let PostgreSQL trust all the users and give access to the requested database by re-configuring the pg_hba.conf file by replacing scram-sha-256 with trust. After importing data into PowerBI I had to switch it (host all all 127.0.0.1/32 trust) back to scram-sha-256.
This worked immediately, Power BI was able to connect without any issues.
However, this approach had two major drawbacks:
It bypassed authentication entirely (not secure)
I had to repeat this change every time I needed to reconnect
This clearly wasn’t a sustainable solution.
Later in my free time I researched more to find a permanent solution.
The problem turned out to be a mismatch between PostgreSQL’s authentication method (SCRAM-SHA-256) and the driver used by Power BI.
If “Encrypt Connections” under Encryption is checked as in the Fig.2, we see the below warning:
In PostgreSQL server registration settings, SSL mode will be by default set to Prefer. So, when PowerBI requests a connection, PostgreSQL attempts an SSL connection first. If the server does not support SSL, it falls back to a non-encrypted connection. This is often the default, but it may mask server misconfigurations.
If you select “OK”, an unencrypted connection is set up and you will see tables in the Postgres database and you can select required tables to be loaded.
Alternatively, you can uncheck the “Encrypt Connection” box to establish an unencrypted connection. But, if you want to establish an encrypted connection which is highly recommended you have to install a “Trusted Root Certificate”. This is needed for PowerBI to verify the Postgresql server.
Root Cause & Technical Fix
Let’s understand in detail about why authentication errors occur.
PowerBI relies on the Npgsql driver to communicate with Postgresql.
Postgresql 10 introduced scram-sha-256 authentication mode instead of earlier MD5 authentication mode to provide stronger security during data exchanges. So, when PowerBI tries to connect to Postgresql, Postgresql expects username and password encrypted using scram-sha-256 mode. PowerBI uses the Npgsql driver that came bundled with PowerBI installation which still uses earlier MD5 authentication mode and hence struggles to authenticate. So, explicit installation of Npgsql driver version 4.0.10 is required which can be done by installing from Npgsql GAC installer version 4.0.10.
In short, The disconnect happens because modern PostgreSQL defaults to SCRAM-SHA-256 encryption, while the older Npgsql drivers bundled with some Power BI versions only speak MD5. It’s like trying to have a conversation where one person is speaking a language the other hasn't learned yet.
If you are connecting to a local instance of PostgreSQL database from PowerBI, verify how your PostgreSQL server expects you to connect. If it is set to scram-sha-256 in pg_hba.conf, the client(PowerBI) must provide a password encrypted with that method.
Final Solution
Updating the Npgsql driver is the primary way to provide a password encrypted with scram-sha-256 method.The driver acts as the "translator." Older or default drivers don't know how to perform the SCRAM-SHA-256 handshake, so they fail to send the password in the format PostgreSQL 18 requires. By installing a modern version (4.0.10+), you give the driver the ability to encrypt your password correctly before sending it to the server.
However, even with the new driver, you must ensure two things:
GAC Installation: When installing Npgsql, you must select the option to "Install to GAC" Power BI is a 64-bit application and will only "see" the driver if it is registered in the Windows Global Assembly Cache. You must reboot your machine after this installation for it to take effect.
Clear Caches: After the update, you must Clear Permissions in Power BI (File > Options > Data source settings). If Power BI tries to use a "remembered" (and likely broken) connection string from before the update, it will still fail.
Sometimes, updating Npgsql driver might corrupt some path variables or system variables and you get the below error when you open Pgadmin to work with Postgresql database:
In such a case, you need to uninstall and install all the tools again.
For the smoothest connection between PowerBI and PostgreSQL, follow the below recommended installation order:
PostgreSQL (Database Server/Client)
Ensure the database is installed, running, and accessible
Npgsql driver for the "PostgreSQL database" connector, while psqlODBC drivers is only needed for "ODBC" connector (32-bit or 64-bit, matching your Power BI version)
Install latest Npgsql driver by downloading MSI installer for version 4.1.x or 4.0.17 from the GitHub releases page. Ensure the drivers(Npgsql or psqlODBC ) match your Power BI architecture. If using a 64-bit Power BI, install the 64-bit ODBC driver.
Native(Npgsql) vs. ODBC: Modern Power BI has a native Npgsql provider, but if you face connection issues, using the manual ODBC installation method is a reliable backup.
Power BI Desktop
Open Power BI, select Get Data, then choose PostgreSQL database (for native connection) or ODBC (to use the DSN you configured)
While modern versions of Power BI (post-December 2019) have built-in connectors, installing the psqlODBC driver first ensures that system-level DSN (Data Source Name) configurations are recognized and available for a more stable, customizable connection.
What I Learned:
Authentication errors are often driver mismatches, not just wrong passwords
Temporary fixes (like trust) help debugging but should not be permanent
Power BI credential caching can silently break connections
Driver compatibility (SCRAM vs MD5) is critical
Final Thoughts
What initially looked like a simple connection issue turned into a deeper learning experience about how tools interact under the hood.
In real-world projects, especially under time pressure, it’s easy to apply quick fixes and move on. But taking the time later to understand the root cause not only solves the problem permanently—it also builds stronger intuition for future challenges.
