How to Configure GitHub Copilot SQL Server MCP Server
You set up a github copilot sql server mcp server by linking your editor to a database tool. Next, you give that tool a read-only database user so the AI agent only reads data. Teams often start by putting a local database string into their editor settings. To save time, developers sometimes reuse an existing admin login during the first test. When roles are too broad, a single prompt can change rows or drop a whole database. Setting up narrow permissions from day one keeps your data safe while
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 github copilot sql server mcp server by linking your editor to a database tool. Next, you give that tool a read-only database user so the AI agent only reads data.
Teams often start by putting a local database string into their editor settings. To save time, developers sometimes reuse an existing admin login during the first test. When roles are too broad, a single prompt can change rows or drop a whole database. Setting up narrow permissions from day one keeps your data safe while the agent works.
How the github copilot sql server mcp server works
The Model Context Protocol lets AI coding agents talk to external tools, databases, and APIs. When you ask a question in your editor, the agent calls the local MCP process. That process uses your database login string to run queries against your SQL Server instance.
In SQL Server, a database user is the identity of a login inside a database. When an account needs to reach many databases, teams create a login in the master database. If the agent only needs one database, an admin can use a contained database user instead. That contained user has a password and does not tie back to a master login. Admins must first turn on contained databases on the server and the target database. SQL Server supports thirteen types of database users in total.
Once you create the user, you assign a fixed database role to limit its power. You cannot change the base rules built into SQL Server fixed database roles. Every role, including the public role, comes with basic database entry rights.
For an AI coding agent, teams add the user to the fixed data reader role. Members of this reader role can read all data from all user tables and views. You can also add the deny data writer role to block any adds, edits, or deletes. By contrast, the fixed data writer role lets a user add, delete, or change table data. Worse, the fixed owner role can run maintenance tasks and even drop the database.
What native SQL Server roles solve and what they miss
Native database roles solve the first step of access control for your AI agent. They decide which user can log in and whether that user can write to tables. Using a dedicated read-only user stops the agent from deleting rows or changing schemas.
Kubernetes API Server Access + GitHub Actions Security: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, a static role does not check what individual queries do in flight. The fixed data reader role lets the user read every user table and view. If a table holds customer emails, phone numbers, or card numbers, the query pulls them back. The MCP tool then sends those raw rows right into the AI model context.
You can use the deny data reader role to block all user tables and views. Yet fixed roles do not give you a middle ground for safe column masking. Also, storing raw database passwords in local editor files creates a secret sprawl risk. Teams need a way to inspect queries and mask fields like national IDs in results.
Managing credentials and query safety for AI agents
When many developers use AI agents, sharing static database passwords quickly gets messy. Each laptop holds plain text connection strings that are hard to rotate or track. If a laptop is lost, admins must reset the shared database user password for everyone.
Shared database logins also make audit logs hard to read during a security review. Every query from every developer shows up under the same SQL Server user name. Security teams cannot tell which person or prompt ran a heavy query against a table. Splitting identity from the raw database role helps teams see who ran each query.
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, inline in the TDS wire protocol. On every batch an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.
Before a batch hits the server, hoop.dev guardrails refuse disallowed actions with a native TDS error or hold risky statements for approval. On the way back, hoop.dev data masking rewrites sensitive values in supported result columns in memory, and every decision is logged. Explore the MIT-licensed project on GitHub.
Frequently asked questions
Should I give my AI agent the database owner role?
No, you should never give an AI agent the fixed database owner role. That role can change server settings, alter tables, and drop the whole database.
Does the SQL Server data reader role hide sensitive columns?
No, the fixed data reader role can read all data in all user tables and views. It does not rewrite emails, phone numbers, or card numbers in query results.
When should I use a contained database user for an MCP setup?
Use a contained user when the agent only needs one or a few databases. An admin must enable contained databases on the server and database before you start.
Related guides
In this post