Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
dstar
New Member

(Report Server, May 2020) Postgres Direct Query Npgqsl

I am trying to create a report on Power BI Report Server (on-prem) which connects to a PostgreSQL database through Direct Query.

 

According to this page, PostgreSQL is now listed as "DirectQuery / Live Connection": Yes

https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources

 

However, it may be that the above link revers only to the Desktop version?

"Power BI report data sources in Power BI Report Server" still lists PostgreSQL as "Live/DirectQuery": No

https://docs.microsoft.com/en-us/power-bi/report-server/data-sources

 

Here is what I have tried so far:

I created a report in "Power BI Desktop (Optimized for Power BI Report Server - May 2020)", basically following the recipe on this page for setting up Row-level-security:

https://docs.microsoft.com/en-us/power-bi/report-server/row-level-security-report-server

I setup a User role which would filter data by looking up "[username] = userprincipalname()" against a user table in my postgres database (where username is defined according to the format "uid@domain.com" used by the userprincipalname() DAX function)

 

I uploaded the report, clicked on the "..." button --> "Data sources", and I could see that it copied the database connection string and credentials that I had already entered in the desktop version (Connection Type: Postgresl, Authentication Type: Basic Authentication). 

However, when I clicked "Test Connection" I got an error:

"Coudn't connect: Please install Npgsql version 4.0.10 or earlier"

 

I went to install Npgsql according to the documentation here:

https://www.npgsql.org/doc/installation.html

I first tried installing using Nuget:

https://www.nuget.org/packages/Npgsql/4.0.10

but the "Install-Package" cmdlet was not recognized in PowerShell (I do not have Visual Studio installed on the server).

I went ahead and upgraded Windows Management Framework to version 5.1:

https://www.microsoft.com/en-us/download/details.aspx?id=54616

However, I still could not get the "nuget" (aka "Install-Package") installer to work, so in the end I just used the ".msi" installer on Github:

https://github.com/npgsql/npgsql/releases/tag/v4.0.10

I made sure to select the "GAC Installation"

 

After completing the installation and restarting the server, I was able to run "... --> Data Sources --> Test Connection" successfully in the Report Server web interface!

However, when I tried to view the report I got a new error:

"An unexpected error occurred: We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly."

 

Now I don't know what is going on.  I am not trying to connect through Analysis Services, so the error does not make sense to me. 

Any ideas?

 

Am I just out of luck because technically Postgres is not yet supported for DirectQuery in Report Server (even though it is now supported in Power BI Desktop)? 

Do I have to start over and try again by setting up a Direct Query ODBC connector?

https://github.com/microsoft/DataConnectors/blob/master/docs/odbc.md

Do I have to just migrate all of my data to SQL Server?

 

 

1 REPLY 1
amitchandak
Super User
Super User

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.