Snowflake MCP Connection

intent

wire snowflake's managed mcp server into clawdbot or any mcp-compatible client. use this when you need to expose snowflake data, cortex ai tools, or custom procedures through an mcp endpoint. covers token generation, server creation, endpoint validation, and client configuration so your ai assistant can execute queries, search documents, or invoke agents directly against snowflake without additional infrastructure.

inputs

snowflake account access

• snowflake account with accountadmin or equivalent role
• programmatic access token (pat) generated from snowflake user profile (see step 1)
• target database and schema names where mcp server will live

mcp client

• clawdbot or any mcp-compatible client (claude desktop, custom scripts, etc.)
• ability to write mcp.json config file to project root or pass config via env vars

external connections

• snowflake api endpoint: https://{org}-{account}.snowflakecomputing.com/api/v2/
  • set via env var SNOWFLAKE_ACCOUNT_URL or hardcode after validation
  • requires outbound https on port 443
• pat token stored securely (env var SNOWFLAKE_PAT recommended, not in git)
• network access to snowflake compute warehouse (if using cortex tools)

optional: cortex ai services

• cortex search service name (if using rag) created in snowsight
• cortex semantic view (if using analyst tool) with yaml definition
• cortex agent identifier (if invoking agents)

procedure

step 1: generate programmatic access token

inputs: snowflake account, user credentials, target role

1. log into snowsight with accountadmin or admin role.
2. click user menu (top right) → select my profile.
3. navigate to programmatic access tokens section.
4. click create token and select the role that will execute mcp queries (must have usage on target warehouse and select/execute on target database/schema).
5. copy the token immediately. snowflake does not re-display it.
6. store token in env var: export SNOWFLAKE_PAT="sfp_xxxxxxxxxxxx" (do not commit to version control).

output: valid pat token in SNOWFLAKE_PAT env var, expiration date noted.

step 2: create mcp server in snowflake

inputs: pat token, target database and schema, mcp server name, desired tool types (sql, cortex search, cortex analyst, cortex agent, or custom)

1. open snowsight and create a new sql worksheet.
2. select target warehouse and database/schema from dropdowns.
3. paste the appropriate create mcp server statement below (choose from examples: basic sql only, with cortex search, with cortex analyst, with cortex agent, or full featured).
4. replace placeholder identifiers (MY_DB, MY_SCHEMA, MY_SEARCH_SERVICE, etc.) with actual object names in your account.
5. execute the create statement.
6. verify success: no errors returned, mcp server listed in snowsight under that schema.

output: mcp server object created in snowflake (name, tools list, identifiers confirmed in snowsight ui).

step 3: retrieve mcp server endpoint url and validate connectivity

inputs: snowflake account org, account name, database name, schema name, mcp server name, pat token

1. construct the mcp endpoint url: https://{org}-{account}.snowflakecomputing.com/api/v2/databases/{DB}/schemas/{SCHEMA}/mcp-servers/{SERVER_NAME}
2. test connectivity with curl (or postman, python requests, etc.):

   curl -X POST "{ENDPOINT_URL}" \
     -H "Authorization: Bearer {PAT_TOKEN}" \
     -H "Content-Type: application/json" \
     -d '{"jsonrpc":"2.0","id":1,"method":"tools/list","params":{}}'
   

3. check response: if status 200 and response contains "result" with tools array, endpoint is reachable and authenticated.
4. if 401: pat token is invalid, expired, or role lacks permissions. regenerate token or grant role select on target schema.
5. if 404: database, schema, or mcp server name is incorrect. verify names in snowsight.
6. if connection timeout: firewall or network issue. verify outbound https to snowflake account url is allowed.

output: mcp endpoint url confirmed, tools list returned in json response.

step 4: create mcp.json client configuration

inputs: mcp endpoint url, pat token, mcp server display name

1. create or edit mcp.json at your project root (or in a config directory).
2. populate with this structure:

   {
     "mcpServers": {
       "Snowflake MCP Server": {
         "url": "https://{org}-{account}.snowflakecomputing.com/api/v2/databases/{DB}/schemas/{SCHEMA}/mcp-servers/{SERVER_NAME}",
         "headers": {
           "Authorization": "Bearer {PAT_TOKEN}"
         }
       }
     }
   }
   

3. replace placeholders with actual values from step 3.
4. do not commit this file if it contains the pat token. use env var substitution instead: "Authorization": "Bearer ${SNOWFLAKE_PAT}" and load from env at runtime.
5. save mcp.json.

output: mcp.json file with valid endpoint url and auth header.

step 5: start mcp client session and load config

inputs: mcp.json file, mcp client (clawdbot or other), pat token in env var or hardcoded

