Snowflake’s Managed MCP Server: Giving AI Assistants a Governed Door Into Your Warehouse (Part 1)

This is Part 1 of a two-part series. Here I explain what MCP is, when you need Snowflake’s managed MCP server, and build a full working example on Snowflake. In Part 2: Building a Chat UI for Snowflake’s Managed MCP Server, I build a small web console that talks to the servers from this post.

TL;DR

• MCP (Model Context Protocol) is a contract for tools. A client like Claude, Cursor or ChatGPT asks a server “what tools do you have?” (tools/list) and then calls them (tools/call). That's most of it.
• Snowflake can host the MCP server for you. It’s a schema-level object (CREATE MCP SERVER) that exposes Cortex Agents, Cortex Analyst, Cortex Search, read-only SQL and your own UDFs and procedures as tools. You don't run a container, and access is just Snowflake RBAC.
• Expose an agent, not raw SQL. Snowflake’s own recommendation, and mine after testing: one Cortex Agent tool for business users, and a separate server with read-only SQL for engineers.
• Watch secondary roles with PATs. In my test, a role with no grant on a server could still list its tools, because the PAT had no role restriction and the user had DEFAULT_SECONDARY_ROLES = ALL (the default). Give PATs a ROLE_RESTRICTION, and keep OAUTH_USE_SECONDARY_ROLES at its default of NONE on OAuth integrations.
• Custom procedure tools must return one cell. A RETURNS TABLE procedure fails over MCP. Return a VARIANT instead.
• Auditing is free. Every MCP call shows up in query history under the real user and role, tagged with the MCP request ID.
• Everything below ran in a sandbox account on Oct 8, 2026. The outputs are real.

The Slack message that started this

Every Monday our ops lead used to post the same thing in the data channel: “Are the dashboards up to date? And can someone pull revenue by region for last quarter?”

Someone on the data team would check when the loads last ran, write a query, paste a screenshot, and answer three follow-ups. None of it was hard. All of it was interruptions.

The ops team already lives in Claude. So the question became: can I give their assistant a safe, governed way to ask Snowflake those questions itself, without handing out a service account with SELECT on everything and hoping for the best?

That’s exactly the gap the Snowflake-managed MCP server fills.

What MCP actually is (without the hype)

MCP is an open protocol for connecting AI applications to tools and data. Strip away the marketing and a remote MCP server is an HTTPS endpoint that speaks JSON-RPC and answers two questions:

1. tools/list: "What can you do?" The server returns each tool's name, a description written for the model, and a JSON Schema for its inputs.
2. tools/call: "Do this one, with these arguments." The server runs it and returns the result.

The model reads the descriptions, decides which tool fits the user’s question, and fills in the arguments. That’s why tool descriptions matter as much as the code behind them. They’re effectively prompts.

The win is standardization. Before MCP, every assistant needed its own plugin format. Now one server works in Claude, Cursor, ChatGPT, VS Code and anything else that speaks the protocol.

What Snowflake adds

You could always write your own MCP server in Python, deploy it somewhere, give it Snowflake credentials and wire up auth. I’ve done that. It works, and then you own the container, the secrets, the patching and the question of whose permissions the queries run with.

With the Snowflake-managed MCP server, the server is just another object in a schema:

• No infrastructure. Snowflake hosts the endpoint at https://<account>.snowflakecomputing.com/api/v2/databases/<db>/schemas/<schema>/mcp-servers/<name>.
• The user’s own identity. Clients sign in with Snowflake OAuth (or your IdP via External OAuth), so every tool call runs as the actual person. By default the session uses that user’s DEFAULT_ROLE. Masking policies, row access policies and query history all just work.

Tools are Snowflake objects you already have. Five types are supported:

• CORTEX_AGENT_RUN: A Cortex Agent, which can orchestrate its own Analyst and Search tools
• CORTEX_ANALYST_MESSAGE: Text-to-SQL over a semantic view
• CORTEX_SEARCH_SERVICE_QUERY: Search over a Cortex Search service (unstructured text)
• SYSTEM_EXECUTE_SQL: Runs SQL; read_only: true by default
• GENERIC: Your own UDF or stored procedure

