How to Configure Copilot Studio Bigquery MCP Server

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a copilot studio bigquery mcp server by linking an agent to Google Cloud. You must give that server a scoped cloud role with read rights. Most teams start by creating a basic service account in their cloud project. They attach that account to the server so the agent can run queries. If you grant broad roles, the agent can read every table in your project. It might also change data or run huge queries that drive up cloud bills. How a copilot studio bigquery mcp server works The Mode

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 bigquery mcp server by linking an agent to Google Cloud. You must give that server a scoped cloud role with read rights. Most teams start by creating a basic service account in their cloud project. They attach that account to the server so the agent can run queries. If you grant broad roles, the agent can read every table in your project. It might also change data or run huge queries that drive up cloud bills.

How a copilot studio bigquery mcp server works

The Model Context Protocol gives AI agents a standard way to use outside tools. When a user asks a question, the agent picks the right tool to run. The agent sends a request to the server, which translates it into a query. Then the server runs that query against your cloud tables and returns the rows.

To make this work, the server needs a valid cloud identity and key. Teams create a dedicated account in their cloud console for this task. Next, they grant that account permission to run jobs and read specific datasets. Vendor guides for MCP database tools advise using a read-only database user. A read-only role stops the agent from altering or dropping tables by mistake.

After you set up the role, you register the server endpoint with your agent. The agent reads the list of tools and table schemas from the server. Now the agent knows how to write queries that match your data columns. Each time a user prompts the agent, it uses that same cloud role.

What native cloud roles solve and what they miss

Native cloud roles do a good job of controlling who can log in. They also define which datasets and tables a service account can read. If an agent tries to query a restricted dataset, the cloud platform blocks it. You can also set project quotas to cap the total bytes scanned per day.

Still, native cloud setup only checks permissions before the query runs. It does not check what individual statements or requests do in flight. Once a table is open to the role, the agent can read every row by default. If a table holds emails, phone numbers, or card numbers, the agent pulls them. Those raw fields go straight into the model prompt and the user chat.

BigQuery IAM + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Shared service accounts also make auditing very hard for security teams. When twenty people use the same agent, every query shows the same account name. Your cloud logs will not show which person asked the question in chat. You also cannot require a human to approve a risky query right before execution. Teams need a way to inspect queries and mask sensitive fields in results.

Steps to lock down agent database queries

Start by scoping your cloud role to the smallest set of tables possible. Never reuse an admin role or a broad data pipeline role for an agent. Create a fresh role that only has rights to read specific views or tables. Keep write, update, and delete permissions off that role at all times.

Next, look at the columns inside the tables you plan to expose. Check for personal data like national IDs, emails, phone numbers, or card numbers. If the agent does not need those columns, leave them out of the view. You should also review the server code before you connect it to your data. Only run servers from sources that your security team has checked and approved.

Govern statements and responses across databases and APIs

Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev runs beside each supported service as an open-source proxy, a sidecar for your database or API.

First, hoop.dev guardrails block banned queries before they run. Then hoop.dev data masking rewrites matched sensitive values in responses in memory. The audit trail records every statement and verdict per resource. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

What permissions should a BigQuery MCP server role have?

Grant the role permission to run query jobs and read only specific datasets. Always use a read-only role so the agent cannot change or delete table rows.

Can native cloud roles hide sensitive fields in query results?

Basic table roles allow or block access to whole tables or pre-built views. They do not rewrite matched fields like emails or card numbers in flight.

Why do shared agent service accounts create audit gaps?

Every prompt from every chat user runs under the same cloud service account. Your database logs record the server identity instead of the person who asked.

The query runs. The emails stay hidden.