1. open terminal and ensure SNOWFLAKE_PAT env var is set: echo $SNOWFLAKE_PAT (should print token, not empty).
2. start clawdbot or your mcp client with mcp config loaded. command depends on client:
   • clawdbot: clawdbot --mcp-config mcp.json or load config in interactive session
   • claude desktop: add server entry to claude_desktop_config.json or equivalent
   • custom client: pass mcp.json path to client initialization
3. wait for client to establish connection. check logs for "connected" or similar message.
4. if connection fails, check firewall, pat token expiry, and endpoint url in step 3.

output: mcp client session active with snowflake mcp server connected.

step 6: invoke snowflake tools from client

inputs: mcp client session, query or command, tool name (e.g., "sql execution tool", "document search", etc.)

1. in your mcp client session, invoke a tool by name or ask a question that triggers tool use:
   • sql execution: "run select count(*) from my_table"
   • cortex search: "search for customer complaints about billing"
   • cortex analyst: "what were sales last quarter by region"
   • custom procedure: "send an email to alice@example.com with subject 'report' and body 'attached'"
2. client parses the request, calls the matching tool via mcp protocol, and snowflake executes it.
3. result is returned to client and displayed in session.

output: tool result (query rows, search matches, agent response, etc.) displayed in client.

decision points

if pat token is expired or 401 unauthorized response:

• regenerate a new pat token in snowsight (step 1).
• update SNOWFLAKE_PAT env var or mcp.json.
• retry mcp client connection.

if you need to change the role that executes mcp queries:

• pat tokens are bound to a single role at creation time.
• you cannot modify role on an existing pat.
• solution: create a new pat with the desired role, update config, restart client.

if 404 not found on mcp endpoint url:

• verify database, schema, and mcp server names match exactly (case sensitive in some contexts).
• confirm mcp server was created successfully in snowsight (check schema object list).
• if server name typo: drop the server and recreate with correct name.

if you want to use cortex search, analyst, or agent tools:

• cortex search: create search service in snowsight first (ai & ml → cortex search). identifier in mcp server spec must match.
• cortex analyst: upload semantic yaml or create semantic view in snowsight first. identifier in mcp server spec must match.
• cortex agent: create agent in snowsight first. identifier in mcp server spec must match.
• if tool invocation fails: verify the referenced object exists in the specified database/schema.

if mcp client cannot reach snowflake endpoint (connection timeout):

• check network firewall allows outbound https to {org}-{account}.snowflakecomputing.com.
• verify account org and account name are correct (hyphens vs underscores).
• test with curl from same network as client to isolate network vs config issue.
• if behind corporate proxy: configure client or curl with proxy settings.

if you want to deploy a local mcp server instead of managed:

• this skill covers managed mcp server only.
• for local deployment using snowflake-labs-mcp package, see alternative resources.

output contract

success state:

• mcp server exists in snowflake (visible in snowsight schema object list).
• mcp endpoint url responds with http 200 and valid jsonrpc response containing tools array.
• mcp.json file exists at project root with valid url, headers, and no syntax errors.
• mcp client session connects without auth or network errors.
• invoking any mcp tool returns result (query rows, search matches, etc.) or graceful error (e.g., "no results found").

data format:

• mcp.json: json object with mcpServers key mapping server name to {url, headers} object.
• mcp endpoint responses: jsonrpc 2.0 format with result field containing tool list or execution result.
• tools list response includes array of tool objects with name, description, inputSchema (for input validation).

file location:

• mcp.json: project root or config directory (path must be accessible to mcp client).
• pat token: snowflake account user profile (programmatic access tokens section).
• mcp server definition: snowflake database/schema (visible in snowsight object browser).

outcome signal

• step 3 validation: curl returns http 200 with "method":"tools/list" result containing one or more tool objects. no 401, 404, or timeout.
• step 5 connection: mcp client logs show "connected to mcp server" or similar. no error messages about auth or endpoint.
• step 6 invocation: user asks client a question, client invokes snowflake mcp tool, and result (query output, search matches, agent response) appears in session within a few seconds. query logs in snowflake query history show the mcp tool execution.

if any step fails (e.g., 401 on curl, client fails to connect, tool invocation hangs), check decision points above for the specific error and retry.

---

reference: mcp server examples

basic sql execution only

CREATE OR REPLACE MCP SERVER sql_mcp_server FROM SPECIFICATION
$$
tools:
  - name: "SQL Execution Tool"
    type: "SYSTEM_EXECUTE_SQL"
    description: "Execute SQL queries against Snowflake."
    title: "SQL Execution"
$$;

with cortex search (rag)

