PostgreSQL database refresh from private subnet

[audian_ben]

Hello,

Just setting up a new power bi report for my company and have started gathering data from our PostgreSQL server which is on a private subnet that is only accessible from the company intranet.  I have put together a few simple queries and a couple of simple reports to get things started to make sure all is working.  As I expected, once I get it uploaded to the app.powerbi cloud, it is unable to authenticate.  But the setting for the datasets indicates that I cannot use an on-prem gateway.  I have the on-prem (personal) gateway installed on my workstation, logged in and configured, but it still will not allow me to turn it on.  Any advice?

Thank you,

Ben

[v-lid-msft]

Hi @audian_ben ,

Based on my test, we can only use the On-Premises Data Gateway (Standard mode) for Postgres SQL Server currently . Please try to use an standard mode gateway to access the private postgresql server.

Best regards,

[audian_ben]

Great, that partially helps.  Now I am trying to attach it to my data source and getting the following after having to install npgsql provider:

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: 3b0decad-01fb-4f93-8256-7f81f0334de9
Request ID: bce2f2e3-566b-03a3-e1f3-8bf67770e313
Cluster URI: https://wabi-west-us-redirect.analysis.windows.net/
Status code: 400

Error Code: DMTS_PublishDatasourceToClusterErrorCode
Time: Fri Jan 24 2020 12:30:44 GMT-0800 (Pacific Standard Time)
Service version: 13.0.11888.332
Client version: 1912.4.031
***************: Unable to connect to the data source undefined.
Underlying error code: -2147467259
Underlying error message: An error happened while reading data from the provider: 'Could not load file or assembly 'System.Numerics.Vectors, Version=4.1.3.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.'
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind: PostgreSQL
Microsoft.Data.Mashup.ValueError.DataSourcePath: **********************************
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error

We have tried installing nuget and installed the System.Numberics,Vectors 4.1.0 and 4.5 into the GCS, and still get the same error above.

[audian_ben]

Was finally able to get past the System.Numerics.Vectors.  Needed to install the windows sdk to be able to run gacutil to install the dll into the Global Assembly cache.  Now I am getting the following:

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. 
Activity ID: 3b0decad-01fb-4f93-8256-7f81f0334de9
Request ID: 1a30c31f-b191-0bff-774b-705d8d632675
Cluster URI: https://wabi-west-us-redirect.analysis.windows.net/
Status code: 400
Error Code: DMTS_PublishDatasourceToClusterErrorCode
Time: Tue Jan 28 2020 13:33:39 GMT-0800 (Pacific Standard Time)
Service version: 13.0.12023.283
Client version: 2001.1.013
Ben-desktop: Unable to connect to the data source undefined.
Underlying error code: -2147467259
Underlying error message: An error happened while reading data from the provider: 'The type initializer for 'Npgsql.TypeMapping.GlobalTypeMapper' threw an exception.'
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind: PostgreSQL
Microsoft.Data.Mashup.ValueError.DataSourcePath: db01.******.com:5432;odoo
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error

I have tried to install the latest ngpsql from nuget and load into the gac using gacutil and still get the same error.  Even after restarting the on-prem gateway.