EMERGENCY || Unable to connect SQL server with PBI

[PremK]

Kindly could you assist me in connecting SQL Server with PBI.

Pioneer Rx ( a pharmacy software ) which uses SQL Server as Data base. i have to connect this with PBI for reporting.

I had connected this SQL Server with SSMS and its connected well and able to interact with the data bases and tables in it.

But while i tried to connect the same SQL Server with Power BI to create reports its showing error like 

provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.

I had tried with these Certificate Revocation options in PBI Desktop Settings, but it doesn't help me out 

kindly provide me solution to Connect SQL Server and PBI...

[PremK · Accepted solution]

Thanks to everyone who tryied to give solution for this error.
I resolved this issue through 2 steps > 
1. Adding / Editing Environment Variable  - the variable name should be Name of the SQL Server.
2. Updating PBI Desktop Config file in this path
C:\Users\<YourUsername>\AppData\Local\Microsoft\Power BI Desktop\User.zip
updating this - <setting name="SqlTrustedServers" serializeAs="String"> <value>NAMES OF YOUR SQL SERVERS </value> </setting>

[Anonymous]

Your solution is great @collinq 

Hi, @PremK 

Has your current issue been resolved? If yes, you can share your workaround here and mark it as a solution or mark the reply that was helpful to you as a solution.

When we connect to SQL Server with Windows authentication in Power BI desktop, the following dialog box pops up, and we need to select OK:

In addition, you need to change the settings to basic or none:

You can get this information from the following links:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-certificate-revocation

Also, if you keep getting the current error, then you can clear out your SQL Server credentials in the data source settings to reconnect to it:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[collinq]

Hi @PremK ,

This chain goes through a solution for this exact problem.  Let us know if this resolved it:
Solved: Re: Certificate Chain Not Trusted Error Effecting ... - Page 2 - Power Platform Community (m...