Separate reports from refresh
A controller works in Access with read and write rights, analyzes in Excel and Power BI Desktop, and has a Power BI report that refreshes itself every night. Each of these paths should get only what it needs and be switchable off on its own.
Solution overview
The service can't tell Excel and Power BI Desktop apart: both send the same identifier through Power Query (measured). What can be told apart is the access you give each path. One key per purpose separates what isn't recognizable from the program.
| Path | Access | Code | Why |
|---|---|---|---|
| Access through the ODBC driver | Windows account or personal key, the person's rights | yes, from outside | The driver asks for the code in a dialog |
| Excel, Power BI Desktop (interactive) | separate key “Analysis”: read-only, one alias, time-limited, totpExempt | no | Power Query can't ask for a code; totpExempt is allowed only for read-only keys |
| Power BI service through the on-premises data gateway (scheduled refresh) | separate key “Refresh”: read-only, only the report's views, totpExempt, bound to the data gateway's network | no | Requests come from the data gateway machine with the access stored there, not from the person viewing |
Implementation
- A rule for reports with the views reports need, read-only, assigned to the person; see rights per object.
Key “Analysis” with
issue-key:{ "person": "Clara Controlling", "label": "Excel/Power BI analysis", "expiresAt": "2027-03-31T00:00:00Z", "readOnly": true, "totpExempt": true, "databases": [ "fachdaten" ], "delivery": "link" }Key “Refresh” for the data gateway, bound to its address:
{ "person": "Clara Controlling", "label": "Power BI refresh", "expiresAt": "2027-03-31T00:00:00Z", "readOnly": true, "totpExempt": true, "databases": [ "fachdaten" ], "from": [ "10.1.5.20/32" ], "delivery": "link" }In the Power BI service, enter it for the data gateway's data source as Basic with user name
pkuser.- For presentations, an on-demand key (
"onDemand": true): it rests until the administration activates it for a few hours (activate-key); see on-demand keys. - Check: In
PersonalKeysevery key shows “last used” with its origin; the refresh key appears only with the data gateway's address. The service answers a request with it from elsewhere with403“… gilt nicht für diese Herkunft” (not valid for this origin).
Result: Three accesses, each with its own label, term, and origin in the log. If the nightly refresh ends, you revoke its key without touching Excel or Access.
Notes
totpExemptexempts only from the code, not from a denial: a rule “Germany only” applies to these keys too.- The viewing person is unknown during scheduled refresh. Who sees which report in the Power BI service is governed by Power BI; the service sees only the data gateway's access.
- Data in the report stays in Power BI and in Excel files, even after the key is revoked; the next refresh then fails.