How to Configure Copilot Studio SQL Server Credentials
You set up copilot studio sql server credentials by creating a database user and assigning a role. Then you enter those login details into your agent tool settings. Many teams start by reusing a broad admin login to make the agent work fast. That shortcut creates high risk because an AI agent can run bad queries or read private tables. Scoping the database account before the agent logs in keeps your data safe. How copilot studio sql server credentials work in practice In SQL Server, a databas
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 copilot studio sql server credentials by creating a database user and assigning a role. Then you enter those login details into your agent tool settings. Many teams start by reusing a broad admin login to make the agent work fast. That shortcut creates high risk because an AI agent can run bad queries or read private tables. Scoping the database account before the agent logs in keeps your data safe.
How copilot studio sql server credentials work in practice
In SQL Server, a database user is the identity of a login inside a database. When an account needs to reach many databases on one instance, teams create a login in the master database. Then they map a SQL user to that login.
If your agent only needs one database, you can use a contained database user instead. A contained user has its own password and does not tie to a login in master. An admin must first turn on contained databases on the server and on the target database. SQL Server supports thirteen types of database users for different login setups.
Once you create the user, you must grant it the right role. SQL Server includes fixed database roles whose built-in permissions cannot be changed. Every role, including the public role, grants the base right to log in to the database.
If the agent only answers questions from data, add the user to the read-only role. Members of this fixed reader role can read all data from all user tables and views. You can also pair it with the deny writer role. That role stops the account from adding, changing, or deleting any table rows.
Never put agent logins in the database owner role. The owner role allows all maintenance tasks and can even drop the entire database. Only assign the writer role if the bot must insert, update, or delete records.
Why native roles are not enough for agent queries
Setting up a scoped database user solves the first step of security. It decides who can log in and which high-level actions the database allows. If an agent uses a read-only role, it cannot drop tables or wipe out rows.
Ephemeral Credentials + Kubernetes API Server Access: 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 open in production. A fixed reader role lets the user read all data across every user table and view. You cannot change the permissions inside a fixed database role.
Once the agent logs in, the database does not check the context of each query in flight. An agent with reader rights can still pull sensitive columns from customer tables. Those results often hold emails, phone numbers, card numbers, or national IDs. Without field masking that rewrites matched fields in results, those raw values flow straight into the chat.
If your workflow needs the writer role, the risk grows even larger. The fixed writer role allows changes and deletes across all user tables. A confused prompt could cause the agent to update or delete the wrong rows. Native logins also mean the app holds static passwords that teams must rotate by hand. Database roles govern the account at login time, not what individual statements do at runtime.
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 standard login or a contained user for my agent?
Use a contained database user if the agent only needs one or a few databases. It keeps the password inside that database instead of the master database. Use a standard login in master only when the account must span many databases.
Can I customize the permissions of a fixed SQL Server role?
No, SQL Server does not let you change the permissions assigned to fixed database roles. The built-in reader role always grants read access to all user tables and views. To restrict specific tables, you must build custom roles or enforce rules at the query level.
How do I stop an agent from changing data in SQL Server?
Add the database user to the fixed reader role so it can only select data. You can also add the user to the fixed deny writer role. That role blocks the user from adding, changing, or deleting data in any user table.
Related guides
In this post