Setting Privacy Settings in a Shared Excel File with Database Connection

[chris_Stei]

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 Excel as part of Microsoft 365 Apps for Enterprise
• the database is on a onPrem server instance
• the web application is hosted in the IIS on this database server

My question is:

How can I set privacy settings for this Excel file so that they are uniform for all users and cannot be individually changed?

I would greatly appreciate any assistance or guidance you can provide on this matter.

Best regards,
Chris

[rubayatyasmin]

Hi, @chris_Stei 

Suggest your users to select "Ignore Privacy Level Checks for this file" when the window pops up. 

Also, check the article

Set privacy levels (Power Query) - Microsoft Support

Also, a few suggestion

1. You can leverage the Group Policy to standardize user settings.

2. How about a centralized data model? This can be done using Power BI or a similar tool where the data is imported and transformed centrally. Then the Excel file connects to this centralized model rather than directly to the database. This approach minimizes the need for users to have their privacy settings configured.

3. For On-prep use need to use a data gateway. This way, all users can connect through a unified gateway which has its privacy level settings controlled centrally.

[chris_Stei]

Hi rubayatyasmin,
thank you for your suggestion. They might work in different scenarios, but unfortunately not in our case. 
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.
Best regards
Chris