How to Configure Copilot Studio SQL Server Connect
You set up a copilot studio sql server connect workflow by linking your bot to a database user. You then assign that database user a strict role to limit what the bot can read or write. Most teams start by creating a basic SQL login and sharing its password with the bot builder. When teams rush this step, they often grant broad owner roles so queries work right away. Broad roles let an automated agent read every table or change records across the database. How a copilot studio sql server conne
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 copilot studio sql server connect workflow by linking your bot to a database user. You then assign that database user a strict role to limit what the bot can read or write. Most teams start by creating a basic SQL login and sharing its password with the bot builder. When teams rush this step, they often grant broad owner roles so queries work right away. Broad roles let an automated agent read every table or change records across the database.
How a copilot studio sql server connect setup works
A database user is the identity of a login when it connects to a database. Microsoft SQL Server supports thirteen distinct types of database users for different access needs. When a bot or person needs many databases on one instance, admins create a login in master. They then map a SQL user to that central login.
When a bot needs just one or a few databases, a contained database user works well. A contained user is a SQL user with a password that is not tied to a master login. An admin must first turn on contained databases on both the server and the target database. This keeps the bot credentials scoped to that single database rather than the whole server.
After creating the user, you assign a fixed database role to control what the bot can do. Every role, including the default public role, already has the base permission to connect. You cannot change the permissions that belong to SQL Server fixed database roles. Instead, you pick the role that matches the exact task your bot needs to run.
Choosing SQL Server roles for AI bots
If your bot only answers questions from existing records, assign it to the reader role. Members of this fixed reader role can read all data from all user tables and views. If the bot must also block writes for safety, you can add the deny writer role. The deny writer role stops the user from adding, changing, or deleting any table data.
Some teams need their bot to insert support tickets or update order rows. The fixed writer role lets a user add, delete, or change data in all user tables. Never assign the database owner role to an automated bot or conversational agent. A database owner can run all maintenance tasks and can even drop the entire database.
Kubernetes API Server Access + Copilot Security Implications: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Where native database roles fall short in flight
Native SQL Server roles do a great job of deciding who can log into the database. They also set a hard ceiling on whether a user can read or write tables. Still, fixed roles apply across every user table and view inside that database at once. A reader role lets the bot read sensitive columns right alongside public product rows.
Static database roles also cannot inspect what individual queries do while they run in flight. Once the bot logs in, the database trusts every valid query that matches the role permissions. If a user tricks the bot into asking for all customer rows, the reader role allows it. Native roles also do not rewrite sensitive fields like emails, phone numbers, card numbers, or national IDs in results.
Finally, sharing a single database user across a bot platform makes audit logs hard to read. Every query arrives under the same SQL user name. Security teams can see that a query ran, but not which end user or prompt triggered it. Teams need runtime controls that check queries in flight and mask matched fields in results.
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 master login or a contained user for a SQL Server bot?
Use a contained database user if the bot only needs one or a few databases. This avoids creating a login in the master database and keeps access scoped tightly. Remember that an admin must first enable contained databases on the server and database.
Does the SQL Server reader role hide sensitive columns from my bot?
No, members of the fixed reader role can read all data from all user tables and views. You cannot alter the permissions built into fixed database roles. To protect fields like emails or card numbers, you need separate column permissions or runtime data masking.
How do I stop a bot user from changing data by mistake?
Assign the bot to the fixed reader role and add it to the deny writer role. The deny writer role blocks the user from adding, modifying, or deleting data in user tables. Also make sure you never grant the database owner role to a bot.
Related guides
In this post