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?
