Connecting aws rds postgreSQL via PowerBi Network Gateway Adapter from PowerBI Premium workspace

[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-sdhruv · Accepted solution]

Hi @mnatarajan ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[mnatarajan · Accepted solution]

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. Only thing, i noticed after publishing the reports from powerbi desktop to workspace, next to the semantic model it shows: Processing error: We are unable to access some data source because the artifact is missing connection details. Please contact the artifact owner to bind the data source to a data connection or use default connection settings for the unbound data source. with the cluster uri, activity id etc. Another thing, if we open the report using the option like analyze report in excel, there it shows an error like data source unreachable, there is no gateway to access to data source, but gateway connection was successful both in gateway adapter and in workspace configuration using db server, credentials etc. Please let me know if you have any additional information for this error.

[mnatarajan · Accepted solution]

1. 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. Used gautil command to register the GAC
4. gacutil /i " C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_4.0.16.0__5d8b90d52f46fda7\Npgsql.dl’”
5. gautil runs only from developer command prompt via Visual Studio, i was not aware of this but figured it out. 
6. Copied npgsql.dll to the gateway directory manually.
7. With the above steps, it worked and gateway can connect to the rds and workspace configuration can connect to the rds.
8. Rest everything works good as expected, the connection was successful from PBI desktop to Workspace, and report was published. Only thing, i noticed after publishing the reports from powerbi desktop to workspace, next to the semantic model it shows: Processing error: We are unable to access some data source because the artifact is missing connection details. Please contact the artifact owner to bind the data source to a data connection or use default connection settings for the unbound data source. with the cluster uri, activity id etc. Another thing, if we open the report using the option like analyze report in excel, there it shows an error like data source unreachable, there is no gateway to access to data source, but gateway connection was successful both in gateway adapter and in workspace configuration using db server, credentials etc. Please let me know if you have any additional information for this error.

[mnatarajan · Accepted solution]

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. Only thing, i noticed after publishing the reports from powerbi desktop to workspace, next to the semantic model it shows: Processing error: We are unable to access some data source because the artifact is missing connection details. Please contact the artifact owner to bind the data source to a data connection or use default connection settings for the unbound data source. with the cluster uri, activity id etc. Another thing, if we open the report using the option like analyze report in excel, there it shows an error like data source unreachable, there is no gateway to access to data source, but gateway connection was successful both in gateway adapter and in workspace configuration using db server, credentials etc. Please let me know if you have any additional information for this error.

[v-sdhruv · Accepted solution]

Hi @mnatarajan ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @mnatarajan ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi  @mnatarajan ,

You can follow these steps to troubleshoot-
1.  Clean Npgsql Installation-

Power BI only supports Npgsql 4.0.10–4.0.17. 



• Uninstall all Npgsql versions (nuget uninstall, remove from GAC)

• Manually install Npgsql 4.0.17 MSI installer

• Install it on the same EC2 instance as the Gateway

• Reboot the machine

  Check if it appears in:

  C:\Program Files (x86)\Npgsql\4.0.17\lib\net45\Npgsql.dll

  Copy this file to:

  C:\Program Files\On-premises data gateway\

  (Or wherever the gateway is installed)
  
  2. Register Npgsql in GAC (Global Assembly Cache)

  Open a Developer Command Prompt (as Admin):

  gacutil /i "C:\Program Files (x86)\Npgsql\4.0.17\lib\net45\Npgsql.dll"

  3. Run Gateway Service with User that Can See Npgsql

  Ensure Power BI Gateway Service is running under a Local Admin account or a service account that has access to Npgsql.
  Go to Power BI Service > Manage Gateways

Select your registered gateway and then Add data source

Additionally you can follow these links-

https://community.fabric.microsoft.com/t5/Desktop/Connect-Desktop-Power-BI-to-AWS-RDS-postgreSQL/td-...

https://docs.aws.amazon.com/whitepapers/latest/using-power-bi-with-aws-cloud/connecting-the-microsof...

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!