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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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