When do you actually need it?

I’d reach for it when:

• Business users want answers in the assistant they already use. Revenue questions in Claude or ChatGPT, answered from a semantic view instead of whatever the model guesses.
• Engineers want their IDE assistant to see the warehouse. Cursor or Claude Code checking table shapes, row counts and freshness while you write dbt models, with read-only SQL.
• You want to wrap operational logic as a tool. A stored procedure that checks pipeline freshness, data quality results or cost is a far safer thing to hand a model than open SQL.
• You’re building agents outside Snowflake (in your app, or another vendor’s agent platform) that need governed Snowflake data.

And I’d skip it, or at least wait, when:

• You need MCP resources or prompts. Only tools are supported right now.
• The tool needs to call external APIs, run heavy Python or keep state. Host your own MCP server in Snowpark Container Services instead.
• Nobody has defined a semantic view yet. An agent over raw tables gives confident wrong answers. Do the modelling first.
• You’d be exposing more than 50 tools on one server. That’s the limit, and honestly a sign you need a better agent rather than more tools.

The example: an ops assistant for a sales pipeline

Here’s what I built, end to end, for the Monday question:

• SALES_OPS_MCP for the ops team, with two tools:
• sales_ops_agent: a Cortex Agent that answers revenue questions through a semantic view and searches support tickets.
• pipeline_freshness: a stored procedure that says when each table was last loaded, so the assistant can say "these numbers are stale" before anyone trusts them.
• ENGINEERING_SQL_MCP for data engineers: read-only SQL, on its own server.
• One least-privileged role for the ops team’s MCP access.

The data is small and synthetic (600 orders, 6 customers, 8 support tickets), but the shape is exactly what I’d do in production.

Step 1: the data

USE ROLE SYSADMIN;
CREATE WAREHOUSE IF NOT EXISTS MCP_DEMO_WH
  WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 INITIALLY_SUSPENDED = TRUE;
CREATE DATABASE IF NOT EXISTS MCP_DEMO_DB;
CREATE SCHEMA IF NOT EXISTS MCP_DEMO_DB.SALES;  -- data, agent, procedures
CREATE SCHEMA IF NOT EXISTS MCP_DEMO_DB.MCP;    -- MCP servers live here

USE SCHEMA MCP_DEMO_DB.SALES;

CREATE OR REPLACE TABLE CUSTOMERS (
  customer_id INT, customer_name STRING, region STRING, segment STRING,
  _loaded_at TIMESTAMP_NTZ);

CREATE OR REPLACE TABLE ORDERS AS
SELECT seq4() + 1000                                          AS order_id,
       uniform(1, 6, random(7))                               AS customer_id,
       DATEADD(day, -uniform(0, 180, random(11)), CURRENT_DATE()) AS order_date,
       ROUND(uniform(40::float, 2500::float, random(13)), 2)  AS amount,
       CASE WHEN uniform(1, 20, random(17)) = 1 THEN 'RETURNED'
            WHEN uniform(1, 10, random(19)) = 1 THEN 'CANCELLED'
            ELSE 'COMPLETED' END                              AS status,
       CURRENT_TIMESTAMP()::TIMESTAMP_NTZ                     AS _loaded_at
FROM TABLE(GENERATOR(ROWCOUNT => 600));

CREATE OR REPLACE TABLE SUPPORT_TICKETS (
  ticket_id INT, customer_id INT, created_at DATE,
  subject STRING, body STRING, _loaded_at TIMESTAMP_NTZ);
-- plus a handful of INSERTs: late deliveries, damaged goods, customs holds...

