Connecting AI agents to Snowflake without another service to run

Table of Contents

I’ve done a few projects recently to help data teams and analysts work better with Snowflake and AI - and found that the external documentation and guidance isn’t particularly helpful at the moment.

The biggest challenge here is not functionality but simplicity. How do we get the most benefit out of doing this without introducing too many additional knobs and dials to tune, and without introducing additional infrastructure to fail?

I’d strongly point people toward a few architectural choices here:

Just use the Snowflake-managed MCP Server . You’re already paying for Snowflake, you’re going to have to provision access anyway, and why introduce any other moving parts?
You don’t need fancy features (not initially anyway). Claude/ChatGPT are both fine at writing SQL - so just let them write SQL.
Use individual OAuth sign-in rather than shared credentials. I also prefer setting up SSO for Snowflake, so people can use their usual company login when authorising the connection.

This guide starts with access for analysts, then covers organisation-wide Claude access using a more restricted role.

Getting data into Claude doesn’t replace everything a BI tool does. I’ve written about whether BI still makes sense when Claude can make dashboards over on A14K. Here I’m sticking to the practical question of giving agents access to the data.

The names below are examples. The SQL assumes you already have a warehouse, curated views, and an administrator able to create roles, schemas, MCP servers and security integrations. Adapt the grants to your organisation’s access model. The client instructions follow the documentation available in October 2026.

1. Start with an analyst role
#

Going through this process with your “developer”/“analyst” archetype in mind will make your life easier. You can start with someone whose existing data access you understand, then work through a more restricted setup afterwards.

If you don’t already have a suitable reader role for those analysts, make one. The database, schema and user names here are placeholders:

CREATE ROLE FULL_READER;

-- Allow it to query the datasets approved for these analysts
GRANT USAGE ON WAREHOUSE SOME_WAREHOUSE TO ROLE FULL_READER;

GRANT USAGE ON DATABASE THIS_DATABASE TO ROLE FULL_READER;
GRANT USAGE ON SCHEMA THIS_DATABASE.STUFF TO ROLE FULL_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA THIS_DATABASE.STUFF TO ROLE FULL_READER;
GRANT SELECT ON ALL VIEWS IN SCHEMA THIS_DATABASE.STUFF TO ROLE FULL_READER;

GRANT USAGE ON DATABASE THAT_DATABASE TO ROLE FULL_READER;
GRANT USAGE ON SCHEMA THAT_DATABASE.OTHER_STUFF TO ROLE FULL_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA THAT_DATABASE.OTHER_STUFF TO ROLE FULL_READER;
GRANT SELECT ON ALL VIEWS IN SCHEMA THAT_DATABASE.OTHER_STUFF TO ROLE FULL_READER;

GRANT ROLE FULL_READER TO USER EXAMPLE_ANALYST;

These grants cover existing tables and views. Decide separately whether the role should receive access to future objects as they’re created; Snowflake’s grant documentation covers both cases.

For analysts, I’d include both processed and unprocessed data where they’re already entitled to see it. Exploring the raw data can be useful when trying to understand how a reporting model ended up with a surprising result. That’s a reason to include the relevant raw datasets, rather than automatically granting access to everything you’ve ingested.

Create a managed MCP Server
#

Use an administrator role to create a dedicated schema for the connection and a server exposing the SQL execution tool:

CREATE DATABASE ANALYTICS;
CREATE SCHEMA ANALYTICS.AI_ACCESS;

CREATE MCP SERVER ANALYTICS.AI_ACCESS.DIRECT_SQL
    FROM SPECIFICATION $$
tools:
  - name: direct_sql
    title: Direct SQL
    type: SYSTEM_EXECUTE_SQL
    description: Query the data available to your Snowflake role.
    config:
      read_only: true
      query_timeout: 60
      warehouse: SOME_WAREHOUSE
$$;

GRANT USAGE ON DATABASE ANALYTICS
    TO ROLE FULL_READER;
