Dataflow Connect to AWS Postgresql

[yanain9]

Hi, I have trouble while connecting AWS Postgresql via the Dataflow and showing the following error message.

DataSource.Error: An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'DetailsReason = DataSource.Error
ErrorCode = 10434
DataSourceKind = PostgreSQL
DataSourcePath = << host >>; << Database Name >>

I also had the same error when I connect via Power Query, Then I added Certificate to my local laptop and the Postgresql can be connected successfully from laptop.

Actually, I would like to know how can I connect to AWS Postgresql from the Dataflow without the Power BI Gateway.

How can I add certificate in Power BI Service if possible?

What can I do in AWS side not to be required certificate?

If someone know the solutions, please help me.

Thank you.

[v-karpurapud]

Hi @yanain9 
Thanks for reaching out to the Microsoft Fabric Community Forum.

Currently, Power BI does not support direct manual certificate installation on the Power BI Service where Dataflow runs. Unlike the desktop version where it is possible to manually import certificates, the Power BI Service is a cloud-based service and cannot handle custom certificates directly.

1.Verify AWS PostgreSQL SSL Configuration
On the AWS side, ensure that the PostgreSQL instance is using a valid SSL certificate signed by a well-known Certificate Authority (CA). This step is important because the SSL certificate validation error often occurs if the PostgreSQL server isn’t correctly configured to handle secure connections.
AWS provides SSL certificates for RDS instances.
2. Download the RDS SSL certificate:

• Go to the AWS documentation on RDS SSL Certificates.
• Download the RDS root certificate (e.g., rds-combined-ca-bundle.pem) which is signed by Amazon and trusted by most systems.

3.Configure PostgreSQL Instance to Require SSL:

• In the RDS console, ensure that the parameter rds.force_ssl is set to 1 (enabled) so that only SSL connections are accepted.
• Restart the instance after applying this setting to ensure it takes effect.
  
  

4. After making these changes, test the connection from Power BI to ensure it connects successfully without SSL errors.

If you have any further questions or need additional help with this, feel free to reach out     to us for further assistance!

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[yanain9]

Thank you for the answer. @v-karpurapud.

I just would like to clarify that

• is this a solution that does't require a gateway to connect via Power BI Service?
• the steps that you have mentioned have to be configured in the AWS side? No need to configure in Power BI side?

Thanks and best regards.

[yanain9]

Thank you for the answer. @v-karpurapud.

I just would like to clarify that

• is this a solution that does't require a gateway to connect via Power BI Service?
• the steps that you have mentioned have to be configured in the AWS side? No need to configure in Power BI side?

Thanks and best regards.

[v-karpurapud]

Hi @yanain9 
Hope You are doing well!

Thanks for reaching out to the Microsoft Fabric Community Forum.

1. Yes, it  aims to establish a direct connection from Power BI Service to an AWS PostgreSQL database using Dataflows, without the need for a Power BI Gateway. By ensuring that the AWS PostgreSQL instance is configured with a trusted SSL certificate, the connection can be securely validated, allowing Power BI Service to connect seamlessly.

2. Yes, the main setup changes need to happen on the AWS PostgreSQL side. Since Power BI Service doesn’t allow manually installing custom SSL certificates, the PostgreSQL instance must use a trusted certificate from a recognized Certificate Authority (CA).This ensures compatibility with Power BI Service's SSL validation process.

If you have any questions or need further assistance, feel free to reach out to us.

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[yanain9]

Thank you for the answer and I will try according to your instruction. @v-karpurapud.

[v-karpurapud]

Hi @yanain9 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[yanain9]

Hi @v-karpurapud , 

I cannot solve with the above solution for now and there is still error. I am keep trying on it.

Thank you.

[v-karpurapud]

Hi  @yanain9 

We understand that the issue with Dataflow Connect to AWS PostgreSQL still persists. We kindly request you to raise a Microsoft Support Ticket.

Please refer below link on how to raise a contact support or support ticket.
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you.

[yanain9]

Thank you for your instructions. @v-karpurapud 

[v-karpurapud]

Hi @yanain9 
Hope you are doing well!

If your issue has been resolved, kindly mark the most helpful reply as the accepted solution.

This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @yanain9 

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[yanain9 · Accepted solution]

Hi @v-karpurapud ,

Thank you for following up on this.

As we cannot find a way to resolve it, we chose the option to use the gateway for now.

Thank you.

[Poojara_D12]

Hi @yanain9 

• Trusted Certificate: Use a trusted SSL certificate on AWS PostgreSQL. Power BI Service will accept it without additional setup.
• sslmode=require: Update your connection string to include sslmode=require to bypass certificate validation while still encrypting the connection:

Host=mydb.host.amazonaws.com;Database=mydb;Username=myuser;Password=mypassword;sslmode=require
​

• Non-SSL Option (Not Recommended): Configure PostgreSQL to allow non-SSL connections by modifying pg_hba.conf, but this is insecure and not suitable for production.

• No Certificate Upload in Power BI Service: Power BI Service doesn’t support custom certificates; rely on a trusted certificate or the sslmode=require option.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[yanain9]

Thank you for the answer. @Poojara_D12 .

I am connecting to AWS Postgresql using Native Query as below.

Value.NativeQuery(PostgreSQL.Database("host", "db_name"), "select * from table", null, [EnableFolding=true])

In that case, how can I update my connection string?

Also, when I get data from Postgresql, there is no box to put connection string.

Could you please provide how to update connection string using NativeQuery.

Thanks and best regards.