(In the screenshots you’ll see a BLOG_TEST_ prefix on every object, for example BLOG_TEST_MCP_DB. That's just my sandbox naming convention so I can clean up afterwards.)

Note the _loaded_at columns. Every table in our real pipeline has one, and it's what the freshness tool reads later.

Step 2: a semantic view, so the agent doesn’t guess

This is the step people skip, and it’s the one that decides whether the assistant gives right answers. The semantic view tells Cortex Analyst what “revenue” means, how tables join, and which values a column can hold.

CREATE OR REPLACE SEMANTIC VIEW SALES_SV
  TABLES (
    orders    AS MCP_DEMO_DB.SALES.ORDERS    PRIMARY KEY (order_id)
      COMMENT = 'One row per customer order',
    customers AS MCP_DEMO_DB.SALES.CUSTOMERS PRIMARY KEY (customer_id)
      COMMENT = 'Customer master data'
  )
  RELATIONSHIPS (orders_to_customers AS orders (customer_id) REFERENCES customers)
  FACTS (orders.order_amount AS amount COMMENT = 'Order value in USD')
  DIMENSIONS (
    orders.order_date      AS order_date,
    orders.order_status    AS status COMMENT = 'COMPLETED, RETURNED or CANCELLED',
    customers.customer_name AS customer_name,
    customers.region       AS region COMMENT = 'EMEA, NA or APAC',
    customers.segment      AS segment
  )
  METRICS (
    orders.total_revenue AS SUM(orders.order_amount)
      COMMENT = 'Revenue in USD across all order statuses',
    orders.order_count   AS COUNT(orders.order_id)
  )
  COMMENT = 'Orders and customers for the sales team';

Step 3: Cortex Search over support tickets

CREATE OR REPLACE CORTEX SEARCH SERVICE SUPPORT_TICKETS_SEARCH
  ON body
  ATTRIBUTES subject, customer_id
  WAREHOUSE = MCP_DEMO_WH
  TARGET_LAG = '1 day'
  AS SELECT ticket_id, customer_id, created_at, subject, body FROM SUPPORT_TICKETS;

Step 4: a freshness procedure (and my first mistake)

My first version returned a nice table, one row per source table:

CREATE OR REPLACE PROCEDURE PIPELINE_FRESHNESS(max_lag_hours INT)
  RETURNS TABLE (table_name STRING, last_loaded_at TIMESTAMP_NTZ,
                 hours_since_load NUMBER(10,1), is_stale BOOLEAN)
  ...

It worked perfectly in a worksheet. Over MCP, it failed:

MCP error calling tool pipeline_freshness: Tool result error:
[{"error":"expected a single cell result set, got 3 rows and 4 columns",
  "execution_type":"procedure", ...}]

GENERIC tools expect a single value back. The fix is to fold the result into one VARIANT, which is friendlier for a model to read anyway:

CREATE OR REPLACE PROCEDURE PIPELINE_FRESHNESS(max_lag_hours INT)
  RETURNS VARIANT
  LANGUAGE SQL
  COMMENT = 'Reports when each sales table was last loaded and flags anything older than max_lag_hours'
AS
$$
BEGIN
  RETURN (
    WITH loads AS (
      SELECT 'ORDERS' AS table_name, MAX(_loaded_at) AS last_loaded_at FROM MCP_DEMO_DB.SALES.ORDERS
      UNION ALL SELECT 'CUSTOMERS',       MAX(_loaded_at) FROM MCP_DEMO_DB.SALES.CUSTOMERS
      UNION ALL SELECT 'SUPPORT_TICKETS', MAX(_loaded_at) FROM MCP_DEMO_DB.SALES.SUPPORT_TICKETS
    )
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(
             'table', table_name,
             'last_loaded_at', last_loaded_at,
             'hours_since_load', ROUND(DATEDIFF(minute, last_loaded_at, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ) / 60, 1),
             'is_stale', DATEDIFF(minute, last_loaded_at, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ) > :max_lag_hours * 60))
    FROM loads
  );
END;
$$;

The procedure runs with owner’s rights (the default), so the ops role only needs USAGE on it, not SELECT on every table it reads.

Step 5: the Cortex Agent

The agent is the brain: it decides when to call Analyst, when to search tickets, and when to do both.

