Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

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

 

 

10 REPLIES 10
Johag
Advocate I
Advocate I

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: 

 

Johag_0-1747208376987.png

JMLyle
Helper I
Helper I

[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.

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 ? 

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.

pere_ramos
New Member

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

WalTig
Frequent Visitor

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
Super User
Super User

Anonymous
Not applicable

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.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.