I'm unable to connect to my Postgres database using the gateway postgres datasource connector.
Gateway enterprise is installed
npgsql 3.2.6 is installed (server restarted)
postgres configured to allow external connections
The postgres database is setup on a VM hosted on Azure
I'm using DirectQuery
I see lots of post with similar issues but no complete guide. What details need passing to the server name? servername/ip + port? What firewall rules are required?
See below error:
Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."Hide details Activity ID: 1482f5c9-ab14-4e23-a961-db9684b9ba65 Request ID: f7f0cad3-7e4b-634b-eb47-8f205876e01b Cluster URI: Status code: 400 Error Code: DMTS_PublishDatasourceToClusterErrorCode Time: Sun Jun 07 2020 15:27:46 GMT+1000 (Australian Eastern Standard Time) Service version: 13.0.13456.212 Client version: 2005.4.01272-train auzre projectwise: Unable to connect to the data source undefined. Underlying error code: -2147467259 Underlying error message: Please install Npgsql version 126.96.36.199 or earlier. DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.ValueError.DataSourceKind: PostgreSQL Microsoft.Data.Mashup.ValueError.DataSourcePath: servername;datasource Microsoft.Data.Mashup.ValueError.Reason: DataSource.MissingClientLibrary
The 400 ERROR means a bad http request. Please updata on-premise gateway to the lastest version. If you already have updated the gateway to the latest version, then you can refer to steps below for troubleshooting.
1. Check if the server is using a proxy and the proxy. Some proxies restrict traffic to only ports 80 and 443. By default, communication to Azure Service Bus will occur on ports other than 443.
2.You can force the gateway to communicate with Azure Service Bus using HTTPS instead of direct TCP. You will need to modify the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file. Change the value from AutoDetect to Https. This file is located, by default, at C:\Program Files\On-premises data gateway.
3. Restart the gateway and check if the issue persists.