CREATE OR REPLACE AGENT SALES_OPS_AGENT
  COMMENT = 'Answers sales and customer-support questions for the ops team'
  PROFILE = '{"display_name": "Sales Ops Agent"}'
  FROM SPECIFICATION
  $$
  models:
    orchestration: auto
  orchestration:
    budget:
      seconds: 60
      tokens: 16000
  instructions:
    response: "Be concise. Show numbers in USD with no decimals. When you quote a support ticket, include its ticket_id."
    orchestration: "Use sales_analyst for revenue, order and customer metrics. Use support_search for complaints, delivery problems and anything customers wrote in tickets. Use both when a question mixes them."
  tools:
    - tool_spec:
        type: "cortex_analyst_text_to_sql"
        name: "sales_analyst"
        description: "Revenue, order counts and order status by customer, region, segment and date."
    - tool_spec:
        type: "cortex_search"
        name: "support_search"
        description: "Full-text search over customer support tickets."
  tool_resources:
    sales_analyst:
      semantic_view: "MCP_DEMO_DB.SALES.SALES_SV"
      execution_environment:
        type: "warehouse"
        warehouse: "MCP_DEMO_WH"
    support_search:
      search_service: "MCP_DEMO_DB.SALES.SUPPORT_TICKETS_SEARCH"
      max_results: 5
      id_column: "TICKET_ID"
      title_column: "SUBJECT"
  $$;

Step 6: the MCP servers

Now the actual subject of this post. Two servers, on purpose:

CREATE OR REPLACE MCP SERVER MCP_DEMO_DB.MCP.SALES_OPS_MCP
  FROM SPECIFICATION $$
  tools:
    - title: "Sales Ops Agent"
      name: "sales_ops_agent"
      type: "CORTEX_AGENT_RUN"
      identifier: "MCP_DEMO_DB.SALES.SALES_OPS_AGENT"
      description: "Ask any question about orders, revenue, customers or support tickets. Answers are governed by the sales semantic view."
    - title: "Pipeline freshness"
      name: "pipeline_freshness"
      type: "GENERIC"
      identifier: "MCP_DEMO_DB.SALES.PIPELINE_FRESHNESS"
      description: "Returns when each sales table was last loaded and whether it is older than max_lag_hours. Use it before trusting today's numbers."
      config:
        type: "procedure"
        warehouse: "MCP_DEMO_WH"
        query_timeout: 60
        input_schema:
          type: "object"
          properties:
            max_lag_hours:
              type: "number"
              description: "How many hours old a table can be before it counts as stale"
          required: ["max_lag_hours"]
  $$;

CREATE OR REPLACE MCP SERVER MCP_DEMO_DB.MCP.ENGINEERING_SQL_MCP
  FROM SPECIFICATION $$
  tools:
    - title: "Read-only SQL"
      name: "run_sql"
      type: "SYSTEM_EXECUTE_SQL"
      description: "Runs read-only SQL against the sales schema. For data engineers debugging pipelines."
      config:
        read_only: true
        query_timeout: 120
        warehouse: "MCP_DEMO_WH"
  $$;

Why not one server with all three tools? Because if SYSTEM_EXECUTE_SQL sits next to the agent, the client model can simply skip the agent and write its own SQL against raw tables. Your semantic view, your verified queries and your instructions all get bypassed. Snowflake's docs call this out explicitly, and it's the single most important design decision here.

One small note on input_schema: Snowflake's documented example only shows type and properties. I added required (standard JSON Schema), and it came through to clients in tools/list, but it isn't in the docs.

Also notice the tool descriptions. “Use it before trusting today’s numbers” is there on purpose: that sentence is what makes the client model call the freshness tool on its own.

The query behind that screenshot, which I now keep in a snippet for grant reviews:

DESCRIBE MCP SERVER MCP_DEMO_DB.MCP.SALES_OPS_MCP;

SELECT t.value:name::STRING        AS tool_name,
       t.value:type::STRING        AS tool_type,
       t.value:identifier::STRING  AS identifier,
       t.value:description::STRING AS description
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())),
     LATERAL FLATTEN(PARSE_JSON("server_spec"):tools) t;

