How to Configure Mastra SQL Server Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up a mastra sql server connect workflow, create a scoped database user. Then, pass those login details to your Mastra agent tool. Teams often start by reusing a broad admin login during early testing. This shortcut exposes every table to unwanted changes or drops if an agent misfires. Scoping the database account before your agent runs keeps your data safe. How a mastra sql server connect setup works In SQL Server, a database user is the identity of a login inside a database. Microsoft

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 mastra sql server connect workflow, create a scoped database user. Then, pass those login details to your Mastra agent tool. Teams often start by reusing a broad admin login during early testing. This shortcut exposes every table to unwanted changes or drops if an agent misfires. Scoping the database account before your agent runs keeps your data safe.

How a mastra sql server connect setup works

In SQL Server, a database user is the identity of a login inside a database. Microsoft supports thirteen different types of database users for various access needs. When an app needs access to many databases, teams create a login in master. They then map that server login to a SQL user in each target database.

Most AI agents only need to reach one specific database to do their job. For one or a few databases, a contained database user is a good option. This user has a password and is not tied to a login in master. An admin must first turn on contained databases on the server and database.

Once the user exists, you assign permissions using fixed database roles. Every role in the database, including the public role, holds the basic connect permission. You cannot change the built-in permissions assigned to any fixed database role. For an agent that only answers questions, assign the user to the reader role. Members of this role can read all data from all user tables and views.

Assigning write roles and blocking sensitive data

Some Mastra workflows need to insert new rows or update existing records for users. For these tasks, admins often add the agent user to the fixed writer role. Members of this role can add, delete, or change data in all user tables. If you want to block writes, use the deny writer role instead. That role stops the user from adding, changing, or deleting any table data.

Never assign an automated agent to the database owner role in production systems. Members of the owner role can run all setup and maintenance tasks. They can even drop the entire database in SQL Server with one bad statement. Admins add users to roles using standard role change statements in SQL Server. In Azure Synapse, admins must use a specific system stored procedure to add members.

Kubernetes API Server Access + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native database roles leave open

Native SQL Server roles decide who can log in and which tables they reach. They do not inspect what individual queries do while the agent is running. A user in the fixed reader role can still read every single user table. That includes sensitive columns holding emails, phone numbers, card numbers, or national IDs. You can use the deny reader role to block all user tables and views. Still, fixed roles alone cannot rewrite sensitive fields inside a single query result.

Likewise, the fixed writer role lets an agent delete rows across every user table. If a prompt causes the agent to build a bad delete query, the database runs it. Sharing static passwords in app files also makes it hard to track agent actions. Teams need a way to inspect queries and mask fields like emails in flight.

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 standard login or a contained user for Mastra?

Use a contained user if your Mastra agent only needs one or a few databases. It avoids creating a server login in master, though an admin must enable containment first.

Can I customize the permissions inside a SQL Server fixed database role?

No, SQL Server does not let you change the permissions assigned to fixed database roles. To limit access further, you must create custom roles or grant permissions per table.

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

Assign the agent user to the fixed reader role and the deny writer role. This lets the agent read user tables while blocking all data changes and deletes.

The query runs. The emails stay hidden.