How to Configure Mastra SQL Server Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up mastra sql server data masking by combining database roles with masking rules. This setup rewrites matched fields like emails and phone numbers in query results. Many teams start by giving their AI agents a shared database login. When that login has broad permissions, the agent can read every table in the database. A single bad prompt can pull raw customer card numbers or national IDs into logs. How mastra sql server data masking works with native roles SQL Server separates server

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up mastra sql server data masking by combining database roles with masking rules. This setup rewrites matched fields like emails and phone numbers in query results. Many teams start by giving their AI agents a shared database login. When that login has broad permissions, the agent can read every table in the database. A single bad prompt can pull raw customer card numbers or national IDs into logs.

How mastra sql server data masking works with native roles

SQL Server separates server logins from individual database users. A database user is the identity of a login inside one specific database. If an agent needs many databases, admins create a SQL user tied to a master login. If the agent only needs one database, admins can create a contained database user instead. First, an admin must turn on contained databases on the server and the database. SQL Server supports thirteen different types of database users for these setups.

After creating the user, admins assign it to a fixed database role. Every database role, including the public role, includes basic permission to connect. Teams often place read-only AI agents into the fixed datareader role. Members of this datareader role can read all data from all user tables and views. You cannot change the permissions built into a fixed database role. If an agent uses the owner role, it can even drop the database.

To hide private values from the agent, admins add column masking rules on the tables. When the Mastra agent runs a query, the database checks the user identity. It then rewrites matched columns like emails or card numbers before returning the rows. If a table holds too many secrets, admins can use the deny datareader role. Members of that role cannot read any data or metadata from user tables and views.

What native roles solve and what they leave open

Native database roles and column rules solve basic login control for your data. They decide who can log in and which tables that account can read or write. For example, the deny datawriter role stops a user from changing or deleting table rows. Meanwhile, the datawriter role lets a user add, change, or delete rows in any user table. These roles give teams a clear way to block write commands from read-only agents.

Still, native database setups leave a large operational gap during live agent runs. Database roles only check who logs in at the start of a session. They do not inspect what individual queries do in flight. Because the fixed datareader role reads every user table, an agent can query anything. If an admin forgets to tag one new column, raw phone numbers flow to the model.

Data Masking (Static) + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Managing column rules across dozens of tables also takes constant work from database admins. Schema changes can break rules or leave new tables wide open to the datareader role. Also, native roles do not tie specific AI agent runs to a human user. When ten Mastra workflows share one contained user, the database log shows one identity. Teams cannot tell which agent task or prompt ran a risky query.

Why AI workflows need runtime query checks

Mastra agents build and run queries based on user prompts and tool definitions. Static database users cannot adapt when an agent switches between different tasks or users. If you grant broad read roles, every prompt shares the same wide database reach. If you lock down the user too much, legitimate agent tasks fail right away.

Teams need a way to inspect queries and rewrite sensitive fields in flight. When you check results at runtime, you do not rely on manual column tags alone. Pattern matching can spot emails, phone numbers, card numbers, and national IDs across any table. It rewrites those matched fields in the results before the Mastra agent reads the response. This keeps sensitive values out of agent memory and third-party model logs.

Govern SQL Server queries and responses in flight

Table grants decide which SQL Server objects a caller may touch. They do not check query intent or rewrite sensitive values in results across every client and agent. By design, hoop.dev is an open-source proxy that sits next to SQL Server as a sidecar for your SQL Server. It inspects TDS batches and parameterized queries in real time.

With hoop.dev guardrails and hoop.dev data masking, banned statements are refused with your message. Matched sensitive fields in supported result columns are masked in memory, and each decision is logged per listener. Check out the code on GitHub.

Frequently asked questions

Can I change the permissions of the SQL Server datareader role for Mastra?

No, SQL Server does not let you change the permissions assigned to fixed database roles. The datareader role always grants read access to all user tables and views in the database.

Should Mastra use a master login or a contained database user?

Use a contained database user if your Mastra agent only needs one or a few databases. Use a login in master if the agent must read across many databases on the server.

What sensitive fields should be rewritten in Mastra query results?

Your setup should rewrite structured fields like emails, phone numbers, card numbers, and national IDs. Rewriting these matched fields in query results keeps raw values out of agent prompts and logs.

The query runs. The emails stay hidden.