How to Configure Pydantic AI SQL Server Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a pydantic ai sql server connect workflow by passing a database driver into your agent dependencies. Next, you create a tool function that runs SQL queries through that driver. Many teams start this setup by sharing an existing admin login with the agent. That quick shortcut gives the model full power to change tables or read private records. If a prompt goes wrong, an overly broad role lets the agent delete production rows. How a native pydantic ai sql server connect setup works I

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 a pydantic ai sql server connect workflow by passing a database driver into your agent dependencies. Next, you create a tool function that runs SQL queries through that driver. Many teams start this setup by sharing an existing admin login with the agent. That quick shortcut gives the model full power to change tables or read private records. If a prompt goes wrong, an overly broad role lets the agent delete production rows.

How a native pydantic ai sql server connect setup works

In a standard setup, the agent framework holds your database session inside a dependency object. When a user asks a question, the language model decides to call your query tool. That tool pulls the active database session from the dependency context and sends the query. SQL Server checks who the agent is before it runs any query.

A database user is the identity of a login when it connects to a database. If your agent needs many databases on one instance, a login in master is common. If your agent only needs one database, you can use a contained database user instead. This user has a password and does not tie back to a login in master. An admin must first turn on contained databases on the server and the target database. SQL Server supports thirteen different types of database users for these setups.

Once the user exists, you assign a fixed database role to control what it does. Every role, including the default public role, holds basic permission to connect. You cannot change the built-in permissions that belong to fixed database roles. If you assign the owner role, the agent can run all maintenance tasks. An owner role can even drop the entire database in SQL Server.

Choosing read-only roles for agent tools

Most AI agents only need to read records to answer user questions. For this job, teams often add the agent user to the fixed data reader role. Members of this reader role can read all data from all user tables and views. If the agent must save results, teams look at the fixed data writer role. That writer role can add, delete, or change data in all user tables.

You can also block actions with deny roles when you set up your database user. The deny data writer role stops the user from adding, changing, or deleting any data. The deny data reader role blocks reading data or metadata from user tables and views. Combining a reader role with a deny writer role keeps the agent from changing tables.

Kubernetes API Server Access + AI Agent Security: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Where native database roles fall short for AI agents

Native roles decide who can log in and which tables they can touch. They do not inspect what individual queries do while they run in flight. A fixed data reader role lets your agent read every single user table and view. That means the agent can pull emails, phone numbers, card numbers, and national IDs.

Even with custom table grants, a single wide query can still pull sensitive columns. Database roles also treat every query from the agent login as the exact same user. You cannot tell which human prompt triggered a slow or risky query in the logs. When an agent writes bad SQL, static roles cannot pause the action for human review. Teams need a way to inspect queries and rewrite sensitive fields in results.

Check SQL Server batches and results on the wire

SQL Server logins and roles decide who connects. They do not check what each TDS batch does or mask sensitive values in returned rows. With hoop.dev, you run an open-source proxy, a sidecar for your SQL Server. It reads plain batches and parameterized queries on the TDS wire while passing Windows login through untouched.

Before a batch hits the server, hoop.dev guardrails check the statement against your rules. Disallowed actions get a native TDS error with your message. On the way back, hoop.dev data masking rewrites sensitive values in supported result columns that match your rules. Every query and decision is logged. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Should I use a contained database user for an AI agent?

Yes, if your agent only queries one or a few databases on your server. A contained user stays inside that single database and does not need a master login. An admin must enable contained databases on the server and database first.

Does the fixed data reader role hide sensitive columns from my agent?

No, the fixed data reader role grants read access to all user tables and views. You cannot alter the permissions of a fixed role to block specific columns. To hide emails or card numbers, you need custom grants or result masking.

How do I stop my agent from changing data in SQL Server?

Do not give the agent the owner role or the data writer role. Assign the data reader role and add the deny data writer role for extra safety. This blocks the user from adding, changing, or deleting rows in user tables.

The query runs. The emails stay hidden.