Unable to connect SQL Server Database with Power Query

[aAmirkHan]

Hi,

I am using SQL Server Express 2022 version. When I try to connect my SQL Database with Power Query, it shows me this error.

Details: "Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)"

Can anyone please advise how to fix this error?

@amitchandak 

@Ibendlin

Thank you

[v-kpoloju-msft · Accepted solution]

Hi @aAmirkHan,

Thank you for reaching out to the Microsoft Fabric community Forum.

After reviewing the details regarding the issue of connecting the SQL Server Database with Power Query, please refer to the steps below to address this issue.

• Verify that the SQL Server instance has a valid SSL certificate installed. You can verify this by connecting to the SQL Server instance using SQL Server Management Studio.
• If the certificate is valid but not trusted by the client, you can configure Power Query to trust the server certificate by setting the TrustServerCertificate parameter to True in your connection string.
• Set up an environment variable named pbi_sql_trusted_servers with the name of your SQL Server. Once that is done, please restart Power Query.
• Consider switching the connection type from a Microsoft account to a Windows account, as this can sometimes resolve the issue.
• If you are operating in a less sensitive environment and wish to bypass SSL certificate validation, please add Encrypt=False to your connection string explicitly.

After making these changes, restart the SQL server and power query services to ensure the changes take effect.

Please go through the below documentation links for better understanding:

Power Query SQL Server connector - Power Query | Microsoft Learn

Connections and authentication in Power Query Online - Power Query | Microsoft Learn

If you have any further queries, please feel free to reach out to us we will guide you accordingly. If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[v-kpoloju-msft]

Hi @aAmirkHan,

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.

[lbendlin]

On-premises data gateway November 2024 release | Microsoft Power BI Blog | Microsoft Power BI

To troubleshoot this error when using Power BI Desktop, modify the value of the environment variable PBI_SQL_TRUSTED_SERVERS to include the SQL Servers. The supported values are the same as outlined for gateway configuration (as described in step 4 above).