Forum Discussion
Dataflow Connect to AWS Postgresql
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.
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.
14 Replies
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=04iw9SYI2HN80HKSThank 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.
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.
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.
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.
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.
