Skip to main content

Snowflake MCP Server Integration Guide

This guide provides comprehensive instructions for setting up a Model Context Protocol (MCP) server for Snowflake integration using OAuth 2.0 or Bearer token authentication.

Overview​

This integration allows you to:

  • Connect to Snowflake with OAuth 2.0 authentication (recommended)
  • Connect to Snowflake with Bearer token authentication (alternative)
  • Execute SQL statements programmatically
  • Manage database operations (list, create, modify, delete databases)
  • Implement secure network access controls
  • Create an MCP server for seamless integration

Prerequisites​

  • Snowflake account with appropriate privileges (ACCOUNTADMIN or SECURITYADMIN)
  • Access to Snowflake web interface
  • Node.js environment for MCP server development
  • Understanding of REST API concepts

Step 1: Identify Your Snowflake Organization Details​

1.1 Locate Your Server URL​

Your Snowflake server URL follows this format:

https://<org-name>-<account-name>.snowflakecomputing.com

Example from our implementation:

https://NBTLVBU-JX39884.snowflakecomputing.com

1.2 Extract Organization Information​

From the URL NBTLVBU-JX39884.snowflakecomputing.com:

  • Organization Name: NBTLVBU
  • Account Name: JX39884

Note: You can find your account URL in the Snowflake web interface after logging in, or from your account administrator.

Step 2: Configure Network Security​

2.1 Identify Client IP Addresses​

Determine the IP addresses that need access to your Snowflake account:

# Check your current public IP
curl https://ipinfo.io/ip

2.2 Create or Update Network Policy​

Log into your Snowflake web interface and execute the following SQL commands:

-- Check existing network policies
SHOW NETWORK POLICIES;

-- Create a new network policy for API access
CREATE OR REPLACE NETWORK POLICY api_access_policy
  ALLOWED_IP_LIST = (
        -- Development IP,
        -- Production IP
  );

-- Apply the policy to your user account
ALTER USER username SET NETWORK_POLICY = 'api_access_policy';

-- Alternative: Apply account-wide (requires ACCOUNTADMIN role)
-- ALTER ACCOUNT SET NETWORK_POLICY = 'api_access_policy';

2.3 Verify Network Policy​

-- Verify the policy is applied
DESC USER username;

-- Check policy details
DESC NETWORK POLICY api_access_policy;

Step 3: Configure OAuth Authentication​

This section walks you through setting up OAuth authentication for Snowflake and retrieving the necessary credentials for your OAuth application.

3.1 Create OAuth Security Integration​

Run the following SQL command in Snowflake to create an OAuth security integration:

CREATE SECURITY INTEGRATION oauth_integration
    TYPE = OAUTH
    ENABLED = TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'  -- Use 'PUBLIC' for mobile/SPA apps
    OAUTH_REDIRECT_URI = 'https://auth.aigateway.cequence.ai/v1/outbound/oauth/callback'
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 86400  -- 24 hours in seconds (adjust as needed)
    OAUTH_USE_SECONDARY_ROLES = IMPLICIT
    BLOCKED_ROLES_LIST = ()  -- Empty list allows all roles
    COMMENT = 'OAuth integration for MCP server';

Important Configuration Notes:

  • OAUTH_CLIENT_TYPE: Use CONFIDENTIAL for server-side apps, PUBLIC for client-side apps
  • OAUTH_REDIRECT_URI: Must exactly match the redirect URI configured in your OAuth application
  • OAUTH_REFRESH_TOKEN_VALIDITY: Set in seconds (86400 = 24 hours, 7776000 = 90 days)
  • BLOCKED_ROLES_LIST: Leave empty () or specify roles to block from OAuth access

3.2 Retrieve OAuth Configuration Details​

Get the authorization URL, token URL, client ID, and other configuration details:

DESC SECURITY INTEGRATION oauth_integration;

This returns a table with all integration properties. Look for these key values:

PropertyDescriptionExample Value
OAUTH_AUTHORIZATION_ENDPOINTAuthorization URLhttps://{account_identifier}.snowflakecomputing.com/oauth/authorize
OAUTH_TOKEN_ENDPOINTToken URLhttps://{account_identifier}.snowflakecomputing.com/oauth/token-request
OAUTH_CLIENT_IDClient IDjlDraW4g+DjiT+GCiM9rGx/NDqQ=
OAUTH_REDIRECT_URIRedirect URIhttps://auth.aigateway.cequence.ai/v1/outbound/oauth/callback

Replace {account_identifier} with your Snowflake account identifier (e.g., NBTLVBU-JX39884).

3.3 Retrieve Client Secret​

Get the OAuth client secret (this is sensitive information, store securely):

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('oauth_integration');

This returns a JSON object containing the client secret:

{
  "OAUTH_CLIENT_SECRET": "your_client_secret_here"
}

Security Note: The client secret is only shown once. Store it securely in a secrets manager or environment variable.

3.4 Configure Your OAuth Application​

