Connect PowerBI to SQL Server - TrustServerCertificate

[Anonymous]

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

[Johag]

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: 

[JMLyle]

[System.Environment]::SetEnvironmentVariable('PBI_SQL_TRUSTED_SERVERS','<servername>', 'User')

[JMLyle]

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.

[tralala2]

Hello! I have the same issue as the OP. I tried going to Control Panel, Edit Environmnet Variables, as @Johag mentioned. It didn't work, I still cannot open my SQL Server tables in PBI. Where did you input this code, @JMLyle ? 

[tralala2]

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!

🍻

[OVerplancke]

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.

[pere_ramos]

We did the temporary workaround using a SQL user instead of Active Directory authentication.

[WalTig]

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.

[amitchandak]

@Anonymous , refer if one of 2 can help

https://powerbi.microsoft.com/en-my/blog/ssl-security-error-with-data-source/

https://community.powerbi.com/t5/Power-Query/Certificate-Chain-Not-Trusted-Error-Effecting-PowerBI-Only/td-p/359157

[Anonymous]

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.