Question

Why am I getting an "SSL Certificate Chain Not Trusted" error when connecting SQL Server to Power BI, and how do I resolve it?
When connecting SQL Server to Power BI, you may encounter an "SSL Certificate Chain Not Trusted" error due to issues with the certificate authority (CA), an expired or self-signed certificate, or misconfigured encryption settings. How can I diagnose the root cause of this error and resolve it? What steps should I take to install or update the necessary certificates, configure trust settings, or modify encryption requirements to establish a secure connection?

Answer 1

The "SSL Certificate Chain Not Trusted" error when connecting SQL Server to Power BI typically occurs due to:

1. Untrusted or Expired SSL Certificate – Power BI may reject self-signed or expired certificates because they are not issued by a trusted Certificate Authority (CA).
2. Incomplete Certificate Chain – The intermediate certificates required to establish trust may be missing on the SQL Server or the client machine.
3. Encryption and Trust Settings – The connection settings may require SSL/TLS encryption, but the certificate is either not configured properly or not recognized.

How to Fix It

1. Verify the SSL Certificate

• Open SQL Server Configuration Manager → Navigate to SQL Server Network Configuration → Check if a valid SSL certificate is assigned.
• Use the command:

openssl s_client -connect your_sql_server:1433 -showcerts

• This helps check certificate details, including the issuing authority and expiration date.

2. Install the Trusted Certificate

• If the certificate is self-signed, install it on the client machine’s Trusted Root Certification Authorities store.
• If using a CA-signed certificate, ensure the intermediate CA is also installed.

3. Modify Power BI Connection Settings

• In Power BI Desktop, while connecting to SQL Server, select Advanced Options → Set Encrypt Connection = False if encryption is not required.
• If encryption is necessary, ensure Trust Server Certificate = True to bypass certificate validation issues.

4. Update SQL Server Settings

• Ensure SQL Server is configured to support TLS 1.2 or later for secure connections.
• If using Azure SQL, check that firewall and security settings allow certificate validation.