How to Configure Pydantic AI SQL Server Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up pydantic ai sql server data masking by combining strict database roles with output filtering rules. This setup stops agents from sending raw emails or card numbers to an external language model. Most teams start by giving the agent a shared database login. If that role is too broad, the agent can read and expose every table. How to set up pydantic ai sql server data masking First, you need to create a dedicated identity in your database for the agent. In SQL Server, a database user

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 pydantic ai sql server data masking by combining strict database roles with output filtering rules. This setup stops agents from sending raw emails or card numbers to an external language model. Most teams start by giving the agent a shared database login. If that role is too broad, the agent can read and expose every table.

How to set up pydantic ai sql server data masking

First, you need to create a dedicated identity in your database for the agent. In SQL Server, a database user is the identity of a login when it connects. If the agent needs many databases on an instance, use a SQL user with a master login. If it needs one or a few databases, you can use a contained database user. This user has a password and does not tie to a login in the master database. An admin must enable contained databases on the server and the database first.

Next, you assign a role to control what the agent can read. Many teams pick the fixed database role for reading data. Members of this role can read all data from all user tables and views. You cannot change the permissions assigned to fixed database roles in SQL Server. Every role, including the public role, has permission to connect to the database. You add the agent user to a role by altering the role membership.

Finally, you define the tool inside your agent code. The agent runs a query tool when it needs facts from the database. You write a data model that checks the rows before the model reads them. Masking rewrites matched fields in results, such as phone numbers, emails, or national IDs.

What native database roles solve and what stays exposed

Native database roles do a good job of stopping bad writes. You can use a deny writer role so the agent cannot add, change, or delete data. You should avoid the owner role for any automated agent. Members of the owner role can do all maintenance tasks and even drop the database. Using a read-only role keeps your tables safe from changes or drops.

Still, a fixed read role still leaves a large security gap in daily use. Members of the reader role can read every column in every user table and view by default. You cannot edit this fixed role to hide specific columns like card numbers or phone numbers. If you rely on a deny reader role, the user cannot read any user tables at all.

AI Data Exfiltration Prevention + Data Masking (Static): architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Database logins and roles only decide who can log in and which tables they can touch. They do not inspect what individual queries do in flight while the agent runs. If a developer forgets to add a check in Python, raw emails go to the model. Also, keeping database passwords inside the agent app creates a risk if someone leaks the code. Teams must maintain custom masking logic across every single tool function the agent uses.

Why query-level masking matters for agent workflows

Agents build and run SQL queries based on open prompts from users. You cannot always predict which columns or views an agent will ask for next. When masking happens inside app code, each new tool needs its own filter rules. One missed field in a response class means raw national IDs reach the model prompt. Moving data checks closer to the database wire makes rules consistent across all agent tools.

Add runtime guardrails beside SQL Server

Role grants and network rules are needed setup for SQL Server. Still, an allowed user or agent tool can send a bad delete or read raw customer fields over a valid session. In practice, hoop.dev runs beside the database as a sidecar for your SQL Server and checks TDS traffic without changing how clients log in.

With hoop.dev SQL Server controls, rules refuse banned statements before they run and can hold risky queries for human review. Matched sensitive values in supported result columns are masked before they reach the client. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for HIPAA, PCI DSS, SOX, and GDPR audits. See the source on GitHub.

Frequently asked questions

Can I change the fixed reader role to hide one table?

No, you cannot change the permissions assigned to fixed database roles in SQL Server. Members of the fixed reader role can read all data from all user tables and views.

Should my agent use a master login or a contained user?

Use a contained database user if the agent only needs one or a few databases. Use a SQL user with a master login if it needs many databases on the instance.

What types of fields should masking rewrite for AI agents?

Masking should rewrite matched fields in results like emails, phone numbers, card numbers, and national IDs. This stops the agent from sending raw personal details to the language model.

The query runs. The emails stay hidden.