Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Hi!
I had similar issue and for me and @JMLyle solution worked.
Go to Control Panel > Edit environment variables for your account. Then add new and use this:
[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.
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 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.
We did the temporary workaround using a SQL user instead of Active Directory authentication.
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.
@Anonymous , refer if one of 2 can help
https://powerbi.microsoft.com/en-my/blog/ssl-security-error-with-data-source/
I haven't been able to get it to work with these links. First, I have restricted access and the second is throwing the same error.
User | Count |
---|---|
84 | |
76 | |
75 | |
43 | |
36 |
User | Count |
---|---|
109 | |
56 | |
52 | |
48 | |
43 |