Error message when refreshing the data in power bi desktop

[SHill83]

Hello,

i need help with refreshing the data in power bi, i am suddenly getting this message:

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.)

i have tried updating the datagateway config file as per microsofts help pages & i have restarted the gateway. i have downloaded the latest version of the gateway too. This issue is stopping people accessing the data & the automated refreshes have also stopped in the workspaces as well so any help is much appreciated.

Many Thanks,

[123abc]

Given community solved issue may help you.

Solved: Certificate Chain Not Trusted Error Effecting Powe... - Microsoft Fabric Community

SSL Security Error with Data Source | Microsoft Power BI Blog | Microsoft Power BI

Solved: Refresh Issues PowerBI SQL database- Connection wa... - Microsoft Fabric Community

Troubleshoot scheduled refresh for Azure SQL databases - Power BI | Microsoft Learn

Power Query SQL Server connector - Power Query | Microsoft Learn

OR follow below steps:

1. Update Power BI Desktop: Ensure you have the latest version of Power BI Desktop installed. Sometimes, simply updating the software can resolve connectivity issues1.

2. Check the Trusted Root Certificate: Verify that the certificate used by SQL Server is in the Trusted Root Certification Authorities store on the machine running the Data Management Gateway. You can use the Microsoft Management Console (MMC) to do this2.

3. Switch Connection Type: Try switching the connection type from your Microsoft account to Windows authentication. This has helped others overcome similar SSL-related errors1.

4. Import the SSL Certificate:

   • Export the certificate from the server where SQL Server is installed.
   • Import the certificate into the Trusted Root Certification Authorities store on the client machine w...1.

5. Environment Variable: Add the environment variable PBI_SQL_TRUSTED_SERVERS with the problem server name. This can sometimes resolve the issue1.

6. Uncheck Encrypt Connection: Go to Home Tab -> Transform Data -> Data Source Connections, then edit p...3.

[SHill83]

@123abc thank you so much for your suggestions - i have now sorted the issue. For some reason it wasn't allowing the standard database connection so i set up a new SQL user that i could use for the database connection and it worked! Thanks for your help!

[Jai-Rathinavel]

@SHill83 glad your sorted the issue, please accept our post as a solution as that others who are facing the same issue will refer this topic. Thanks

[Anonymous]

Hi @SHill83 ,

Thank you, @Jai-Rathinavel  , for sharing the link.

@SHill83 ,If the content in the link helped you, please consider marking it as a solution. This will greatly assist other users facing similar issues.However, if the issue is not resolved, please let us know so we can assist further.

Best Regards,

Bof

[SHill83 · Accepted solution]

@Jai-Rathinavel  & @Anonymous thank you so much for your suggestions - i have now sorted the issue. For some reason it wasn't allowing the standard database connection i was previously using, so i set up a new SQL user that i could use for the database connection and it worked! Thanks so much for your help!

[SHill83]

Hello I am very sorry about the delay in getting back regarding the advice given by Jai (Thank you Jai for your help!) but unfortunately i don't think this is the issue (after having spoken with the relevant people in IT). I am going to try something else out today, so i will let you know if this resolves the issue.

Many Thanks,

Sacha

[Jai-Rathinavel]

Hi @Anonymous , FYI. SHill83 posted the question I replied him back with a solution link. Thanks

[Jai-Rathinavel]

Hi SHill83,

"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.) (.Net SqlClient Data Provider)"

The exact error has been documented under Microsoft's Learn page. Please go through the below link and see if that helps.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/error-message-when-you-co... 

Thanks,

Jai