How to Configure Goose SQL Server MCP Server
To set up a goose sql server mcp server, link the agent to your database. Give the agent a read-only SQL Server user so it can run safe queries. Many teams start by reusing an admin login to get the tool working fast. That broad role lets an AI agent change tables or drop the database by mistake. How a goose sql server mcp server uses database roles Goose uses the Model Context Protocol to send SQL queries from the local app. The MCP server takes prompts from the agent and turns them into dat
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 goose sql server mcp server, link the agent to your database. Give the agent a read-only SQL Server user so it can run safe queries. Many teams start by reusing an admin login to get the tool working fast. That broad role lets an AI agent change tables or drop the database by mistake.
How a goose sql server mcp server uses database roles
Goose uses the Model Context Protocol to send SQL queries from the local app. The MCP server takes prompts from the agent and turns them into database commands. Before you connect the tool, you must pick the right SQL Server user type. A database user is the identity of a login when it connects to a database. Microsoft offers 13 types of database users for different needs.
If the agent needs many databases on an instance, use a standard SQL user. This user links to a login in the master database. If the agent only needs one database, you can use a contained database user. A contained user has a password and does not tie to a master login. An admin must first turn on contained databases on the server and the database.
Setting up read-only permissions in SQL Server
Once you create the user, you must limit what the agent can do. Every user sits in the public role, and all roles have connect permissions. You cannot change the permissions assigned to fixed database roles in SQL Server. Instead, you add the agent user to the right fixed role for the job.
For most AI tasks, add the user to the fixed data reader role. Members of this role can read all data from all user tables and views. You can also add the user to the deny data writer role for safety. Members of that role cannot add, change, or delete data in user tables. Never put an AI agent in the database owner role. That owner role can run all maintenance tasks and even drop the database.
What native roles solve and what they leave open
Fixed database roles do a good job of stopping basic write mistakes. When the agent uses a read-only role, it cannot delete rows or change tables. This setup decides who logs in and which broad actions they can take. Still, native roles do not check what single queries do in flight.
Kubernetes API Server Access + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
A read-only user can still read every column in every user table and view by default. When the agent queries a customer table, the database returns all matching rows. Those results often hold emails, phone numbers, card numbers, or national IDs. The local MCP server passes that raw text right into the AI model context. Also, the database user password must sit on the developer machine in plain text. Anyone with access to that local file can take the key and log in.
Native roles also lack a step to pause risky queries for human review. If an agent writes a huge join, the database runs it right away. Teams need a way to check queries in flight and mask sensitive fields. Rewriting emails or card numbers in results keeps private data out of model prompts.
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
Which SQL Server role is safest for a Goose agent?
Add the database user to the fixed data reader role and deny data writer role. This lets the agent read user tables and views while blocking all data changes.
Should I use a login in master or a contained database user?
Use a contained user if the agent only needs one or a few databases. Use a standard login in master if it must read across many databases.
Does a read-only SQL Server role hide sensitive columns from Goose?
No, the fixed data reader role lets the user read all user tables and views. It does not rewrite emails, phone numbers, or card numbers in query results.
Related guides
In this post