Connecting Power BI To PostgreSQL

In order to connect one of our PostgreSQL databases from Azure Services to the Power BI desktop application, we came across an interesting configuration setting that we would like to share. There are two sets of setup that you would need to do: setup from Power BI and setup from PostgreSQL/Azure to connect Power BI to PostgreSQL.

Read more: my top 6 free websites for keyword research 2021

From Power BI

To connect Power BI to PostgreSQL, the first logical step would be to go into the Power BI Desktop application and use the [Get Data] > [PostgreSQL database] to establish the connection.

Unfortunately, I ran into the following issue:

I followed their link and went to https://github.com/npgsql/npgsql/releases

Installed the latest stable version of the Npgsql (3.2.6) into my PC and restarted. Unfortunately, the error did not go away.

I decided to find an alternative way to do so and after some research came across a method using ODBC connection.

Following one approach, I went to the following link: https://www.postgresql.org/ftp/odbc/versions/msi/

After running and completing the installation, I went back to Power BI Desktop, but this time, I selected the [ODBC] connection option.

Next step is to select (None) for the Data source name (DSN) and set up the right connection string.

• Do not add the User ID and Password parameters here. As mentioned on the label, you should set non-credential properties)
• Also, note that you have to point to the right driver and you won’t be able to click [OK] until you get the connection string right. Depending on the driver installation that you have chosen, your Driver might look slightly different; please refer to the following link to ensure that you are using the right Driver (https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/)

You will be prompted for your credential information. Note that if you tried in the past already and set up the wrong information, it is possible that Power BI Desktop caches the information. To ensure that you are setting up a “clean” connection, clear the permissions by going to [Options and settings] >> [Data source settings]

Then clear the appropriate connection string.

On the credential screen, ensure that you have the right authentication method selected from the left side and add the proper User name and password information.

If you pass the credential (User and Password) screen successfully, you will be able to see the data structure and select the table to be loaded to your report.

Configure the remote connection The PostgreSQL database is running on a virtual machine with a Linux Operating System located on Azure. So, few steps have to be done before allowing to connect Power BI to PostgreSQL

Through SSH, get access to the server and edit the file pg_hba.conf like the following:

You have to edit the file PostgreSQL.conf too like the following:

Then restart the PostgreSQL service to apply the changes.

From Azure, you have to add a rule to the firewall (inbound rule). Note, that a database should not be accessed from the external network. The best way is to restrict by a trusted source IP address.

Source: https://docs.bitnami.com/azure/components/postgresql/#how-to-connect-to-postgresql-from-a-different-machine