GRANT USAGE ON SCHEMA ANALYTICS.AI_ACCESS
    TO ROLE FULL_READER;
GRANT USAGE ON MCP SERVER ANALYTICS.AI_ACCESS.DIRECT_SQL
    TO ROLE FULL_READER;

The server specification defines which tools are exposed. Its location in AI_ACCESS doesn’t restrict queries to that schema: the account role’s privileges do that work.

The SQL tool’s read_only setting and timeout are enforced by the server. The role restricts which data it can read. Those are useful, separate controls; a prompt asking the assistant to behave itself is doing neither job.

For this approach, I’d start with just SYSTEM_EXECUTE_SQL. Tools like CORTEX_AGENT_RUN, CORTEX_SEARCH_SERVICE_QUERY and CORTEX_ANALYST_MESSAGE can be useful, but introduce more configuration and their own charging models. I’d add them when there’s a specific need, rather than as a prerequisite.

This deliberately exposes direct SQL, without forcing queries through a semantic view or a set of verified queries. Snowflake’s data permissions still apply; the agent interpreting the data correctly is a separate question, and you still need to check its answers. For analysts exploring data, I think that’s a useful starting point.

The endpoint has this shape, with your account host substituted:

https://YOUR-ACCOUNT.snowflakecomputing.com/api/v2/databases/ANALYTICS/schemas/AI_ACCESS/mcp-servers/DIRECT_SQL

See the managed MCP server documentation for endpoint and tool configuration details. Warehouse compute still costs money, so start with a suitable warehouse size and monitor its usage.

Configure authentication
#

I’ll use Snowflake OAuth for the MCP connection. SSO handles the person’s sign-in to Snowflake; OAuth lets them authorise Claude to connect with their Snowflake permissions. They’re different parts of the setup. You don’t need SSO to use Snowflake OAuth, but it’s a much cleaner UX for a wider rollout so people can use their existing company login.

I’m not going to detail how to set up SSO in Snowflake for your org provider of choice (Google Workspace, Microsoft, Okta, etc.). That’s beyond the scope of this guide - but I’ll assume you’ve done it.

The OAuth connection then requires its own SECURITY INTEGRATION in Snowflake. Additionally, there will be parameters here which are specific to the AI platform you’re using. That means if you’re using both Codex and Claude, then I would suggest separate integrations for each. Likewise, there are different configurations for local and cloud setups.

Here’s the config for the organisation-wide Claude connection described below. Use the redirect URI shown during your connector setup if it differs from this example:

CREATE OR REPLACE SECURITY INTEGRATION AUTH_MCP_CLAUDE_SQL
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  ENABLED = TRUE
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://claude.ai/api/mcp/auth_callback'
  OAUTH_USE_SECONDARY_ROLES = NONE
  OAUTH_ALLOW_NON_TLS_REDIRECT_URI = FALSE
  IS_AGENTIC = TRUE
  OAUTH_ENFORCE_PKCE = FALSE
  ALLOWED_ROLES_LIST = ('FULL_READER');

That example is for a confidential client which can hold a client secret. For local clients such as Codex or Claude Code, check that client’s current OAuth instructions for its client type, callback URI and PKCE requirements. Only allow a non-TLS callback if it needs an HTTP loopback URI. The Snowflake OAuth settings describe those options; “local” alone doesn’t determine the right values. I’m not covering the individual local client setup steps in this guide.

Claude requests the session:role:all scope, which uses the connecting user’s default role, rather than activating all their roles. Set the test user’s defaults explicitly:

ALTER USER EXAMPLE_ANALYST
  SET DEFAULT_ROLE = 'FULL_READER'
      DEFAULT_WAREHOUSE = 'SOME_WAREHOUSE';

The role must already be granted to that user, and allowed by the integration. The warehouse default is also needed for session initialisation. These defaults can affect other connections, so choose them deliberately. See Snowflake’s OAuth role behaviour for the details.

Organisation-wide Claude access
#

