How to Configure Copilot Studio ClickHouse Connect
To set up a copilot studio clickhouse connect workflow, create a read-only database user first. Then link your agent action to the analytical store using that restricted account. Many teams rush this step and share a single admin account across all bots. Broad permissions let an AI agent read private tables or run heavy queries by mistake. How a native copilot studio clickhouse connect setup works A safe setup starts inside the database before you build any agent workflows. ClickHouse manages
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 copilot studio clickhouse connect workflow, create a read-only database user first. Then link your agent action to the analytical store using that restricted account. Many teams rush this step and share a single admin account across all bots. Broad permissions let an AI agent read private tables or run heavy queries by mistake.
How a native copilot studio clickhouse connect setup works
A safe setup starts inside the database before you build any agent workflows. ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn on SQL access control, an admin sets a storage directory on the server. At least one database user also needs user management privileges enabled.
Once SQL access control is active, you can create a dedicated user for the bot. A user who runs the create statement needs the privilege to make new users. In ClickHouse, an admin can only grant privileges that they hold themselves. When you create the bot account, you set its login method and allowed client hosts. You also assign default roles and specific user settings for that bot account.
Next, you limit what the bot can read inside your analytical tables. ClickHouse lets admins grant read rights on specific columns instead of the whole table. The bot can then run queries that ask for those allowed columns only. If the bot runs a wildcard query for all columns, the database refuses it. That wildcard query returns no data unless the table holds only the granted columns. Recent database versions also let admins grant read rights across tables using a name prefix.
After the database role is ready, you link the agent in your studio workspace. The studio bot calls an external action or API service that runs the client driver. That backend service holds the database host address, port, and user login details. When a person asks the bot a question, the bot triggers this external action. The driver opens a session, sends the query, and returns the rows to the chat.
What native roles solve and what they leave open
Native database roles and column grants solve the basic login problem well. They make sure the bot logs in from a known host with read-only rights. Column grants also stop the bot from pulling restricted columns into a chat reply. Row policies and quotas help limit how much data the bot can scan at once.
ClickHouse Access Management + Copilot Security Implications: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, static database roles only check who logs in when a session starts. Every chat user who talks to the bot shares that same backend database account. The database logs show the bot account name, not the person asking the question. Security teams cannot tell which employee triggered a specific query from the database logs alone.
Column grants also work as an all-or-nothing rule on each column. Sometimes a bot needs to read a text column that holds mixed customer notes. That column might include emails, phone numbers, card numbers, or national IDs inside the text. Native grants cannot rewrite matched fields in results while letting the rest of the text through.
Static credentials stored in an action backend also create key rotation work for engineers. If an admin needs to approve a one-off query, they must edit database grants by hand. Teams need a way to inspect individual queries and mask sensitive fields in flight.
Check ClickHouse queries and result columns on the wire
ClickHouse users and row policies decide who connects. They do not check each statement on the wire or redact sensitive string columns in query results. With hoop.dev, you run an open-source proxy, a sidecar for your ClickHouse. It reads ClickHouse's native TCP protocol before queries run while ClickHouse stays in charge of login and grants.
Multi-statement queries and table mutations are split and checked by hoop.dev guardrails. Banned actions are refused with a native ClickHouse error. On returned rows, hoop.dev data masking rewrites String and FixedString columns that match your rules. The audit trail logs each query, table, and verdict. See the MIT-licensed source on GitHub.
Frequently asked questions
Why did my bot wildcard query fail after we added column grants?
ClickHouse blocks wildcard queries when a user only has read rights on specific columns. Update the bot prompt or action logic to list only the allowed column names.
How do we restrict which servers the agent service can log in from?
When you create the database user in SQL, define the allowed client host rules. This stops the bot credentials from working if someone copies them to another machine.
Can a database user grant extra table rights to the bot account?
A database user can only grant privileges that they already hold on their own account. They also need explicit access management rights enabled to change roles or permissions.
Related guides
In this post