(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 "[email protected]" 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?Power BI refresh fails 90% of the time with postgres ODBC
We are having refresh errors 90% of the time for our reports. Repeatedly refreshing it manually might solve the problem. There are no problems refreshing via powerbi desktop app on the report server. Driver={PostgreSQL ANSI(x64)}; Server=xx.xx.xx.xx; Port=5432; Database=xxx; Connection Timeout=500; SessionID: 0622896a-4188-495f-9aef-57291a069a69 [0] -1055784932: The 'Driver' property with value '{PostgreSQL ANSI(x64)}' doesn't correspond to an installed ODBC driver.. The exception was raised by the IDbCommand interface. [1] -1055129594: The current operation was cancelled because another operation in the transaction failed. [2] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.
Connecting to Postgres in Report Builder
Hi, I'm trying to connect to a postgres data source through Power BI Report Builder - I assumed that I would have access to an ODBC data source setup, as per SSRS. Is this available in Power BI Report Builder or is there some kind of work around to allow me to connect to Postgres. The ODBC source is configured correctly in the Windows ODBC data sources The connection works in Power BI desktop using the Postgres data source option, however I now need to start building out some static reports using Report Builder Thank youPostgreSQL- Publish Error
I'm having an issue around published dashboard that connect to PostgreSQL that do not work. From my Power BI desktop, I can connect to PostgreSQL and build a (direct query) viz with no issues. I can even publish and get the "success" window. However, when I go to view the published dashboard, I get an access error about credentials. I have not modified anything on the PostgreSQL/ AWS side, but I'm wondering if there is something outside of Power BI that needs to be modified (maybe ralated to SSL)? Thank you. Thank you in advancedAutoUpdate PostgreSQL dataset Not Working
Hello everyone, I have a question abount the dataset autoupdate. We have some datasets connected directly to a postgresql database locatted in a rds of Amazon. If I try to update those dataset manually I have not got any inconvenients but the autoupdate is not working. This is the error that shows me, Error del origen de datos An error happened while reading data from the provider: 'Npgsql.Tls.ClientAlertException: CertificateUnknown: Server certificate was not accepted. Chain status: A certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider. . at Npgsql.Tls.TlsClientStream.SendAlertFatal(AlertDescription description, String message) at Npgsql.Tls.TlsClientStream.ParseCertificateMessage(Byte[] buf, Int32& pos) at Npgsql.Tls.TlsClientStream.TraverseHandshakeMessages() at Npgsql.Tls.TlsClientStream.<GetInitialHandshakeMessages>d__43.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.Tls.TlsClientStream.<PerformInitialHandshake>d__72.MoveNext()'. The exception was raised by the IDbCommand interface. Table: calendar. Does anyone knows how to resolve this? Thanks in advanced
Cannot connect on-premise gateway with postgres on PowerBI
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.
