How to Configure Copilot Studio ClickHouse MCP Server

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a copilot studio clickhouse mcp server by pairing an agent action with a database role. First, create a restricted database user and give the MCP server its login key. Most teams start with a shared admin account to test the link fast. That broad role lets the AI agent read every table or change live data. A safe setup needs strict database rules before any agent sends a query. How a copilot studio clickhouse mcp server works The Model Context Protocol gives AI agents a standard wa

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 clickhouse mcp server by pairing an agent action with a database role. First, create a restricted database user and give the MCP server its login key. Most teams start with a shared admin account to test the link fast. That broad role lets the AI agent read every table or change live data. A safe setup needs strict database rules before any agent sends a query.

How a copilot studio clickhouse mcp server works

The Model Context Protocol gives AI agents a standard way to query databases. In this setup, Copilot Studio sends a prompt to the MCP server. The MCP server turns that prompt into a SQL query for ClickHouse. ClickHouse then checks the database user and runs the statement. The database sends the rows back to the MCP server and the agent.

ClickHouse manages users, roles, row policies, settings profiles, and quotas through SQL. To turn on SQL access control, an admin sets a storage directory on the server. At least one admin account also needs user management rights turned on. When you create the MCP user, you set its login method and default roles. You can also limit which network hosts the user can log in from. A user can only grant rights that they already hold themselves.

Scoping database roles and column rights

A safe agent setup starts with a read-only database user. This stops the agent from running queries that change or delete rows. In ClickHouse, you can grant read rights on single columns in a table. If you grant read access on two columns, the agent can query only those two. A query for any other column in that table will fail right away. A full wildcard query across all columns will also fail and return no data. It only works if the table holds just the allowed columns. Since version 24.10, admins can also grant rights across tables that share a name prefix.

Team admins can add row policies and quotas to the MCP role as well. Row policies filter which rows the agent can read during a session. Quotas cap how much work the database user can do over time. Together, these native controls limit the blast radius of a bad agent prompt.

ClickHouse Access Management + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native database roles leave open

Native ClickHouse roles decide who logs in and which columns they can read. Still, static grants do not check what a query does in flight. Once a column is open, the MCP server pulls every raw value in that column. If an allowed column holds emails, phone numbers, card numbers, or national IDs, raw data flows out. Dynamic data masking solves this by rewriting matched fields in query results.

Static database credentials also create a key management risk for the team. The MCP server needs a stored password or key to log in to ClickHouse. Every user who talks to the Copilot Studio agent shares that one database identity. In the ClickHouse logs, every query looks like it came from the same MCP account. You cannot see which person triggered a heavy query or asked for sensitive rows. Teams also lack a step to pause a risky query for human review.

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 does a wildcard query fail for a restricted ClickHouse MCP user?

When you grant read rights on specific columns, ClickHouse blocks wildcard queries on that table. The query returns no data unless the table contains only the granted columns. The agent must name the allowed columns in its query.

How should we set up the ClickHouse user for an MCP server?

Create a dedicated read-only user with SQL access control and limit its allowed network hosts. Grant read rights only on the specific tables and columns the agent needs. Add quotas and settings profiles to cap resource use.

Can ClickHouse column grants hide sensitive fields inside a text column?

No, column grants are all or nothing for each column you open. If a column holds emails or card numbers inside text, native grants return the full text. You need runtime data masking to rewrite matched fields in the results.

The query runs. The emails stay hidden.