Use the retrieved values to configure your OAuth application:

Authorization URL:

https://{account_identifier}.snowflakecomputing.com/oauth/authorize

Token URL:

https://{account_identifier}.snowflakecomputing.com/oauth/token-request

Client ID: Copy from OAUTH_CLIENT_ID property

Client Secret: Copy from the SYSTEM$SHOW_OAUTH_CLIENT_SECRETS result

Scopes: For Snowflake OAuth, use role-based scopes:

session:role:ROLE_NAME

Examples:

  • session:role:ACCOUNTADMIN (administrative access)
  • session:role:PUBLIC (default role)
  • session:role:api_role (your custom role)

Note: The role specified in the scope must not be in the BLOCKED_ROLES_LIST and the user must have been granted that role.

3.5 Grant Role to Users​

Ensure users have the appropriate role for OAuth access:

-- Grant role to user
GRANT ROLE api_role TO USER username;

-- Create a custom role with specific permissions (recommended)
CREATE ROLE api_role;
GRANT USAGE ON DATABASE database_name TO ROLE api_role;
GRANT USAGE ON SCHEMA database_name.schema_name TO ROLE api_role;
GRANT SELECT ON ALL TABLES IN SCHEMA database_name.schema_name TO ROLE api_role;
GRANT ROLE api_role TO USER username;

3.6 OAuth Flow​

1. Authorization Request​

Direct users to the authorization URL with required parameters:

https://{account_identifier}.snowflakecomputing.com/oauth/authorize?
  client_id=YOUR_CLIENT_ID&
  response_type=code&
  redirect_uri=YOUR_REDIRECT_URI&
  scope=session:role:api_role

2. Token Exchange​

After user authorization, exchange the code for tokens:

curl -X POST https://{account_identifier}.snowflakecomputing.com/oauth/token-request \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "grant_type=authorization_code" \
  -d "code=AUTHORIZATION_CODE" \
  -d "redirect_uri=YOUR_REDIRECT_URI" \
  -d "client_id=YOUR_CLIENT_ID" \
  -d "client_secret=YOUR_CLIENT_SECRET"

Response:

{
  "access_token": "ver:...",
  "token_type": "Bearer",
  "expires_in": 600,
  "refresh_token": "ver:..."
}

3. Refresh Token​

When the access token expires, use the refresh token:

curl -X POST https://{account_identifier}.snowflakecomputing.com/oauth/token-request \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "grant_type=refresh_token" \
  -d "refresh_token=YOUR_REFRESH_TOKEN" \
  -d "client_id=YOUR_CLIENT_ID" \
  -d "client_secret=YOUR_CLIENT_SECRET"

4. Make API Calls​

Use the access token in the Authorization header:

curl -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  https://{account_identifier}.snowflakecomputing.com/api/v2/databases

Step 4: Configure Bearer Token Authentication (Alternative)​

Bearer token authentication provides a simpler alternative to OAuth 2.0 for development and testing scenarios.

4.1 Generate Snowflake Access Token​

Snowflake provides built-in OAuth support. To generate a Bearer token:

Go to Account -> Settings -> Authentication -> Access Tokens -> Generate Token

Example token format:

eyJraWQiOiIxNDQ3NjUzMzc2NSIsImFsZyI6IkVTMjU2In0...

Available Snowflake OAuth Scopes​

Snowflake OAuth uses role-based scopes to control access. The scope format is:

session:role:ROLE_NAME

Role-Based Scopes​

  • session:role:ACCOUNTADMIN

    • Full administrative access
    • Account-level operations
    • Use with caution in production

  • session:role:PUBLIC

    • Default role access
    • Basic operations
    • Suitable for read-only access

  • session:role:api_role

    • Custom role for API access
    • Recommended for production use
    • Configure specific permissions as needed

  • session:role:custom_role

    • Additional custom roles
    • Tailored to specific use cases
    • Follow principle of least privilege

For Full Administrative Access:

session:role:ACCOUNTADMIN

For Production API Access:

session:role:api_role

For Read-Only Operations:

session:role:PUBLIC

Note: The role specified in the scope must:

  • Not be in the BLOCKED_ROLES_LIST of the OAuth integration
  • Be granted to the user authenticating
  • Have appropriate permissions for the operations you need to perform

Step 5: Create an MCP Server​

  1. Go to AI Gateway -> Apps -> Third-party -> Snowflake -> Create MCP Server
  2. Configure the MCP Server
  3. Click on the MCP Server -> Configure API Endpoints
  4. Configure the API Endpoints
  5. Click on the MCP Server -> Configure Authentication

  1. Authentication Type: Select OAuth 2.0

  2. Fill in the OAuth configuration:

    • Authorization URL:

      https://{account_identifier}.snowflakecomputing.com/oauth/authorize

      Replace {account_identifier} with your Snowflake account identifier (e.g., NBTLVBU-JX39884)

    • Token URL:

      https://{account_identifier}.snowflakecomputing.com/oauth/token-request

      Replace {account_identifier} with your Snowflake account identifier

    • Client ID: Paste the Client ID from Step 3.2

    • Client Secret: Paste the Client Secret from Step 3.3

    • Redirect URI:

      https://auth.aigateway.cequence.ai/v1/outbound/oauth/callback

    • Scopes: Enter the required scopes (space-separated):

      session:role:api_role

      See the "Available Snowflake OAuth Scopes" section for more options.

  3. Token Refresh: Enable automatic token refresh

  4. Click Validate Credentials to test the configuration

  5. Click Next after successful validation

