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
