CODEWITHSUNDEEP
powerbipowerbi-desktoppostgresql-9.2powerbi-datasource
Ramin Maharjan
Ramin Maharjan

Reputation: 25

Cannot connect on-premise gateway with postgres on PowerBI

With powerbi desktop I can easily connect with postgres database on server(192.168.1.5:32100) running on a port and a database_name. But doing the same with PowerBI on premise gateway I could not connect when adding a data source on gateway setting(gateway cluster). Below screenshot for reference. The first picture is of PowerBI desktop and the second one is on gateway settings. Is it something with the port?If yes I was able to connect on PowerBI desktop, if no then is there any way or am I missing something. Thanks in advance for the help. enter image description here enter image description here

Upvotes: 1

Views: 6892

Answers (2)

Eyles IT
Eyles IT

Reputation: 11

I cannot comment because I don't have 50 reputation. I downloaded the psqlodbc_16_00_0000-x64.zip file from the link above and opened it. I get a dialog from Windows saying

Windows protected your PC
Windows Defender SmartScreen prevented an unrecognised app from starting. Running this app might put your PC at risk.
App: psqlodbc_x64.msi
Published: Unknown publisher

Windows Defender dialog

I've never received a dialog like this before. Is the link above safe, or has it (or my PC) been compromised?

I also downloaded an earlier file psqlodbc_13_02_0000-x64.zip and ran it. It gave no Windows Defender dialog warning.

Upvotes: 0

AmilaMGunawardana
AmilaMGunawardana

Reputation: 1830

Method 1

  1. Install the Npgsql provider on the server that's hosting the data gateway (https://www.nuget.org/packages/Npgsql/4.0.2)

  2. Reboot the server

Method 2

Connecting to PostgreSQL database using ODBC Connector

  1. Using the below link download the latest version of ODBC connection for PostgreSQL database and Install https://www.postgresql.org/ftp/odbc/versions/msi/
  2. After installation completed, open the Power BI Desktop and choose Get Data
  3. Instead of selecting postgresql database, this time we will choose ODBC. Select ODBC and click on Connect
  4. A window will open and select “None” as Datasource Name (DSN)
  5. In the same window, we have connection string. Here we have to provide proper connection details to connect postgresql database. We won’t be able to click “OK” until we have provided the correct connection string.
  6. To get the proper connection string use this link. https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
  7. We have given the below connection string and able to click on “OK” Connection String: Driver={PostgreSQL ANSI(x64)}; Server=localhost; Port=6433; Database=foodmart
  8. Enter the credentials and select the tables
  9. Create a report and publish in Power BI service
  10. Login to Power BI service and open settings of a report dataset
  11. Expand gateway connection and now we can see that gateway is enabled and it is connected local gateway
  12. Expand the data source credentials and enter the database credentials
  13. Now go to datasets and click on refresh icon of the dataset
  14. Dataset will get refreshed using the gateway

Should now be able to use the PostgreSQL data source type when creating the data source in Power BI Service.

Thanks,

Upvotes: 2

Related Questions