5.2 Configure Bearer Token Authentication (Alternative)​

  1. Authentication Type: Select Bearer Token

  2. Token: Enter your Snowflake access token from Step 4.1

  3. Click Next to continue

5.3 Review and Deploy​

  1. Review all configurations:

    • API endpoints selected
    • Authentication method (OAuth 2.0 or Bearer Token)
    • Security settings

  2. Click Create & Deploy to finalize the setup

  3. Wait for deployment (typically 1-2 minutes)

Common Issues and Troubleshooting​

Invalid OAuth Access Token (401)​

Causes:

  • Token has expired (default: 10 minutes)
  • Using wrong account URL for API calls
  • Token was issued before integration changes
  • Redirect URI mismatch

Solutions:

  • Use refresh token to get new access token
  • Ensure API endpoint matches OAuth endpoint account identifier
  • Re-authenticate after any integration changes
  • Verify redirect URI exactly matches integration configuration

Blocked Role Error​

Cause: Attempting to use a role in BLOCKED_ROLES_LIST

Solution:

ALTER SECURITY INTEGRATION oauth_integration
SET BLOCKED_ROLES_LIST = ();

Token Not Refreshing​

Cause: OAUTH_ISSUE_REFRESH_TOKENS is FALSE

Solution:

ALTER SECURITY INTEGRATION oauth_integration
SET OAUTH_ISSUE_REFRESH_TOKENS = TRUE;

Redirect URI Mismatch​

Cause: Redirect URI in OAuth integration doesn't match the one used in authorization request

Solution:

ALTER SECURITY INTEGRATION oauth_integration
SET OAUTH_REDIRECT_URI = 'https://auth.aigateway.cequence.ai/v1/outbound/oauth/callback';

Role Not Granted to User​

Cause: User doesn't have the role specified in the OAuth scope

Solution:

GRANT ROLE api_role TO USER username;

Viewing and Managing Integrations​

List All OAuth Integrations​

SHOW INTEGRATIONS;

View Specific Integration​

DESC SECURITY INTEGRATION oauth_integration;

Modify Integration​

ALTER SECURITY INTEGRATION oauth_integration
SET OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;  -- 90 days

Drop Integration​

DROP SECURITY INTEGRATION oauth_integration;

Security Best Practices​

  1. Use Non-Privileged Roles: Avoid using ACCOUNTADMIN, SECURITYADMIN, or ORGADMIN for OAuth applications. Create custom roles with minimal required permissions.

  2. Rotate Secrets Regularly: Periodically regenerate client secrets using:

    SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('oauth_integration');

  3. Implement Token Expiration: Use short-lived access tokens (10 minutes default) and enable refresh tokens for long-lived sessions.

  4. Secure Storage: Store client secrets in secure secret managers or environment variables. Never commit secrets to version control.

  5. Monitor Access: Regularly audit OAuth token usage and revoke unused integrations:

    SHOW INTEGRATIONS;

  6. Limit Redirect URIs: Only specify necessary redirect URIs in the OAuth integration configuration.

  7. Use HTTPS: Always use HTTPS for redirect URIs and API endpoints.

  8. Network Policies: Combine OAuth with network policies (Step 2) to restrict access by IP address.

  9. Role-Based Access: Follow the principle of least privilege when granting roles to users for OAuth access.

  10. Regular Reviews: Periodically review and update OAuth integrations, roles, and permissions.

Conclusion​

This guide provides a complete implementation for integrating Snowflake with an MCP server using OAuth 2.0 or Bearer token authentication. The setup includes:

  • ✅ Snowflake organization identification
  • ✅ Network security configuration
  • ✅ OAuth 2.0 authentication (recommended)
  • ✅ Bearer token authentication (alternative)
  • ✅ Complete MCP server implementation
  • ✅ Security best practices
  • ✅ Troubleshooting guidance

The MCP server enables seamless integration between Claude and Snowflake, allowing natural language queries to be converted into SQL operations and database management tasks.

Using Your MCP Server​

Setup Instructions:

Next Steps​

  1. Extend Functionality: Add more Snowflake API endpoints (schemas, tables, warehouses)
  2. Error Handling: Implement robust error handling and retry logic
  3. Caching: Add response caching for better performance
  4. Monitoring: Integrate logging and monitoring solutions
  5. Security: Review and implement security best practices for OAuth integration
  6. Role Management: Create and configure custom roles for specific use cases

Additional Resources​