How to Configure Vercel AI SDK SQL Server MCP Server
To set up a vercel ai sdk sql server mcp server workflow, bind your model client to a local tool runner. Then log in that tool runner to your database with a scoped SQL user. Teams often start by reusing an admin account during early testing. This shortcut exposes every table and lets the model change or drop live data. Setting up the vercel ai sdk sql server mcp server flow The setup links two main parts in your app stack. First, your app runs the SDK to manage prompts and tool calls. Second
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.
To set up a vercel ai sdk sql server mcp server workflow, bind your model client to a local tool runner. Then log in that tool runner to your database with a scoped SQL user. Teams often start by reusing an admin account during early testing. This shortcut exposes every table and lets the model change or drop live data.
Setting up the vercel ai sdk sql server mcp server flow
The setup links two main parts in your app stack. First, your app runs the SDK to manage prompts and tool calls. Second, the MCP server translates those tool calls into database queries. When a user asks a question, the model picks a database tool. The MCP server then opens a database session and runs the SQL statement.
To keep this flow safe, you must set up a dedicated database user. In SQL Server, a database user is the identity of a login inside a database. You have two main ways to create one for your MCP tool. If the tool needs many databases on one instance, create a SQL user with a login in master. If the tool needs just one database, a contained database user with a password works well. An admin must enable contained databases on the server and database first.
Next, you assign a role to this new database user. SQL Server offers fixed database roles with permissions that you cannot change. Every role, including the public role, gets the permission to connect. To limit the agent to read queries, add the user to the db_datareader role. Members of db_datareader can read all data from all user tables and views. You can also add the user to db_denydatawriter to block data changes. Members of db_denydatawriter cannot add, modify, or delete any table data. Never put an AI tool in the db_owner role. A db_owner member can run all maintenance tasks and even drop the database.
What native roles solve and what they leave open
Fixed database roles solve the first layer of database security. They check who logs in and block broad write actions at the table level. Using db_datareader stops a confused model from deleting rows or changing records. Using a contained user also keeps the agent inside one single database.
Kubernetes API Server Access + AI Agent Security: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, native database roles leave a big operational gap in production. A database role checks who logs in, not what each statement does in flight. Because you cannot change fixed role permissions, db_datareader opens every user table and view. If a table holds emails, phone numbers, card numbers, or national IDs, the tool reads them. The database sends those raw records straight back to the model context. A static role also cannot ask a human to approve a risky query. Finally, the database only sees the shared tool login, not which app user triggered the prompt.
Designing safer query rules for AI agents
Before you move to production, review what your AI agent needs to read. Most bots only need a few views to answer user questions. If you must use native roles, pair db_datareader with db_denydatareader on a separate schema will not work because fixed roles cover all user tables. Instead, teams need controls that inspect each query before it reaches the database engine. Good runtime controls rewrite matched fields like emails or card numbers in results. They also link every SQL statement back to the specific prompt and user identity.
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
Should I use a login in master or a contained user for my MCP server?
Use a contained database user if the MCP tool only reads one or a few databases. Use a login in master if the tool must read many databases on one instance.
Does the db_datareader role hide sensitive columns from the AI model?
No, members of db_datareader can read all data from all user tables and views. You cannot change the permissions assigned to fixed database roles to hide specific columns.
How can I be sure the AI agent cannot change or delete rows?
Add the database user to the db_denydatawriter role and avoid db_datawriter or db_owner. Members of db_denydatawriter cannot add, modify, or delete data in any user table.
Related guides
In this post