Give the agent a tool, not a login

4 days ago

7 min read

Eleven rules from putting production SQL Server data behind an AI assistant, read-only, with the receipts.

In September we put an AI assistant in front of our production databases. Support staff ask it questions in chat ("what is the customer type of this account?", "did this message get delivered?") and it answers from live data in seconds. Nobody who asks holds a database login. The assistant doesn't hold one either, not in the way you'd expect

This post is the design, the eleven rules that came out of it, and one real incident where the design got tested. Three of the sections are interactive: click through them at your own pace.

The stack, in one line: Microsoft's open-source Data API builder (DAB) turns a JSON config into an MCP endpoint; an agent platform calls it as a tool; a small policy function sits between them; SQL Server is the last wall.

The shape of it

Every question takes the same path. Click Next to push one request through it.

Widget 1: how a request travels. Next, Back, Replay; arrow keys work when the widget has focus.

The point of the diagram is not the number of boxes. It is that the model never writes SQL. It calls a tool with an entity name and a filter, and DAB's query builder writes parameterized SQL over the entities and columns that role was granted. Everything else in this post follows from that choice.

Rule 1. Give the model a tool over entities, never a SQL login

Most database MCP servers are NL2SQL: the model writes T-SQL and runs it under some login. The blast radius is the login's. A read-only login still lets the model scan any table it can see, pull any column, and run a 40-minute aggregate on your busiest table.

DAB is NL2DAB. The model calls read_records with an entity and an OData filter; DAB writes the SQL. The model can reach exactly what the config grants, and nothing else:

"Customer": {
  "source": { "object": "dbo.Customer", "type": "table" },
  "permissions": [
    {
      "role": "data-api-reader",
      "actions": [
        {
          "action": "read",
          "fields": {
            "exclude": [ "StreetAddress", "InvoiceContacts", "PaymentProviderId", "PaymentWalletId" ]
          }
        }
      ]
    }
  ]
}

That is the whole grant. No application code, no handwritten endpoint. In production we expose seven entities over four tables to one role; in dev, 1,095 tables and views, read-only, for building and testing.

Rule 2. Switch write tools off in config, and fail the build if they come back

DAB 2.0 exposes seven MCP tools. We run two: describe-entities and read-records. The other five are off in the config:

"runtime": {
  "mcp": {
    "enabled": true,
    "path": "/mcp",
    "dml-tools": {
      "describe-entities": true,
      "read-records": true,
      "aggregate-records": true,
      "execute-entity": false,
      "create-record": false,
      "update-record": false,
      "delete-record": false
    }
  }
}

A config setting is one pull request away from changing. So CI reads the production config and fails the build if any write tool is on or any entity grants anything but read:

jq -e '.runtime.mcp["dml-tools"]
       | to_entries | map(select(.key | test("create|update|delete|execute")))
       | all(.value == false)' dab-config.prod.json

jq -e '[ .entities[].permissions[].actions[]
         | (if type == "string" then . else .action end) ]
       | all(. == "read")' dab-config.prod.json

Two lines. They have never failed in anger, which is the point: the gate makes the conversation about turning a write tool on an explicit one.

Rule 3. Require a key on every read, and enforce it outside the prompt

"Don't run unbounded queries" in a system prompt is a wish. Our policy function (a small Lambda between the gateway and DAB) refuses any read_records call that does not carry one of:

one account ID, or
one message ID, or
a SentAt window of 31 days or less.

Aggregates are refused outright on every production entity, even though DAB's aggregate tool is technically on. The model gets a clear error naming the missing key, and in practice it fixes the call on the next turn.

Rule 4. Cap every answer at the API

Two caps, in two places. DAB pages at 200 rows:

"runtime": {
  "pagination": { "default-page-size": 100, "max-page-size": 200 }
}

and the policy function truncates any response above 1 MB before it reaches the model. The model never sees the raw table; it sees at most a page, and it knows it.

Rule 5. Hide content columns twice

Message bodies never leave the database. They are excluded in DAB (fields.exclude on every message-log entity, as in Rule 1), and the policy function also refuses any filter that references them. The second half matters: a column you cannot select is still a column you can filter on, and Content eq 'your one-time code is…' is a data leak with an extra step.

Rule 6. Expose only /mcp in production

One DAB config produces three endpoints: REST, GraphQL and MCP. In production the service mesh routes only /mcp, and every path except /health requires a valid token. Nothing is wrong with REST or GraphQL; they are two more surfaces to review, and the agent does not need them.

Now try to break it.

Widget 2: what it exposes, and what stops you. Click any attempt, or Next to run them in order.

Rule 7. Short tokens, exact-match roles

The agent platform mints a 900-second JWT that carries the caller's roles. The gateway checks two things on every call: the audience (data-api) and the role (data-api-reader), by exact string. The policy function then swaps that token for one that lives five minutes at most before it talks to DAB. DAB maps the role claim to its own role by exact name, and grants anonymous and authenticated callers nothing.

A role name is a string. Make the comparison exact, make the names boring, and give each audience its own role and login (Rule 11 comes back to this).

