connecting from Powerbi workspace via powerbi network gateway adapter to aws rds - postgreSQL

[mnatarajan]

Hi, 

I want to connect to Powerbi premium Workspace to postgreSQL RDS hosted in aws. Having issue to connect to AWS PostgreSQL RDS from PowerBi Premium workspace.

Following steps were taken in windows aws ec2. 

• I installed in PowerBi network gateway adapter standard version in windows box. Installed and configured successfully, it shows online.
• Configured odbc connection to test aws postgreSQL rds and connection is successful
• installed pgadmin and connection is succesfull to PostgreSQL RDS. 
• PowerBi Adapter is visible in Powerbi premium workspace.
• Installed npgsql 4.0.17.0 as recommended by powerbi based on the below error message. (installed via the nuget command)
• Also copied npgsql.dll from npgsql folder to the network gateway adapter folder as recommended by other blog.

When creating a powerbi premium workspace - new connection to aws postgreSQL rds, via on-premises option. [since powerbi recommending on-premises option even though rds is in aws cloud] - note: there is cloud option but Azure recommends to use mainly for Azure fabric env. 

Passed the following information correctly: 

Connection String - aws rds end point, username and password and selected gateway adapter from the drop down, and tried all three different options individually like private, none, org and public, when the connection occurs, i am getting the following error:

Unable to create connection for the following reason: Unable to connect to the data source. Either the data source is inaccessible, a connection timeout occurred, or the data source credentials are invalid. Please verify the data source configuration and contact a data source administrator to troubleshoot this issue.

Please install Npgsql version 4.0.17.0 or earlier.

if any one faced similar issue like above, somehow, it shows npgsql 4.0.17.0 earlier even though the current npgsql version is 9.0. Please let me know.  I couldn't find any solution and tried all the options. 

Our goal is to use Network gateway adapter should be visible in powerbi workspace, so users can query the rds table and views to generate the report and refresh the report. 

Thanks in advance. 

[v-lgarikapat]

Hi @mnatarajan ,

Thanks for reaching out to the Microsoft fabric community forum.

@burakkaragoz ,

Thanks for your prompt response

@mnatarajan 

If your question has been answered, kindly mark the appropriate response as the Accepted Solution. This small step goes a long way in helping others with similar issues.

We appreciate your collaboration and support!

Best regards,
LakshmiNarayana

[v-lgarikapat]

Hi @mnatarajan ,

If your question has been answered, kindly mark the appropriate response as the Accepted Solution. This small step goes a long way in helping others with similar issues.

We appreciate your collaboration and support!

Best regards,
LakshmiNarayana

[burakkaragoz · Accepted solution]

Hi @mnatarajan ,

This issue usually comes down to how the Npgsql provider is registered on the machine where the gateway is running. Even if you’ve installed a newer version, Power BI Gateway might not be detecting it properly unless it's registered correctly in the GAC (Global Assembly Cache) or via the machine.config.

Here’s what you can try:

1. Install Npgsql via MSI installer, not just by copying DLLs manually. You can find the official MSI for Npgsql 4.0.17 on GitHub releases. Make sure to install it on the same machine where the gateway is running.

2. Register the provider in the machine.config file:

   • Navigate to:
     C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config
   • Add this inside the <DbProviderFactories> section:

    <add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql, Version=4.0.17.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>

   • Do the same for the 32-bit version if needed (Framework instead of Framework64).

3. Restart the gateway service after making these changes.

Also, avoid manually copying DLLs into Power BI folders — that usually causes more issues than it solves.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[mnatarajan]

Thank you for the quick response. Yes, it worked with a few tweaks done. 

1. I installed the npgsql 4.0.16 msi, i couldn't find the other MSI version like 4.0.10 etc
2. The default path for npgsql.dll is under "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_4.0.16.0__5d8b90d52f46fda7\Npgsql.dl
3. gautil runs only from developer command prompt via Visual Studio, i was not aware of this but figured it out. 

Rest everything works good as expected, the connection was successful from PBI desktop to Workspace, and report was published. 

[mnatarajan]

Also, i didn't do below step: 

Register the provider in the machine.config file:

• Navigate to:
  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config
• Add this inside the <DbProviderFactories> section

Instead above step:

used Gutil from Visual studio command prompt:

gacutil /i "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_4.0.16.0__5d8b90d52f46fda7\Npgsql.dll"

[v-lgarikapat]

Hi @mnatarajan,
Thank you for the confirmation. We're pleased to know your issue has been resolved. Kindly consider accepting the solution so it can assist other community members in finding it more easily

Best Regards,

Lakshmi Narayana