Setting Privacy Settings in a Shared Excel File with Database Connection

[Syndicate_Admin]

Hello Community,

I'm facing a challenge while using Power Query in conjunction with a shared Excel file. This file contains a database connection, and I'm trying to standardize privacy settings for all users.

The current situation is as follows:

• Privacy settings for the database connection are set to "Private."
• The Excel file is centrally provided for multiple users with read-only access.
• When opening and updating data via the connection, users are prompted for privacy settings.
• Since users only have read-only access to the file, they can't save the settings, potentially leading to different settings being used.
• we are using M365 Enterprise Office
• the database server is running onPrem
• We set the data protection setting in the centralized excel file. But every user has to set it again, until he saved the file afterwards, which is not our mandatory intention. 
• The PowerBI centralized data model would require, that all users (>400) have to have a user based licence. This effort is not covered by the business case in the next 5 years. 
• A SSRS centralized data model doesn't cover our requirements of passing parameters from excel to the data source.

My questions are:

• How can I set privacy settings for this Excel file so that they are uniform for all users and cannot be individually changed?
• My assumption, that the saved protection settings in the centrally stored excel file would be used for every user is not valid. But where are these settings stored? In the registry or user specific app data or ...? 

I appreciate your time and any assistance or guidance you can provide on this matter.

Best regards,

Chris

[Jaywant-Thorat]

Hi @chris10 @Syndicate_Admin 
This is a very common but poorly documented Power Query / Excel issue, and your assumptions are logical, unfortunately Power Query doesn’t behave the way most enterprise users expect.

(no Power BI licensing, Excel front-end, on-prem DB).

-> Upfront Answer

You cannot centrally enforce Power Query privacy levels via a shared Excel file.

• Privacy settings are not stored in the workbook in a reusable way.
• They are per-user, per-machine, and Power Query will always prompt a new user.
• This is by design, not misconfiguration.

Solution:
If your DB is trusted internally:
Excel setting (per user, once)
File → Options → Trust Center → Privacy →
☑ Ignore Privacy Levels and potentially improve performance

This:

• Removes prompts
• Stops inconsistent behavior
• Is acceptable in controlled enterprise networks

Enterprise deployment
You can enforce this via:

• Group Policy
• Office ADMX templates
• Registry push

This is how most large organizations solve this.
Tradeoff: Users could combine sources (but your model already assumes trust)

Power Query was designed assuming:

1. Personal data mashups
2. Not shared governed Excel artifacts
3. Power BI Semantic Models solve this, but licensing blocks you, which is unfortunately common.

=================================================================
Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
Linkedin: https://www.linkedin.com/in/jaywantthorat/
Join #MissionPowerBIBharat = https://shorturl.at/5ViW9
#MissionPowerBIBharat
LIVE with Jaywant Thorat from 10 Jan 2026
8 Days | 8 Sessions | 1 hr daily | 100% Free

[mohit_sakhare]

Hi Chris,

What you’re experiencing is actually by design in Power Query.

Power Query does not store privacy levels inside the Excel file. They are stored per user, per machine in the local Power Query credential and privacy store (within the user’s Windows profile). That’s why:

• Each user is prompted the first time

• Read-only users are prompted every time

• Saving the file does not carry the privacy settings to other users

This is a security boundary in Microsoft’s architecture — otherwise one user could weaken another user’s data-isolation rules.

So to answer your questions directly:

1) Can I force or lock privacy settings in a centrally stored Excel file?
No. There is no supported way to centrally enforce Power Query privacy levels via the workbook, SharePoint, or OneDrive.

2) Where are the settings stored?
They are stored locally per user in the Power Query credential/permission store (encrypted, under the user’s Windows profile and registry), not in the Excel file.

What you can do:
For enterprise scenarios like yours (on-prem DB, controlled network, many users), Microsoft provides a supported option to disable privacy checks via Excel Trust Center or Group Policy (“Ignore Privacy Levels”). This removes the prompts entirely and is the only scalable solution when you don’t want per-user configuration.

That’s exactly the scenario that setting was designed for.