Step 7: a least-privileged role

Access to a server does not give access to its tools. You need both. (Whoever creates the server needs the CREATE MCP SERVER privilege on the schema. I used SYSADMIN, which owns the schema.)

USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS MCP_SALES_OPS;

USE ROLE SYSADMIN;
-- reach the server
GRANT USAGE ON WAREHOUSE MCP_DEMO_WH                  TO ROLE MCP_SALES_OPS;
GRANT USAGE ON DATABASE  MCP_DEMO_DB                  TO ROLE MCP_SALES_OPS;
GRANT USAGE ON SCHEMA    MCP_DEMO_DB.MCP              TO ROLE MCP_SALES_OPS;
GRANT USAGE ON SCHEMA    MCP_DEMO_DB.SALES            TO ROLE MCP_SALES_OPS;
GRANT USAGE ON MCP SERVER MCP_DEMO_DB.MCP.SALES_OPS_MCP TO ROLE MCP_SALES_OPS;

-- use each tool
GRANT USAGE  ON AGENT MCP_DEMO_DB.SALES.SALES_OPS_AGENT                 TO ROLE MCP_SALES_OPS;
GRANT SELECT ON SEMANTIC VIEW MCP_DEMO_DB.SALES.SALES_SV                TO ROLE MCP_SALES_OPS;
GRANT SELECT ON TABLE MCP_DEMO_DB.SALES.ORDERS                          TO ROLE MCP_SALES_OPS;
GRANT SELECT ON TABLE MCP_DEMO_DB.SALES.CUSTOMERS                       TO ROLE MCP_SALES_OPS;
GRANT USAGE  ON CORTEX SEARCH SERVICE MCP_DEMO_DB.SALES.SUPPORT_TICKETS_SEARCH TO ROLE MCP_SALES_OPS;
GRANT USAGE  ON PROCEDURE MCP_DEMO_DB.SALES.PIPELINE_FRESHNESS(INT)     TO ROLE MCP_SALES_OPS;

USE ROLE ACCOUNTADMIN;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_AGENT_USER TO ROLE MCP_SALES_OPS;

Two things from the Cortex Agents access-control docs that are easy to miss:

• Over MCP, the session’s primary role is the user’s DEFAULT_ROLE, so the agent’s tools are authorized against that role. Set DEFAULT_ROLE to the role you want MCP clients to use.
• SNOWFLAKE.CORTEX_USER is granted to PUBLIC by default, and it covers all Cortex AI features, agents included. CORTEX_AGENT_USER only adds agent access. If you want this role limited to agents, you'll need to revisit the PUBLIC grant, which is an account-wide decision.

A small surprise: in SHOW GRANTS TO ROLE MCP_SALES_OPS, the server grant shows up as granted_on = CORTEX_AGENT_SERVER, not MCP_SERVER. Worth knowing if you audit grants with a script that filters on object type.

The ops role gets no grant at all on ENGINEERING_SQL_MCP.

Step 8: talk to it like a client would

Before plugging in a real assistant, I always hit the endpoint by hand. It takes the client out of the equation when something breaks. For this test I used a programmatic access token (PAT). In production, clients use OAuth (next step).

A few notes on PATs before you copy this:

• Service users need a network policy. TYPE = SERVICE users can only generate or use a PAT if they're subject to one.
• Create the PAT with a ROLE_RESTRICTION. Snowflake's docs recommend scoping it to the least-privileged role that works with MCP. With a restriction, secondary roles aren't used at all. You'll see later why that matters.
• The X-Snowflake-Role header below isn't in the MCP docs. It worked in my test (query history showed the calls running under that role), but a role-restricted PAT is the documented way to pick the role.

ACCOUNT_URL="https://<org>-<account>.snowflakecomputing.com"   # hyphens, not underscores
MCP_URL="$ACCOUNT_URL/api/v2/databases/MCP_DEMO_DB/schemas/MCP/mcp-servers/SALES_OPS_MCP"

