Forum Discussion
Power BI Error from new SQL Server - certificate chain was issued by an authority the is not trusted
I have previously been able to connect from Power BI Desktop to a SQL Server 2008 R2 data source, but ever since this data was migrated to SQL Server 2019, I now get the “Unable to connect” error with the 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 the is not trusted.)
I have troubleshot things using SSMS on the new server to ensure that the login I use has proper permissions on the new server, database, and exec permissions on the stored procedure. All the data gets pulled inside SSMS, but when set up inside PBI Desktop, the error occurs.
Hi MarkSanchez,
So i digged the web for your issue and find some amazing stuff so could you please review these solutions & documents:
1-This is your issue (SSL Certificate Chain Not Trusted)
2-This also another solution For your Issue (certificate chain not trusted)
3-Import data from a database using native database query
4-SqlConnection.ConnectionString Property
5-Sql.Database
These Resources will explain better than me I hope this is helpful ☺️❤️
I recommend you the First and Second Solution 😅❤️if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
12 Replies
AMD0791Advocate IV
Make sure your login in PowerBI matches how you log in via SSMS, especially if you log in with your Windows credentials or database credentials.
In PowerBI desktop, TransformData > Data source Settings > Choose the connection and "Edit Permissions". Under credentials, make sure it's using the right type of credentials. Connections are often defaulted to "Microsoft account" where you'd log in with your email and go through the OAuth pathway like you're logging in to your cloud services.
If it's set up correctly, sometimes just re-doing the credentials setup in PowerBI will help clear things out and get them working.
Thanks for your response. I confirmed that my login for both PBI and SSMS do match.
I am aware of the "Edit Permissions" option but that only comes into play once you have connected and reruned a data set. In my case, nothing is returned.
I can literally enter a SQL statement in PBI like: select * from Employees where active = 1
I get an error when trying it from the new Server, but it works from the old Server.
- Ahmed-Elfeel
Super User
Hi MarkSanchez,
The Easiest way to fix this issue is to add TrustServerCertificate to Connection String
In Power BI Desktop when configuring the SQL Server connection:
- Click Edit instead of entering credentials directly
- Go to Advanced Options
- Add this parameter to the connection string:
;TrustServerCertificate=true- This bypasses certificate validation
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.Thanks for your response.
TrustServerCertificate=true
This looks like a connection string parameter. I actually had to add this parameter to the conn string of some .NET apps that required it and this was also when querying from the SAME SERVER, so you're on to something.
Are you saying there is a Connection String parameter WITHIN Power BI? I'm sorry but the steps above do not give me access to any such item. All I see is an advanced option for "Command timeout in seconds"
ThanksFor some context of where I am trying to do this:
v-ssriganeshCommunity Support
Hi MarkSanchez,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Ahmed-Elfeel & AMD0791 for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
- v-ssriganesh
Hello MarkSanchez,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.
- v-ssriganesh
Hello MarkSanchez,
Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.
Thank you.