Rule 8. SQL Server is the last wall

Assume every layer above has failed. The login can still only read four tables, on a readable secondary, under a Resource Governor pool:

-- the login reads exactly the exposed tables, nothing else
CREATE USER svc_readapi FOR LOGIN svc_readapi;
GRANT SELECT ON OBJECT::dbo.Customer        TO svc_readapi;
GRANT SELECT ON OBJECT::dbo.CustomerProfile TO svc_readapi;
GRANT SELECT ON OBJECT::dbo.CustomerProduct TO svc_readapi;
GRANT SELECT ON OBJECT::dbo.MessageLog      TO svc_readapi;

The connection string carries ApplicationIntent=ReadOnly, so reads land on a secondary replica and a write fails at the engine even if everything above let it through.

-- in master: a pool and a workload group for the data API, on every node
CREATE RESOURCE POOL rp_readapi WITH (
    MAX_CPU_PERCENT = 10, MAX_MEMORY_PERCENT = 5, MAX_IOPS_PER_VOLUME = 500);

CREATE WORKLOAD GROUP rg_readapi WITH (
    MAX_DOP = 1, GROUP_MAX_REQUESTS = 4, REQUEST_MAX_CPU_TIME_SEC = 30)
USING rp_readapi;
GO
CREATE FUNCTION dbo.fn_readapi_classifier() RETURNS sysname
WITH SCHEMABINDING AS
BEGIN
    RETURN CASE WHEN SUSER_SNAME() = N'svc_readapi' THEN N'rg_readapi' ELSE N'default' END;
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_readapi_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Two things we learned the hard way. REQUEST_MAX_CPU_TIME_SEC only aborts a query when trace flag 2422 is on; without it, the limit raises an event and the query keeps running, and the client's 30-second command timeout is what actually ends it. And MAX_DOP = 1 is not a performance choice, it is a courtesy: a one-row lookup does not need four cores of a production replica.

Rule 9. Carry the caller's claims to SQL, and capture them

DAB can pass the caller's token claims to SQL Server on every query:

"data-source": {
  "database-type": "mssql",
  "connection-string": "@env('DAB_CONNSTR')",
  "options": { "set-session-context": true }
}

That puts the claims in SESSION_CONTEXT for the life of the request, which means an Extended Events session filtered on the login captures who asked, not just what ran:

CREATE EVENT SESSION readapi_audit ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.client_app_name, sqlserver.session_id)
    WHERE sqlserver.server_principal_name = N'svc_readapi')
ADD TARGET package0.event_file (SET filename = N'readapi_audit', max_file_size = 256)
WITH (MAX_MEMORY = 16 MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS);

The set-session-context call DAB issues arrives in the same RPC batch as the query, so the captured statement text includes the claims. Until that session is on, attribution comes from the policy function's logs, kept one month. Rule 11 lists the session as a gate for a reason.

The day it got tested

On a Friday in October, a VIP client's campaign came up 2,481 messages short, and the support thread asked the assistant three times. Here is the afternoon, with names and the client masked. Click through it.

Widget 3: one incident, three answers. Names and the client are masked.

Three things to notice. The first answer was wrong, and said so in advance: self-rated: medium, sources listed, one data-API call. The second answer, built on the team's numbers, found the two sending waves and the stall, said which tables it could not see, and handed off to a human. The third answer, asked to confirm rows in a table outside its role, said "I cannot verify the answer from the tools available to me" and listed what someone with access should check. The table was empty, as the root cause predicted.

Rule 10. Make the agent show its work, and give it a way out

Three mechanisms, all cheap:

A footer on every answer: elapsed seconds, which agents and tools ran, token count, an evidence rating and a self-rating. The self-rating is what made the first answer a hypothesis instead of a verdict. The team treated it as one.
A "Tag a human" button that resolves the on-call engineer. The hand-off in the thread took six minutes, and it is how the actual root cause (a node rotation evicting a scheduler pod mid-batch) got found.
describe_entities first. Because the role is explicit and the agent can list it, it can tell you what it cannot see. An agent with a broad login would have guessed.

Rule 11. Grow scope through named gates, not requests

Within a month, operations asked for 48 more tables. The answer was not yes or no; it was a list of gates that have to exist first, each a concrete artifact:

G1: an exact-name allowlist of entities in the policy function, so a new entity in the config is not automatically reachable.
G2: fields.include (not exclude) on every new entity, checked in CI, so a new column is hidden by default.
G3: projection views in a dedicated schema, so the API never reads a base table directly.
G4: a separate role and a separate SQL login per audience, so operations' grants and engineering's grants cannot drift into each other.
G5: the per-call audit line and the Extended Events session from Rule 9.

Nothing reaches production until all five are built and tested. That sentence is doing more security work than any single layer in the diagram.

If you take one thing

Give the agent a tool over entities, with a role it can describe back to you, and put every cap and refusal in code it cannot talk its way past. Then let it be wrong out loud. The incident above would have gone worse with a smarter model and a broader login.