RLS-aware AI tools: securing enterprise data in Copilot Studio

Putting an AI assistant in front of a governed dataset is one of the most requested features in enterprise software right now. It is also one of the easiest to get quietly, dangerously wrong. The demo works. The integration leaks. The two facts are not in tension — they're the same fact, seen before and after someone checks who can see what.

I got to live both halves of that on a real build: a media client's analytics platform with an Angular front end, a Copilot Studio agent in a chat panel, and an MCP server — a .NET 8 Azure Function — that lets the agent run DAX against a Power BI semantic model. The first prototype demoed beautifully. It was also, precisely, the leak.

The service-identity trap

Here is the integration almost every team builds first, because it's the integration that's easiest to build. The assistant needs to query a data platform, so it's given a service identity: one app registration, one client secret, a client_credentials token, broad access. Our prototype's endpoint called Power BI's ExecuteQueries API exactly that way. Any user, any question, one identity.

The problem isn't subtle, and it isn't an implementation bug you can patch. Power BI's documentation is blunt about it: service principals can't be added to an RLS role, so RLS is not applied when a service principal is the final effective identity. The row-level security on that dataset — the rules that say a regional manager sees their region and not the others — was still configured, still correct, and completely bypassed. When I audited the prototype against the architecture spec, that was the headline finding, and every other functional requirement cascaded from fixing it.

Copilot Studio makes this trap unusually comfortable, because it's one radio button. A tool attached to an agent runs with either end-user credentials or maker-provided credentials. Maker-provided credentials are the service-identity trap in low-code clothing: every user of the agent now queries as the person who built it.

On-behalf-of is the whole game

The fix is not a patch on top of the service-identity design. It's a different design. The assistant must call the data platform as the user who asked the question, carrying that user's identity through every hop.

The mechanism is the OAuth 2.0 on-behalf-of flow: the middle tier receives the user's token and exchanges it for a new token scoped to the downstream resource. In the working system the chain looks like this:

1. The user signs into the Angular app and invokes the Copilot Studio agent.
2. The agent calls the MCP server through a custom connector configured for OAuth with on-behalf-of login enabled, so the tool call carries a delegated token for the Function — not a bot identity, not a secret.
3. The Function validates that inbound JWT properly — signature, issuer, audience, and the scope claim — before doing anything else.
4. Only then does it exchange the token: MSAL's AcquireTokenOnBehalfOf (via Microsoft.Identity.Web and ITokenAcquisition in .NET) swaps the user's token for a Power BI-scoped one, cached per user so the exchange happens once per session rather than per query.
5. The DAX query executes against Power BI as the actual human who asked.

Row-level security is now enforced by the platform that owns the data — not by application code, not by hope. Each person sees exactly the rows their role allows, because the query genuinely runs as them.

If your AI tool calls the data platform under a service identity, it is a data leak with good manners.

The proof, when it came, was almost anticlimactic: a test account mapped to a deliberately narrow RLS role ran EVALUATE TOPN(5, 'Dim Client Info') through the agent and got back only the rows that role allows. Five rows, correctly filtered, is worth more than any architecture diagram. Make that your acceptance test and run it with two differently-scoped accounts before anyone demos anything.

One operational cost of doing identity properly, so it doesn't burn you the way it briefly burned us: the first time an agent calls a user-authenticated connector, Copilot Studio sends a consent card — an adaptive card asking the user to allow the connection under their identity. Our custom chat widget filtered out any activity without message text, so the card (which has none — it's all attachment) was silently dropped. To the developer it looked like "the agent can't reach the MCP." It was actually the OAuth handshake succeeding and the UI eating the evidence. If you front Copilot Studio with your own client, render adaptive cards before you debug anything else — the consent card appearing is proof the delegated auth is working.

Where identity alone won't save you

On-behalf-of carries the who correctly. It does not decide whether filtering applies — and this is the part I'd underline for anyone reviewing such a design, because two facts surprised people on this project:

• RLS only filters workspace Viewers. Power BI does not apply RLS to workspace Admins, Members, or Contributors. If your business power users hold Contributor roles, the agent will show them everything — under their own identity, legitimately. That was fine for this client (report consumers are Viewers; the finance team is deliberately unfiltered), but it has to be a decision, not a discovery.
• Workspace roles are your second gate, and you should lean on them instead of re-implementing them. The Entra scope on the token (Dataset.Read.All or Dataset.ReadWrite.All) is a capability ceiling; Power BI's own dataset and workspace permissions decide what each user can actually do. We deliberately wrote no role-checking code in the Function — a user without Build permission gets Power BI's 401/403, and the MCP surfaces it. The platform that owns the data stays the authority.

And the honest counterpoint: a service identity is not intrinsically wrong. Power BI's app-owns-data embedding pattern legitimately uses a service principal plus an explicitly supplied effective identity — username, roles, and optionally CUSTOMDATA — stamped into the embed token. That works because the impersonation is explicit, declared per request, and auditable. What's indefensible is the ambient service identity: one broad credential with nobody's name attached to any query. If you can't carry the user's token end-to-end, carry their identity explicitly the way embed tokens do — but for a conversational agent inside the user's own tenant, on-behalf-of is the cleaner and safer default.

An agent that can query can run a bad query

Identity is the first gate. The second is the query itself. An assistant that generates and executes DAX can be steered — by an ambiguous request, or by deliberately injected instructions riding in on retrieved content, which is prompt injection, the top entry in OWASP's LLM risk list — into generating a query you would not have approved.

So the generated query gets validated before it executes, deterministically. Ours enforces an allowlist shape: statements must start with EVALUATE; DEFINE blocks, MDX SELECT, and DMV queries are rejected; row counts are capped with TOPN. The agent proposes; a boring, testable check disposes. Microsoft's own guidance for mid-tier DAX services says the same thing — sanitize DAX input, enforce least privilege, and never expose the query endpoint as an open proxy. This is not distrust of the model. It's the same defense in depth you'd apply to any input that reaches a query engine.

Instrument every call

The last requirement is the one that turns a clever feature into something a security team will sign off on: every tool call is logged — who asked, what query ran, how many rows came back, how long it took, and Power BI's request ID for correlation. We emit structured events to Application Insights from the Function; Microsoft's best-practices doc recommends exactly this query-metadata logging, and it earns its keep the first time someone asks "what did this thing do last Tuesday" — or the first time a security reviewer asks you to prove the RLS story rather than assert it.

What's moved since I built this

Two updates worth knowing if you're building the same thing now. The JSON executeQueries endpoint has a hard limit of 100,000 rows and 1,000,000 values per query; Microsoft has since shipped an Arrow-based executeDaxQueries endpoint without fixed row limits, Premium/Fabric capacity only. Everything in this post ports over unchanged — including the caution: the new API's effectiveUsername parameter lets a service identity impersonate a user for RLS, which is powerful and exactly the kind of thing your validator and your audit log need to know about.

The short version

Carry the user's identity all the way through, and prove it with two differently-scoped test accounts before you demo. Let the data platform enforce its own row-level security — and check who RLS actually applies to, because workspace Admins, Members, and Contributors are exempt. Validate the generated query before it runs. Log every call. Do those four things and an AI assistant over governed data stops being a risk you're quietly carrying and becomes a capability you can defend.