Connect Solid MCP to Microsoft Copilot Studio

This guide walks you through connecting the Solid MCP server to a Microsoft Copilot Studio agent so it can translate natural-language questions into SQL against your data warehouse.

Two connection paths are available:

No bridge infrastructure is required on your side for native MCP.

Before you start

Gather these from your Solid admin before opening Copilot Studio:

Step 1: Set up your Copilot Studio environment

Copilot Studio requires a non-Default Power Platform environment with Dataverse provisioned and the right role assignments before you can add MCP tools or collaborate on the agent.

Things that commonly go wrong:

• "Dataverse isn't set up" in Studio almost always means the user has no org role or environment access — not that the database is missing.
• Security group membership alone isn't enough — the user must also be Enabled under that environment's Users with Environment Maker (or System Administrator).
• Only Power Platform / env admins can provision Dataverse or grant those roles.
• Chat share ≠ co-edit. Chat share only lets people use the agent (e.g. connections page). For editing, share with Editor / collaborative authoring (use classic Share if needed), ensure Environment Maker, and have them open Studio → that environment → Agents — not the chat link.
• Don't rely on the Default environment for shared authoring — create or move the agent into a governed sandbox or custom environment once Dataverse and roles are in place.

Prerequisites

1a: Choose or create a governed environment

1. In the Power Platform admin center, create a new environment (or use an existing non-Default one)
2. Set Dataverse to Yes when creating the environment — required for MCP tools and co-edit
3. If "Dataverse isn't set up" appears in Studio, check the user's org role and environment access first — the database is almost never actually missing

1b: Grant the right roles

Group membership in a security group that gates the environment is not sufficient on its own. Each user also needs:

1. In the Power Platform admin center, open the environment → Users
2. Confirm the user appears there and has Environment Maker (or System Administrator) role
3. If the environment is restricted to a security group, verify the user is both in the group and Enabled under the environment's Users list — both conditions must be true

Only Power Platform admins or environment admins can provision Dataverse or assign these roles.

1c: Share the agent for editing (not just chat)

Chat share and co-edit are different. Chat share only lets users interact with the agent (e.g. the connections page). It does not grant authoring access.

1. Open the agent in Copilot Studio
2. Share with Editor role (collaborative authoring) — use the classic Share option if the modern flow doesn't show an Editor role
3. Confirm the user has Environment Maker in that environment (Step 1b)
4. Have them open Copilot Studio → select that environment → Agents — not the chat link

Do not send co-editors a chat link. They must open Studio directly, select the correct environment, and navigate to Agents.

Co-edit troubleshooting

Step 2: Add Solid as a tool (native MCP)

Skip to Step 3 if your environment does not support native MCP tools.

1. In Copilot Studio, open your agent and go to Tools
2. Select Add a tool → New tool → Model Context Protocol
3. Fill in the server details:

4. Under Authentication, select API key and enter your Solid management key — this is sent as the x-solid-management-key header on every request. There is no token exchange step.
5. Click Create, then Add to agent

The Solid tools (text2sql, glossary_search, semantic_model_qa, specific_asset_information_tool) will appear in your agent's tool list.

Step 3: Add Solid as a REST connector (OpenAPI)

Use this path only if your agent cannot consume native MCP / SSE.

1. Ask your Solid admin for the OpenAPI spec for the REST-to-MCP bridge and the bridge base URL (e.g. https://…azurewebsites.net/api/mcp)
2. In Copilot Studio, go to Connections → Custom connectors → New custom connector → Import from OpenAPI file
3. Import the spec — the Azure Function host key (code parameter) is pre-filled
4. Under Authentication, use API key and map your Solid management key to the management_key field in the connector
5. Add the connector to your agent as an action

For the REST path, include management_key in every request body alongside the tool fields:

{
  "management_key": "<your-solid-management-key>",
  "question": "What were total sales last month?",
  "semantic_layer_ids": ["your-model-uuid"]
}

No Bearer token and no separate auth call are needed.

Step 4: Configure your agent

Agent instructions

Tell the agent when to use Solid and how to handle the SQL it gets back. Add instructions like:

For any data, analytics, or reporting question, use the Solid text2sql tool to generate SQL.
Pass the user's question exactly as asked. Always include semantic_layer_ids: ["<your-model-uuid>"].
Show the user the generated SQL and ask if they want to run it.
If they confirm, use the warehouse connector action to execute it and return the results.
If they say the results are wrong, call submit_text2sql_feedback with sentiment: negative
and the generation_id from the text2sql response.

Adding a warehouse runner (to execute the SQL)

Solid generates SQL — it does not execute it. To run the query and return results to the user, you need a second connector in Copilot Studio that connects to your warehouse.

For Snowflake:

1. In Copilot Studio, go to Connections → Add a connection
2. Search for the Snowflake connector (built-in Power Platform connector)
3. Enter your Snowflake account URL, database, warehouse, and credentials
4. Add an Execute SQL action to your agent, wired to this connection
5. In your agent's flow, pass the SQL returned by text2sql as the input to this action

For other warehouses (BigQuery, Databricks, Redshift):

Use a Power Automate flow as the execution layer:

1. Create a Power Automate flow with an HTTP trigger that accepts a SQL string
2. Inside the flow, use the appropriate connector (BigQuery, Databricks JDBC via Azure Function, etc.) to run the query
3. In Copilot Studio, add the flow as an action in your agent
4. Pass the SQL from text2sql to the flow, then return the results to the user

If you only want to surface the SQL (let the user copy and run it themselves), skip the warehouse runner entirely — just show the returned SQL in the agent's response.

Semantic layer IDs

Always pass your semantic layer UUID(s) in the semantic_layer_ids parameter so the tool routes to the right model:

{
  "question": "Show total revenue by region for Q1 2025",
  "semantic_layer_ids": ["a1b2c3d4-e5f6-7890-abcd-ef1234567890"]
}

Not sure of your model IDs? Ask your Solid admin, or use the get_semantic_model tool to look up details by UUID.

Web search

Consider limiting or disabling web search for data questions so the agent routes to Solid rather than trying to answer from the web.

Step 5: Test the connection

1. In Copilot Studio, open the Test panel and enable Show activity map when testing
2. Ask a data question, e.g. "What were total sales last month?"
3. In the activity map, confirm:
   • The text2sql tool is called
   • A SQL query is returned in the tool response
   • No 401 Unauthorized errors appear

If the tool is called but returns unexpected SQL, check the Debugging section below.

Debugging

Available Solid tools

Once connected, your agent has access to these tools:

See Getting Started with the Solid MCP Server for full parameter reference and error codes for each tool.