first create a cortex search service in snowsight (ai & ml → cortex search), then:

CREATE OR REPLACE MCP SERVER search_mcp_server FROM SPECIFICATION
$$
tools:
  - name: "Document Search"
    identifier: "MY_DB.MY_SCHEMA.MY_SEARCH_SERVICE"
    type: "CORTEX_SEARCH_SERVICE_QUERY"
    description: "Search and retrieve information from documents using vector search."
    title: "Document Search"
  - name: "SQL Execution Tool"
    type: "SYSTEM_EXECUTE_SQL"
    description: "Execute SQL queries."
    title: "SQL Execution"
$$;

with cortex analyst (semantic views)

first upload a semantic yaml or create a semantic view, then:

CREATE OR REPLACE MCP SERVER analyst_mcp_server FROM SPECIFICATION
$$
tools:
  - name: "Sales Analytics"
    identifier: "MY_DB.MY_SCHEMA.SALES_SEMANTIC_VIEW"
    type: "CORTEX_ANALYST_MESSAGE"
    description: "Query sales metrics and KPIs using natural language."
    title: "Sales Analytics"
  - name: "SQL Execution Tool"
    type: "SYSTEM_EXECUTE_SQL"
    description: "Execute SQL queries."
    title: "SQL Execution"
$$;

with cortex agent

CREATE OR REPLACE MCP SERVER agent_mcp_server FROM SPECIFICATION
$$
tools:
  - name: "Documentation Agent"
    identifier: "MY_DB.MY_SCHEMA.MY_AGENT"
    type: "CORTEX_AGENT_RUN"
    description: "An agent that answers questions using documentation."
    title: "Documentation Agent"
$$;

full featured server

CREATE OR REPLACE MCP SERVER full_mcp_server FROM SPECIFICATION
$$
tools:
  - name: "Analytics Semantic View"
    identifier: "ANALYTICS_DB.DATA.FINANCIAL_ANALYTICS"
    type: "CORTEX_ANALYST_MESSAGE"
    description: "Query financial metrics, customer data, and business KPIs."
    title: "Financial Analytics"
  - name: "Support Tickets Search"
    identifier: "SUPPORT_DB.DATA.TICKETS_SEARCH"
    type: "CORTEX_SEARCH_SERVICE_QUERY"
    description: "Search support tickets and customer interactions."
    title: "Support Search"
  - name: "SQL Execution Tool"
    type: "SYSTEM_EXECUTE_SQL"
    description: "Execute SQL queries against Snowflake."
    title: "SQL Execution"
  - name: "Send_Email"
    identifier: "MY_DB.DATA.SEND_EMAIL"
    type: "GENERIC"
    description: "Send emails to verified addresses."
    title: "Send Email"
    config:
      type: "procedure"
      warehouse: "COMPUTE_WH"
      input_schema:
        type: "object"
        properties:
          body:
            description: "Email body in HTML format."
            type: "string"
          recipient_email:
            description: "Recipient email address."
            type: "string"
          subject:
            description: "Email subject line."
            type: "string"
$$;

tool types reference

type	purpose
SYSTEM_EXECUTE_SQL	execute arbitrary sql queries
CORTEX_SEARCH_SERVICE_QUERY	rag over unstructured data
CORTEX_ANALYST_MESSAGE	natural language queries on semantic models
CORTEX_AGENT_RUN	invoke cortex agents
GENERIC	custom tools (procedures, functions)

common troubleshooting

ssl error / hostname mismatch: use hyphens in account name, not underscores. snowflake urls are case insensitive but require correct hyphen placement.

401 unauthorized: pat token is invalid, expired, or role lacks select on target schema. regenerate token or grant role permissions.

404 not found: database, schema, or mcp server name is incorrect or doesn't exist. verify all names in snowsight schema object browser.

connection timeout: firewall blocks outbound https to snowflake. test with curl from same network. check corporate proxy settings if behind firewall.

tool invocation hangs or returns error: if cortex tool, verify the referenced service/view/agent exists in the specified db.schema. check warehouse is running if sql execution tool.

pat token always fails after creation: secondary roles are not evaluated by pat. create new pat, select single role with all required permissions at creation time.

to list available tools at any time:

curl -X POST "{ENDPOINT_URL}" \
  -H "Authorization: Bearer {PAT_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/list","params":{}}'

resources

• snowflake mcp server guide: https://www.snowflake.com/en/developers/guides/getting-started-with-snowflake-mcp-server/
• snowflake mcp docs: https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-agents-mcp
• github sfguide: https://github.com/Snowflake-Labs/sfguide-getting-started-with-snowflake-mcp-server
• mcp protocol: https://modelcontextprotocol.io