Forum Discussion
Connect PowerBI to SQL Server - TrustServerCertificate
Hi there,
I have a problem with connecting my PowerBI desktop to my SQL server. I know 100% the problem lies with the 'TrustServerCertificate' attribute.
When logging into to SSMS, or creating and ODBC data source, I need to ensure TrustServerCertificate is true. However, when using the SQL server connector (and the Azure SQL Server connector) in PowerBI desktop, there is no option for it. I have tried writing SQL statement to change this, as that's the only option I have but none have worked.
I have tried
TrustServerCertificate=TRUE;
TrustServerCertificate=Yes;
With all different versions of capitals, semi-colons etc.
Am I missing something? What statement can I use to get PowerBI to TrustServerCertificate?
Apologies if I posted this in the wrong place.
Thank you
14 Replies
[System.Environment]::SetEnvironmentVariable('PBI_SQL_TRUSTED_SERVERS','<servername>', 'User')Sorry this last post was so brief and cryptic, but I'm glad some people were able to interpret it and get it to work.
This is a PowerShell command to set an Environment Varible. In this case, it is setting a User Environment Variable instead of a System Environment Variable ("User" is not refering to a specific user account name, it is simply "user" as opposed to "system"). System Environment Variables generaly need a high lever of access to set, but most of the time, you should be able to set a User Environment Variable for yourself.
The variable name is "PBI_SQL_TRUSTED_SERVERS."
Open a PowerShell console window, and run this:[System.Environment]::SetEnvironmentVariable('PBI_SQL_TRUSTED_SERVERS','<servername>', 'User')Where <servername> is the name of the SQL server.
You should also be able to use wildcards (*) with domain names, like "*.example.com" which would match any servers that have a Fully Qualified Domain Name that ends in .example.com:[System.Environment]::SetEnvironmentVariable('PBI_SQL_TRUSTED_SERVERS','*.example.com', 'User')
And as tralala2 points out, you should probably reboot after setting the environment variable.This worked for me. Thanks!
This solution worked for me.
PowerBI (much like Fabric), when using custom certificates for your internal sql servers, doesn't have the option to say "Trust Server Certificate" like you can in a traditional connectionstring.
NEVERMIND, it worked. It just needed a Restart before trying again. So use the method written by the 2 users above, but restart your computer before retrying!
🍻
This simply worked for me. Thank you
Sometimes you have to search in another direction. I read a lot about loading service certificate and/or setting "TrustServerCertificate" to true.
However, in our case (and it sounds more cases are like this) from one day to another the user (and only "one user") was not able to connect to SQL Server from power BI anymore. Nothing had changed in our environment. Solving the issue by some certificate did not sound right.
Try this (it solved our issue): make sure the user has the latest version of Power BI Desktop installed.
In Sep 2023 the user was still working with version May 2023. Just installing the newest version of Power BI Desktop enabled him to connect to SQL data again.We did the temporary workaround using a SQL user instead of Active Directory authentication.
In Power BI Desktop, this issue happens because the connector doesn't always trust the server's SSL Certificate. The easiest fix is to add the setting in the Advanced options of the connection window so it accepts the certificate. If that still fails, the proper way is to make sure your SQL server has a valid certificate chain installed on Power BI can trust it without extra settings. If you're not comfortable handling certificates, your IT/DBA team can set this up for you. Hope it helps!
In Power BI Desktop, this issue happens because the connector doesn't always trust the server's SSL Certificate. The easiest fix is to add the setting in the Advanced options of the connection window so it accepts the certificate. If that still fails, the proper way is to make sure your SQL server has a valid certificate chain installed on Power BI can trust it without extra settings. If you're not comfortable handling certificates, your IT/DBA team can set this up for you. Hope it helps!
