How to Configure Copilot Studio ClickHouse Credentials
You set up copilot studio clickhouse credentials by making a scoped SQL user and storing its secret in your agent settings. This lets your AI agent read database tables while blocking unwanted data changes. Many teams start by sharing one broad admin login across all their bots. That shortcut makes setup fast, but it creates huge risks. If a user prompt tricks the bot, a broad account can read private tables or drop data. Scoping the database account before the bot runs is the first step to safe
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 clickhouse credentials by making a scoped SQL user and storing its secret in your agent settings. This lets your AI agent read database tables while blocking unwanted data changes. Many teams start by sharing one broad admin login across all their bots. That shortcut makes setup fast, but it creates huge risks. If a user prompt tricks the bot, a broad account can read private tables or drop data. Scoping the database account before the bot runs is the first step to safe operations.
How copilot studio clickhouse credentials work with SQL roles
ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn this on, the server needs a storage folder for access files. You also need at least one admin user with access management turned on. Once that is ready, an admin can create a fresh database user for the bot. In ClickHouse, a user can only grant privileges that they already hold themselves. Making a new bot account requires the privilege to create users.
When you create the account, you pick how it logs in. You can also limit which hosts the account may connect from. Also, you can attach default roles and per-user settings right in that same step. For an AI agent, you should only grant read privileges on the exact tables it needs. ClickHouse even lets you grant read rights on single columns of a table. If you grant read rights on just two columns, the bot can query only those two columns. A query that asks for a third column will fail right away. A wildcard query that asks for all columns will also fail and return no data. That wildcard query only works if the table holds just the granted columns. Recent database versions also let you grant privileges across tables that share a name prefix. Once the database user is ready, you put those login details into your agent tool action.
What native credentials solve and what they leave open
Native database roles and column grants solve the first part of the security puzzle. They decide who can log in and which tables or columns remain off limits. Host limits also stop someone from using a stolen password from an unknown network. This stops an agent from dropping tables or reading whole tables it has no business touching.
Still, static database logins leave a big operational gap during live agent runs. The database only checks the shared bot account when the session starts. Every person who chats with the agent shares that exact same database identity. Database logs will show the bot account running queries, not the human who asked the question. Also, column grants are all or nothing. If a bot needs a column to answer support questions, it gets every value in that column. Native grants do not rewrite sensitive strings like emails, phone numbers, card numbers, or national IDs inside query results. Finally, storing long-lived passwords inside external agent platforms means another place to rotate secrets when staff change.
Ephemeral Credentials + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Steps to keep agent database queries safe in production
Start by auditing which tables your AI workflow actually needs to answer user questions. Never hand an agent an admin account or a role that can alter data. Create a dedicated role for each bot workflow instead of sharing one login across ten bots. Use column grants so the role only touches safe fields in each table. Set host limits on the ClickHouse user so connections only come from known network addresses. Review query logs often to see what SQL statements the agent generates during real chats. If a table holds emails or card numbers, plan how to mask those fields in results.
Add runtime guardrails and masking beside ClickHouse
Database grants and network rules control who can reach ClickHouse. Even so, an allowed client or agent can send an alter mutation that deletes rows or read raw personal data. In practice, hoop.dev runs beside the server as a sidecar for your ClickHouse and checks native TCP queries with no change to your drivers.
With hoop.dev ClickHouse support, guardrails refuse banned queries before they run, and data masking rewrites matched string columns in results. The audit trail logs which entity types were masked and how many without storing raw values. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.
Frequently asked questions
Can I restrict an AI agent to specific ClickHouse columns?
Yes, ClickHouse lets you grant select privileges on individual columns within a table. The agent can then query only those named columns, and broad wildcard queries will return no data.
What do I need to create a new SQL user in ClickHouse?
You need SQL access control turned on with a configured storage directory on the server. Your admin account must also hold the create user privilege and the privileges you plan to grant.
Why do native database accounts struggle with AI agent audit logs?
An AI agent usually connects to the database using one shared service account for all users. The database log records the shared bot login rather than the specific person who triggered the query.
Related guides
In this post