curl -sS -X POST "$MCP_URL" \
  -H "Authorization: Bearer $SNOWFLAKE_PAT" \
  -H "X-Snowflake-Authorization-Token-Type: PROGRAMMATIC_ACCESS_TOKEN" \
  -H "X-Snowflake-Role: MCP_SALES_OPS" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/list","params":{}}'

Trimmed response:

{
  "jsonrpc": "2.0", "id": 1,
  "result": {
    "tools": [
      {
        "name": "sales_ops_agent",
        "title": "Sales Ops Agent",
        "inputSchema": {
          "type": "object",
          "properties": {
            "text":              { "type": "string" },
            "thread_id":         { "type": "integer" },
            "parent_message_id": { "type": "integer" }
          },
          "required": ["text"]
        },
        "annotations": { "readOnlyHint": false, "destructiveHint": true, ... }
      },
      {
        "name": "pipeline_freshness",
        "inputSchema": { "properties": { "max_lag_hours": { "type": "number" } }, ... },
        "annotations": { "readOnlyHint": false, "destructiveHint": true, ... }
      }
    ]
  }
}

Two things I didn’t expect:

• The agent tool supports multi-turn conversations. You get thread_id and parent_message_id for free, so follow-up questions keep their context.
• Agent and GENERIC tools are annotated destructiveHint: true. Snowflake can't know your procedure is harmless, so it plays safe. In practice, most clients will ask the user to approve each call. The read-only SQL tool, on the other hand, comes back as readOnlyHint: true.

Now the freshness tool. Note the Accept header: since August 20, 2026, tools/call responses stream as Server-Sent Events, and a client that expects a single JSON body will break. The change isn't part of a behavior change bundle, so you can't switch it off. The docs say the stream ends with a data: [DONE] event. None of my responses included one, though. They simply ended after the JSON-RPC result, so I wouldn't make a client wait for [DONE].

-d '{"jsonrpc":"2.0","id":3,"method":"tools/call",
     "params":{"name":"pipeline_freshness","arguments":{"max_lag_hours":12}}}'

