Forum Discussion

audian_ben's avatar
audian_ben
Frequent Visitor
6 years ago

PostgreSQL database refresh from private subnet

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

 
Dataflow
Need Help

3 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      audian_ben
      Frequent Visitor
      6 years ago

      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's avatar
        audian_ben
        Frequent Visitor
        6 years ago

        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.