(Report Server, May 2020) Postgres Direct Query Npgqsl

dstar — Thu, 13 Aug 2020 19:03:30 GMT

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?

Re: (Report Server, May 2020) Postgres Direct Query Npgqsl

amitchandak — Mon, 17 Aug 2020 07:37:23 GMT

dstar , have imported trusted certificate - Power BI Only support SSL enabled PostgreSQL

You might have to convert its format before importing

https://www.sonicwall.com/support/knowledge-base/how-can-i-import-certificates-into-the-ms-windows-local-machine-certificate-store/170504615105398/

https://docs.microsoft.com/en-us/skype-sdk/sdn/articles/installing-the-trusted-root-certificate

topic Re: (Report Server, May 2020) Postgres Direct Query Npgqsl in Report Server

(Report Server, May 2020) Postgres Direct Query Npgqsl

Re: (Report Server, May 2020) Postgres Direct Query Npgqsl