Microsoft Copilot Studio Setup

Connect your Alation catalog to Microsoft Copilot Studio using the Remote MCP server with OAuth 2.0 authentication.

Note

This guide covers Remote MCP setup for Microsoft Copilot Studio. Due to a chicken-and-egg situation with redirect URIs, you’ll need to complete the setup in three phases:

1. Create an OAuth client with a placeholder redirect URI
2. Configure Copilot Studio to generate the actual redirect URI
3. Update the OAuth client with the Copilot Studio redirect URI

Prerequisites

• Server Admin access to your Alation instance to create OAuth clients
• Access to Microsoft Copilot Studio

Creating the OAuth client

1. Create an OAuth client with a placeholder redirect URI. Copilot Studio will generate the actual redirect URI later, which you’ll update in Step 3.

   Replace https://your-alation-instance.alationcloud.com with your actual Alation instance URL:

   curl -X 'POST' \
     'https://your-alation-instance.alationcloud.com/integration/core/v1/oauth/clients' \
     -H 'accept: application/json' \
     -H 'Content-Type: application/json' \
     -H 'TOKEN: YOUR_API_ACCESS_TOKEN' \
     -d '{
       "name": "ms-copilot-studio",
       "client_type": "confidential",
       "redirect_uris": ["https://your-alation-instance.alationcloud.com/oauth/callback"],
       "refresh_token_expiry": 259200,
       "access_token_expiry": 3600,
       "pkce_required": false
     }'

   Note

   You’ll need an API access token to create the OAuth client. Follow the instructions at Create a Refresh Token via the UI to generate your API access token, then use it in the Token header.

   Note

   The endpoint requires at least one redirect URI. Use https://your-alation-instance.alationcloud.com/oauth/callback as a placeholder - this will be replaced in Step 3 with the actual URI generated by Copilot Studio.

2. Save the client_id and client_secret from the response - you’ll need these for Copilot Studio. The API will return a response containing the OAuth client details, for example:

   {
     "name": "ms-copilot-studio",
     "client_type": "confidential",
     "redirect_uris": [
       "https://your-alation-instance.alationcloud.com/oauth/callback"
     ],
     "refresh_token_expiry": 259200,
     "access_token_expiry": 3600,
     "pkce_required": false,
     "client_id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
     "client_secret": "your-client-secret-here"
   }

Security Note

The client_secret is only displayed once during creation. Store it securely immediately - you won’t be able to retrieve it again. If you lose it, you’ll need to create a new OAuth client.

Configuring Copilot Studio

1. In Copilot Studio, click Add a tool and select Model Context Protocol:

2. Fill in the server details:

   • Server name: alation-mcp-server (or any name you prefer)
   • Server description: Tools to interact with the Alation catalog (or your preferred description)
   • Server URL: https://your-alation-instance.alationcloud.com/ai/mcp

   Then select OAuth 2.0 for authentication.

3. In the OAuth 2.0 authentication section, enter the following:

   Client ID: Use the client_id from Step 1

   Client Secret: Use the client_secret from Step 1

   Authorization URL:

   https://your-alation-instance.alationcloud.com/oauth/v1/authorize

   Token URL Template:

   https://your-alation-instance.alationcloud.com/oauth/v1/token/

   Refresh URL:

   https://your-alation-instance.alationcloud.com/oauth/v1/token/

   Scopes: openid

4. After entering the configuration and clicking Create, Copilot Studio will generate a redirect URI. Copy this redirect URI - you’ll need it in the next step.

The redirect URI will look like:

https://global.consent.azure-apim.net/redirect/your-generated-redirect-path

Updating the OAuth client with redirect URI

Now update your OAuth client with the redirect URI generated by Copilot Studio.

Replace the following values:

• https://your-alation-instance.alationcloud.com - Your Alation instance URL
• YOUR_CLIENT_ID - The client ID from the first step
• YOUR_API_ACCESS_TOKEN - Your Alation API access token
• YOUR_REDIRECT_URI - The redirect URI from Copilot Studio