event: message
data: {"jsonrpc":"2.0","id":3,"result":{"content":[{"type":"text","text":"[
  { \"table\": \"ORDERS\",          \"hours_since_load\": 0.6,  \"is_stale\": false, ... },
  { \"table\": \"CUSTOMERS\",       \"hours_since_load\": 3.1,  \"is_stale\": false, ... },
  { \"table\": \"SUPPORT_TICKETS\", \"hours_since_load\": 24.1, \"is_stale\": true,  ... }
]"}],"isError":false}}

The ticket data is a day old. That’s exactly the kind of thing I want the assistant to mention before anyone builds a slide on it.

And the agent, with a question that needs both structured and unstructured data:

-d '{"jsonrpc":"2.0","id":4,"method":"tools/call",
     "params":{"name":"sales_ops_agent","arguments":{"text":
       "Which region had the highest revenue from completed orders in the last 90 days, and what are customers in that region complaining about in support tickets?"}}}'

About 29 seconds later (with : keep-alive lines streaming in the meantime), the answer came back. Inside the response you can see the agent's whole plan: it searched tickets, pulled the semantic view, and ran the SQL that Cortex Analyst generated:

SELECT c.region AS region, SUM(o.order_amount) AS completed_revenue
FROM __orders AS o
LEFT JOIN __customers AS c ON o.customer_id = c.customer_id
WHERE o.order_status = 'COMPLETED' AND o.order_date >= DATEADD(DAY, -90, CURRENT_DATE)
GROUP BY c.region
ORDER BY completed_revenue DESC NULLS LAST /* Generated by Cortex */;

The final text:

APAC had the highest revenue from completed orders in the last 90 days, at $116,989, narrowly ahead of NA ($111,554) and EMEA ($109,644).

The main APAC-specific complaint is a customs delay: a shipment held at Sydney customs for a week (ticket #6). […]

Note: support tickets don’t carry an explicit region tag, so aside from the Sydney customs case the remaining complaints are general and can’t be definitively attributed to APAC.

I like that last paragraph a lot. The agent noticed a real gap in my data model: tickets have a customer_id but the search service doesn't expose region. The fix belongs in the pipeline (add region as a search attribute), not in a cleverer prompt.

One more thing hiding in the response metadata: that single question used about 160k input tokens, mostly cache reads. Agents aren’t free. Keep an eye on Cortex Agents usage once a whole team gets access.

Query history held two nice surprises:

• Every query is tagged cortex-agent-<request-id>, and that ID is the same request-id the MCP server returns to the client. When someone says "the assistant gave me a weird number", you can go from their request straight to the exact SQL that produced it.
• Each call starts with a failed LIST_FILES against snow://workspace/USER$.../.snowflake/si/skills. The server looks for skills in the caller's personal workspace, and the ops role has no privilege there. It's harmless, but it will show up as failures in your monitoring, so filter it out before someone opens a ticket about it.

SELECT start_time, user_name, role_name, query_type, query_tag, execution_status
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE role_name = 'MCP_SALES_OPS'
  AND query_tag LIKE 'cortex-agent-%'
ORDER BY start_time DESC;

Step 9: connect a real client with OAuth

PATs are fine for testing. For people, use OAuth so each user signs in as themselves. Snowflake’s docs use a public client (no secret, PKCE instead) for Claude:

USE ROLE ACCOUNTADMIN;
CREATE SECURITY INTEGRATION IF NOT EXISTS MCP_CLAUDE_OAUTH
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  ENABLED = TRUE
  OAUTH_CLIENT_TYPE = 'PUBLIC'
  OAUTH_REDIRECT_URI = 'https://claude.ai/api/mcp/auth_callback'
  OAUTH_ENFORCE_PKCE = TRUE                 -- PKCE is optional unless you enforce it
  OAUTH_USE_SECONDARY_ROLES = NONE          -- already the default; I set it explicitly
  ALLOWED_ROLES_LIST = ('MCP_SALES_OPS');

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('MCP_CLAUDE_OAUTH');  -- name in UPPERCASE

A few details that tripped me up while reading the docs:

• PUBLIC doesn't turn PKCE on by itself. OAUTH_ENFORCE_PKCE defaults to FALSE, and Snowflake strongly recommends requiring it. Hence the extra line. It isn't in the MCP page's example.
• The redirect URI depends on which Claude you use. https://claude.ai/api/mcp/auth_callback is for claude.ai. Claude Desktop typically uses a localhost URI. Use whatever the client shows you, and add extras with OAUTH_ALTERNATE_REDIRECT_URIS.
• For Cursor or VS Code, which can keep a secret, use OAUTH_CLIENT_TYPE = 'CONFIDENTIAL' with that client's redirect URI.

Then, for each user, set the default role and warehouse. This isn’t optional. The OAuth session starts in the user’s DEFAULT_ROLE, and Cortex Agents check permissions against that role:

ALTER USER <username> SET DEFAULT_ROLE = 'MCP_SALES_OPS' DEFAULT_WAREHOUSE = 'MCP_DEMO_WH';

In Claude, add a custom connector with the MCP URL and the integration’s credentials. The docs’ Claude steps say “client ID and secret”. That fits a confidential integration. A public one has no secret to give, so you’d enter just the client ID. Snowflake doesn’t support dynamic client registration, so you always register the client by hand. If your account has a network policy, the client provider’s outbound IPs also need to be allowed. That’s a conversation with your security team, not something to sneak in.

I didn’t run this step in my sandbox, because creating integrations there is outside what I’m allowed to touch. The SQL follows Snowflake’s MCP documentation, plus OAUTH_ENFORCE_PKCE from the security integration reference.

The gotcha that almost got past me: secondary roles

When I was testing, I listed the tools on ENGINEERING_SQL_MCP while using the ops role, expecting a permission error. It worked. The tools came back.

The role header was honored. Query history showed my calls running as MCP_SALES_OPS. But my PAT had no role restriction, and the service user I tested with had DEFAULT_SECONDARY_ROLES = ('ALL'), which is the default for new users. With secondary roles active, privileges are evaluated across every role granted to the user, so SYSADMIN's ownership quietly let me in.

The fix depends on how clients authenticate:

• PATs: create them with ROLE_RESTRICTION = '<mcp_role>'. The docs are explicit that secondary roles aren't used then, even when the user's default is ALL. Or set DEFAULT_SECONDARY_ROLES = () on dedicated MCP service users.
• OAuth: you’re protected by default, because OAUTH_USE_SECONDARY_ROLES defaults to NONE. Don't change it to IMPLICIT. Also keep ALLOWED_ROLES_LIST down to the MCP role. One quirk from the docs: the consent screen may still say "secondary roles = ALL". That label is cosmetic, and the integration setting is what counts.

If you’ve inherited older integrations, it’s worth checking them:

DESCRIBE SECURITY INTEGRATION <integration_name>;   -- look at OAUTH_USE_SECONDARY_ROLES
ALTER SECURITY INTEGRATION <integration_name> SET OAUTH_USE_SECONDARY_ROLES = NONE;
ALTER SECURITY INTEGRATION <integration_name> SET ALLOWED_ROLES_LIST = ('MCP_SALES_OPS');

The read_only flag on the SQL tool, for what it's worth, did hold up. A DELETE sent through run_sql was rejected. The error message, though, was just MCP Server tool error: !399517!, which won't help a model (or you) understand what happened.

Limitations to know before you commit

• Tools only. No MCP resources, prompts, sampling or notifications. Supported protocol revision is 2025-11-25.
• 50 tools per server.
• Responses from GENERIC and SQL tools are truncated at 250 KB. Agent responses can be big too (mine was 33 KB for one question).
• Max recursion depth of 10 if your tools end up calling other MCP servers.
• Cortex Analyst tools need a semantic view. Legacy YAML semantic model files aren’t supported.
• Government regions: available, except Azure US Gov Virginia (non-FedRAMP High), and the formal FedRAMP assessment isn’t done yet.
• Not replicated by failover groups. Recreate MCP servers on the secondary account. The OAuth integrations do replicate.
• No dynamic client registration. Every client gets a manually created integration.

Wrapping up

The thing I like most about the managed MCP server is how little of it is new. Agents, semantic views, search services, procedures and roles are all things a data team already knows how to build, review and deploy. The MCP server is a thin, declarative door on top. That means your existing CI/CD, your existing grants audit and your existing query history all cover it.

If you try it, start with one agent and one role, test with curl before you test with a client, and spend more time on the semantic view and the tool descriptions than on the server itself. That’s where the answer quality comes from.

And restrict every PAT you hand to an MCP client to a single role. Seriously.

What’s next: Part 2

Curl got me through testing, but it’s not something I can hand to the ops team. In Part 2, I build a small local web console on top of these exact two servers. It’s a chat for the agent, generated forms for every other tool, and a look inside what a Cortex Agent response really contains: tables, charts, the SQL it ran, and what each answer costs in tokens.

Cleanup

When you’re done experimenting:

DROP DATABASE IF EXISTS MCP_DEMO_DB;   -- drops servers, agent, search service, procedure
DROP WAREHOUSE IF EXISTS MCP_DEMO_WH;
DROP ROLE IF EXISTS MCP_SALES_OPS;
DROP SECURITY INTEGRATION IF EXISTS MCP_CLAUDE_OAUTH;

References

• Snowflake-managed MCP server

To stay updated on more Snowflake-related posts, follow me at my Medium profile: Eylon’s Snowflake Articles.

I’m Eylon Steiner, Principal Architect at Infostrux Solutions and a Snowflake Data Superhero. You can follow me on LinkedIn.

Subscribe to Infostrux Medium Blog at https://blog.infostrux.com for the most interesting Data Engineering and Snowflake news. Follow Infostrux’s open-source efforts through GitHub.

Snowflake’s Managed MCP Server: Giving AI Assistants a Governed Door Into Your Warehouse (Part 1) was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.