cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heoliv
Advocate I
Advocate I

Certificate Chain Not Trusted Error Effecting PowerBI Only

Hi,

 

In a SQL query that I have run several times before I am not receiving the following error: 

 

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

 

However, I am still able to access the databases that I receive this error for on PowerBI via SQL Server Management Studio. 

 

Does anyone have any insight onto why I would suddenly be running into this issue after not having issues with the databases effected before and how to resolve it?

1 ACCEPTED SOLUTION

This ended up being a fairly easy solution, in order to work around this issue I ended up just having to switch the connection type from MSFT account to Windows for any who encounter this issue in the future. 

View solution in original post

8 REPLIES 8
nicbottie-mon
Regular Visitor

So, we're getting this error (similar to above) when connecting to the Azure Analysis Services Tabular Model, from Power BI Desktop...

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.)\r\nStackTrace:\n   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionFactory

Where can one specify "Connection Properties" and choose "Trust server certificate" in Power BI Desktop?
Microsoft Support ticket guides us to that setting, like you can set in SSMS when Connecting to a SQL Server...

In SSMS...In SSMS...

RamyaKanaka
Microsoft
Microsoft

I am getting this certificate error while i am trying to connect to SQL OnPrem source. I tried to connect to the same soure through excel and it works but it getting error in PowerBBi. 

 

Any help please!

v-yuezhe-msft
Microsoft
Microsoft

@heoliv,

Make sure that the certificate used by the SQL Server is within the Trusted Root Certification Authorities store of the machine running the Power BI Desktop.


There are a similar thread and a blog for your reference.
http://community.powerbi.com/t5/Desktop/provider-SSL-provider-error-0-The-certificate-chain-was-issu...
https://powerbi.microsoft.com/en-us/blog/ssl-security-error-with-data-source/

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Lydia, I saw these threads but since the connection works in SQL Server Management Studio I thought it may be something else? If not how do I locate the Certificate Authority the database I am connecting to uses? Thanks for the insight.

@heoliv,

You would need to contact SQL Server database administrator about that where he stores the SSL certificate. Then import the certificate to the client computer that installs Power BI Desktop following the guide in the KB below.

https://support.microsoft.com/en-sg/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This ended up being a fairly easy solution, in order to work around this issue I ended up just having to switch the connection type from MSFT account to Windows for any who encounter this issue in the future. 

Hi Syndicate_Admin,

 

I am having the same problem, before testing this workaround what are the implications of doing this on users trying to access the report across the network?.

 

Also do you have any idea what the switching to Oauth2 would do?

This worked for me too, thanks!

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors