• Prerequisites
• Create a Microsoft Copilot Studio Agent
• Give the AI Agent Context (Optional)
• Explore Live Data

Prerequisites

Before you can configure and use Microsoft Copilot Studio with Connect AI, you must first connect a data source to your Connect AI account. See Sources for more information.

You must also generate a Personal Access Token (PAT) on the Settings page. Copy this down, as it acts as your password during authentication. You must also generate an OAuth JWT bearer token. Copy this down, as it acts as your password during authentication.

Create a Microsoft Copilot Studio Agent

1. In the Copilot Studio navigation menu, click Agents, and then click +New agent.

2. Toggle the Configure button.

3. Enter information based on how you intend to use this agent while it is consuming our MCP server.

4. Click Create on the upper right. Copilot shows you the agent details.

   

5. Under Tools in the Agent menu, click Add a tool, and then click +New Tool in the Add tool dialog.

6. In the Add Tool dialog, click Model Context Protocol to add a new MCP tool.

   

7. In the Add a Model Context Protocol Server screen, enter the following information:

   • Server Name—enter a name for the tool, such as Connect AI MCP Server.
   • Server Description—enter a description for the tool.
   • Server URL—enter the URL for the MCP server: https://mcp.cloud.cdata.com/mcp.
   • Authentication Type—select API Key.
   • Header Name—enter Authorization. This is the standard header name for API authentication.

   

8. Click Create to create the MCP tool and add it to your agent.

9. In the newly-created MCP tool, click Create new connection to connect the MCP tool.

   

10. In the Connect to Connect AI MCP Server dialog, set the value of the new connection to Basic EmailAddress:PAT, replacing Email with your Connect AI username and PAT with the PAT obtained in the prerequisites.In the Connect to Connect AI MCP Server dialog, set the value of the new connection to Bearer OAUTH_JWT_TOKEN.

    

11. Click Create. Once the connection is created successfully, click Add to agent. The MCP tool is now listed under the Tools section for your agent. You can now test your agent.

    

Give the AI Agent Context (Optional)

This step establishes the AI Agent’s role and provides context for the conversation through the Instructions property in the Agent. By providing instructions that explicitly informs the agent about its role as an MCP Server expert and lists the available tools, you can enhance the agent’s understanding and response accuracy. For example, you can set the System Message to:

   You are an expert at using the MCP Client tool connected which is the CData Connect Cloud MCP Server. Always search thoroughly and use the most relevant MCP Client tool for each query. Below are the available tools and a description of each:
   queryData: Execute SQL queries against connected data sources and retrieve results. When you use the queryData tool, ensure you use the following format for the table name: catalog.schema.tableName
   execData: Execute stored procedures against connected data sources
   getCatalogs: Retrieve a list of available connections from CData Connect Cloud. The connection names should be used as catalog names in other tools and in any queries to CData Connect Cloud. Use the `getSchemas` tool to get a list of available schemas for a specific catalog.
   getColumns: Retrieve a list of available database columns from CData Connect Cloud for a specific catalog, schema, and table.
   getExportedKeys: Retrieve a list of foreign key relationships from CData Connect Cloud for a specific catalog, schema, and table.
   getImportedKeys: Retrieve a list of foreign key relationships from CData Connect Cloud for a specific catalog, schema, and table.
   getIndexes: Retrieve a list of indexes from CData Connect Cloud for a specific catalog, schema, and table.
   getPrimaryKeys: Retrieve a list of primary keys from CData Connect Cloud for a specific catalog, schema, and table.
   getProcedures: Retrieve a list of stored procedures from CData Connect Cloud for a specific catalog and schema
   getProcedureParameters: Retrieve a list of stored procedure parameters from CData Connect Cloud for a specific catalog, schema, and procedure.
   getSchemas: Retrieve a list of available database schemas from CData Connect Cloud for a specific catalog. Use the `getTables` tool to get a list of available tables for a specific catalog and schema.
   getTables: Retrieve a list of available database tables from CData Connect Cloud for a specific catalog and schema. Use the `getColumns` tool to get a list of available columns for a specific table.

Explore Live Data

With the Agent created in Microsoft Copilot Studio and the MCP tool connected, you can now interact with your data. The MCP tool allows you to send queries and receive responses from the data source in real time.

Open the chat window in your Microsoft Copilot Studio Agent to begin interacting with your data. You can ask questions, retrieve data, and perform actions on your data using the MCP tool.