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.

Before you start

You will need the following. Contact your Solid admin if anything is missing.

Authentication note: The Copilot Studio MCP wizard also shows an OAuth 2.0 option. The header API key (x-solid-management-key) is the standard path when DCR OAuth is not set up. Use it unless your admin tells you otherwise.

Step 1: Set up your Copilot Studio environment

Copilot Studio requires a non-Default Power Platform environment with Dataverse provisioned and the correct role assignments.

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-authoring
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

Each user needs both of these — group membership alone is not enough:

1. In the Power Platform admin center, open the environment → Users
2. Confirm the user is listed there and has Environment Maker (or System Administrator) role
3. If the environment is restricted to a security group, verify the user is in the group and Enabled in the 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 co-authoring

There is no "Editor" role in the share dialog — co-authoring in Copilot Studio is granted via Environment Maker access plus a Copilot Studio license.

1. Ensure the co-author has Environment Maker in the environment (Step 1b) and a Copilot Studio license
2. Role propagation takes roughly 10 minutes — if access doesn't work immediately, wait and retry
3. Have them open Copilot Studio → select that environment → Agents — not a chat link

Chat share ≠ co-authoring. Chat share only lets people use the agent (e.g. the connections page). Co-authoring requires the environment role and license above.

Co-authoring troubleshooting

Common mistakes: "Dataverse isn't set up" almost always means missing org role/access, not a missing database. Security group membership alone doesn't grant env access — the user must also be Enabled in the Users list. Don't use the Default environment for shared authoring; create or move the agent into a governed custom environment once Dataverse and roles are in place.

Step 2: Add Solid as a tool

1. Open your agent in Copilot Studio 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 fill in:

5. Click Add (not Create)
6. On the next screen, click Select a connection, then Create connection — it will ask for the management key again. Enter it and click Add.

All 6 Solid tools will now appear in your agent's tool list. Click Allow all to enable them.

The 6 tools are: text2sql, glossary_search, semantic_model_qa, specific_asset_information_tool, get_semantic_model, submit_text2sql_feedback.

Step 3: Configure your agent

Agent instructions

Open your agent's Instructions and add:

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.

Web search: New agents have Knowledge → Search all websites turned on by default. Turn it off (or scope it to specific sources) for data questions so the agent routes to Solid instead of searching the web.

Finding your semantic layer ID

The UUID is visible in the Solid UI under Semantic Models → [model name] → Settings. It looks like a1b2c3d4-e5f6-7890-abcd-ef1234567890. You can also ask your Solid admin. Pass it in every text2sql call:

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

Adding a warehouse runner (to execute the SQL)

Solid generates SQL — it does not execute it. To return results to the user, you need a warehouse connector.

For Snowflake:

1. In Copilot Studio, go to Connections → Add a connection and search for the Snowflake connector
2. Enter your Snowflake account URL, database, warehouse, and credentials
3. Add an Execute SQL action to your agent and wire it to this connection
4. Pass the SQL from text2sql as the input

For other warehouses (BigQuery, Databricks, Redshift):
Use a Power Automate flow with an HTTP trigger that accepts a SQL string, runs it via the appropriate connector, and returns the results.

To surface SQL without running it — let the user copy and run it themselves — skip the warehouse runner and show the returned SQL directly.

Step 4: Test the connection

1. In Copilot Studio, click Preview (top right) to open the test chat
2. Ask a data question, e.g. "What were total sales last month?"
3. Confirm the following in the activity panel:
   • The text2sql tool is called
   • A SQL query is returned in the tool response
   • No 401 Unauthorized errors appear
4. If SQL is returned but looks wrong, see the Debugging section below

Debugging

Available Solid tools

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