An organisation owner can add the endpoint in Organization settings → Connectors, choosing the Snowflake connector and entering its details. You’ll need the MCP endpoint from above, plus the OAuth client ID and secret. As an administrator, retrieve those with:

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('AUTH_MCP_CLAUDE_SQL');

Use oauth_client_id and oauth_client_secret from the result in the connector settings. DESCRIBE SECURITY INTEGRATION is useful for inspecting the configuration, but doesn’t return the client secret. The function documentation explains the returned values. Treat the secret as a credential; don’t paste it into a chat to get help with the setup.

Anthropic’s custom connector instructions cover the owner and member steps. For this setup, use individual OAuth sign-in; don’t substitute a shared bearer credential and expect per-user permissions to survive.

On the first attempt per user, Claude should open up a browser window, asking the user to grant access to Snowflake for Claude, listing the role that it will use to do so.

Once connected, start by checking the session you’re actually using:

SELECT CURRENT_USER(), CURRENT_ROLE(), CURRENT_WAREHOUSE();

Then query a known table or view and check its results against a trusted source. Getting a plausible answer back is encouraging, but it isn’t the whole test.

2. Then do this with the wider organisation in mind
#

Once you’ve proven out that the basic access works, we’re now going to do the same again - but this time with a more restricted role. Think of the schemas that contain data you want the wider org to explore: probably the schemas backing your existing BI tool or similar.

The important comparison is what each person is allowed to see in BI, rather than everything the BI tool’s shared warehouse account can query. If row or column restrictions live only inside the BI tool, direct SQL won’t inherit them. Put equivalent restrictions into the Snowflake access model before opening up this route.

Do a quick audit for anything particularly confidential or any lingering PII before going further. This is a useful check on the reporting layer anyway.
Work out what the more restricted role is. Something like a BI_READER or GENERAL_READER is a good role. Ensure it has grants on a warehouse and SELECT access to the relevant tables and views, with USAGE on the necessary schemas and databases.
You can reuse the same Snowflake MCP server (ANALYTICS.AI_ACCESS.DIRECT_SQL from our initial example). Check the code we ran to make it, and you’ll see it doesn’t encode anything related to permissions. You will need to grant USAGE on this MCP server (and its containing schema and database) to the restricted role.
You can also reuse the same security integration, although you’ll need to add your restricted role to the ALLOWED_ROLES_LIST:
```
ALTER SECURITY INTEGRATION AUTH_MCP_CLAUDE_SQL SET ALLOWED_ROLES_LIST = ('GENERAL_READER', 'FULL_READER');
```
Grant the restricted role to a test user and set their defaults, as before:
```
GRANT ROLE GENERAL_READER TO USER EXAMPLE_BUSINESS_USER;

ALTER USER EXAMPLE_BUSINESS_USER
  SET DEFAULT_ROLE = 'GENERAL_READER'
      DEFAULT_WAREHOUSE = 'SOME_WAREHOUSE';
```
Adding a role to the OAuth allowlist doesn’t grant it to anyone, or select it for their session. Reconnect with the test user and verify the active role. If you need different roles for different connections, check the client’s role-scope support; separate MCP servers with dedicated roles may be a better fit than relying on clients to select a role they don’t support.
Test the boundaries with that restricted user. Check an approved reporting query succeeds, a known restricted dataset is denied, and row or column policies return what that person should see. In a test schema, check that the SQL tool rejects write requests too. A test from your own admin login doesn’t tell you whether the business user’s access is right.

That’s the setup. The useful lesson for me is that “it returned the right number” and “it connected with the right permissions” are two different checks. I’d make both part of the first test rather than leaving the second until the organisation-wide rollout.

Keep an eye on Snowflake credit consumption after you launch this. You should now have a route for analysts to explore their data, and for the wider team to query the reporting data they’re entitled to see, without another service to run.

Author

Alan Cruickshank

Director @ A14K, Author & Maintainer @ SQLFluff & ex Head of Data & Insights Director @ tails.com. Alan likes building things, breaking things and measuring things.