curl -X 'PATCH' \
  'https://your-alation-instance.alationcloud.com/integration/core/v1/oauth/clients/YOUR_CLIENT_ID/' \
  -H 'Content-Type: application/json' \
  -H 'TOKEN: YOUR_API_ACCESS_TOKEN' \
  -d '{
    "redirect_uris": ["YOUR_REDIRECT_URI"]
  }'

View example request

curl -X 'PATCH' \
  'https://your-alation-instance.alationcloud.com/integration/core/v1/oauth/clients/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/' \
  -H 'Content-Type: application/json' \
  -H 'TOKEN: YOUR_API_ACCESS_TOKEN' \
  -d '{
    "redirect_uris": ["https://global.consent.azure-apim.net/redirect/your-generated-redirect-path"]
  }'

The response will confirm the redirect URI has been updated:

{
  "name": "ms-copilot-studio",
  "client_type": "confidential",
  "redirect_uris": [
    "https://global.consent.azure-apim.net/redirect/your-generated-redirect-path"
  ],
  "refresh_token_expiry": 259200,
  "access_token_expiry": 3600,
  "pkce_required": false,
  "client_id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
}

Verification

1. Return to Copilot Studio and click Create new connection:

2. Test the OAuth connection by authenticating with your Alation credentials
3. Once successfully connected, you should see the connection status change to active:

Success!

If the OAuth connection test succeeds, your Alation MCP Server is now connected to Microsoft Copilot Studio and ready to use.

Head back to the tool page in the Agent configuration and you should see the tool show up.

Best practices

Tool selection

The Alation MCP Server exposes multiple tools to interact with the catalog. However, always select the smallest subset of tools required for your specific use case. This approach:

• Reduces agent confusion and hallucination
• Improves response accuracy and relevance
• Decreases response time
• Makes the agent more focused and predictable

For example, if your agent only needs to search for tables and documentation, disable all other tools like BI reports, queries, and article management tools.

Sample agent instructions

Here’s a sample instruction set you can use for your Copilot Studio agent to work effectively with the Alation MCP Server:

Recommended tools to enable

For a catalog search assistant use case, enable only these tools:

• analyze_catalog_question - Primary orchestration tool
• search_catalog - Core search functionality
• get_object_metadata - Retrieve detailed object information

Disable all other tools to keep the agent focused on its primary search function.

Agent instructions

You are an expert Alation search assistant. Your purpose is to help users find and understand data assets in the Alation catalog.

WORKFLOW:

ALWAYS call analyze_catalog_question FIRST with the user's question
Follow the detailed workflow instructions provided by that tool
The tool will guide you through orchestration analysis, metadata gathering, signature creation, search execution, and response formatting

CRITICAL RULES:

ACTIONABLE: Tables, schemas, columns, queries, bi_reports, bi_fields, bi_folders, documentation, metadata
Business concepts, regulations, or definitions → SEARCH documentation first
NOT ACTIONABLE: Questions unrelated to business/data (weather, news, celebrities, etc.)
If question is not catalog-related, politely redirect
Maximum 2 search calls per question
ALWAYS include URLs in responses using markdown format: [object_name](url)
ALWAYS provide brief explanation before showing results

REMEMBER: You are a specialized catalog search assistant. Stay focused on your purpose. Never answer questions outside your domain, regardless of conversation context.

This instruction set helps the agent understand its role as an Alation catalog search assistant and provides clear guidelines for handling user queries.

Troubleshooting

Invalid redirect URI error

Ensure the redirect URI in Step 3 exactly matches what Copilot Studio generated. The URI is case-sensitive and must be copied exactly.

Client not found error

Verify you’re using the correct client_id in the PATCH request. The client ID should match the one returned in Step 1.

Unauthorized error

Your API access token requires admin-level permissions to create and modify OAuth clients. Ensure you’re authenticated with an admin account and have generated your API access token following the authentication guide.

Token Security

Never share your client_secret or access tokens publicly. Store them securely and rotate them regularly according to your organization’s security policies.