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.
Add a comment
|
2 Answers
Method 1
Install the Npgsql provider on the server that's hosting the data gateway (https://www.nuget.org/packages/Npgsql/4.0.2)
Reboot the server
Method 2
Connecting to PostgreSQL database using ODBC Connector
- Using the below link download the latest version of ODBC connection for PostgreSQL database and Install https://www.postgresql.org/ftp/odbc/versions/msi/
- After installation completed, open the Power BI Desktop and choose Get Data
- Instead of selecting postgresql database, this time we will choose ODBC. Select ODBC and click on Connect
- A window will open and select “None” as Datasource Name (DSN)
- 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.
- To get the proper connection string use this link. https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
- 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
- Enter the credentials and select the tables
- Create a report and publish in Power BI service
- Login to Power BI service and open settings of a report dataset
- Expand gateway connection and now we can see that gateway is enabled and it is connected local gateway
- Expand the data source credentials and enter the database credentials
- Now go to datasets and click on refresh icon of the dataset
- 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,
4 Comments
Ramin Maharjan
Thanks for the support. I've already done that it doesn't work. Do you mean installing npgsql service on the server thats hosting the data gateway or the actual server that holds the data? Currently I did on the one that is hosting the only data gateway(actual data contains on another server)
AmilaMGunawardana
Hi, You have to install the driver on the data gateway installed machine. And check that machine has access to the database server. (database connectivity)
AmilaMGunawardana
Hi Now i understood the issue. Please find the edited answer.
AmilaMGunawardana
Thank you for the reply. Can you please upvote my answer..
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
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.
1 Comment
Community
As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.