How to Configure Pydantic AI SQL Server MCP Server

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a pydantic ai sql server mcp server by linking an agent to a database user. You then restrict that database user to a read-only role. Many teams start by reusing an admin account during early tests. That shortcut creates huge risks when the agent runs in production. A broad role lets the model change rows or drop the database. How a pydantic ai sql server mcp server works Pydantic AI builds structured agents that call external tools to fetch data. The Model Context Protocol, or MCP

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 mcp server by linking an agent to a database user. You then restrict that database user to a read-only role. Many teams start by reusing an admin account during early tests. That shortcut creates huge risks when the agent runs in production. A broad role lets the model change rows or drop the database.

How a pydantic ai sql server mcp server works

Pydantic AI builds structured agents that call external tools to fetch data. The Model Context Protocol, or MCP, gives these agents a standard way to run queries. The MCP server sits between the agent and your SQL Server instance. When the agent needs data, it sends a tool request to the MCP server. The MCP server turns that request into a SQL query and runs it.

To do this safely, you must set up the right SQL Server identity. In SQL Server, a database user is the identity of a login for a database. There are 13 types of database users available in the system. If an agent needs many databases on an instance, teams use a SQL user with a login. That login lives in the master database.

For one or a few databases, a contained database user is a good option. This is a SQL user with a password that is not tied to a master login. An admin must first enable contained databases on the server and the target database. Once the user exists, you assign it a fixed role.

Setting up SQL Server roles for AI agents

SQL Server includes fixed database roles to control what a user can do. You cannot change the permissions assigned to these fixed database roles. Every role, including the public role, has permission to connect to the database. To keep an AI agent safe, you should avoid the owner role. A member of the owner role can do all maintenance tasks and drop the database.

Instead, add the agent user to the data reader role. Members of this role can read all data from all user tables and views. If the agent must never change records, you can also add the deny data writer role. That role stops the user from adding, changing, or deleting any table data. If you want to block all reads, the deny data reader role blocks all tables and views. It also blocks metadata about those tables and views.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Why native roles leave a gap for AI queries

Native database roles decide who logs in and which tables they can read. They do not inspect what individual queries do while they run in flight. A member of the data reader role can still read every user table. That means the agent can pull sensitive rows from customer tables.

When the MCP server runs a query, the database returns raw text to the agent. This result might hold emails, phone numbers, card numbers, or national IDs. Data masking rewrites matched fields in results before they reach the model. Native roles also cannot pause a single risky query for human approval. Teams need a way to check queries and mask fields in real time.

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

What SQL Server role is best for a read-only AI agent?

Assign the database user to the fixed data reader role. This role lets the agent read all user tables and views without changing data. You can also add the deny data writer role to block all writes.

Should I use a standard login or a contained user for MCP?

Use a contained database user if the agent only needs one or a few databases. It keeps the user and password inside the target database instead of the master database. An admin must enable contained databases on the server and database first.

Does a read-only role hide sensitive customer fields from the model?

No, the data reader role lets the user read all data in every user table. To protect emails, phone numbers, or card numbers, you must mask matched fields in results.

The query runs. The emails stay hidden.