Snowflake connection patterns on Connect using service account OAuth authentication

These patterns enable you to access Snowflake data in Posit Connect using Connect Service Account OAuth Authentication. They include both Python and R code examples.

Choose a different pattern by referring to the table in Choose a connection for Snowflake.

Not sure which pattern to use?

For an overview of credential types and scope in different environments, see Credential types and Managing data access from development to deployment.

Overview

Administrator configuration required

Your Connect administrator must configure a Snowflake service account OAuth integration in Connect. Your Snowflake administrator must also create a service account and grant it appropriate permissions to access Snowflake resources.

For detailed setup instructions, see the Snowflake OAuth Integration page in the Connect Admin Guide.

Service account integrations allow published content to use the credentials of a Snowflake service account when accessing Snowflake resources. All viewers have the same experience when interacting with the content. Service account integrations support both interactive and rendered content types, as well as content with access type Anyone - no login required and Share links.

Required steps when publishing

You must enable the OAuth integration for your specified content using the Access tab in the Content Settings pane in Connect, or by selecting the integration upon deployment.

For more details, refer to the Adding Integrations section of the Connect User Guide.

Prerequisites

The connection patterns in this guide use the snowflake-connector-python package to connect to Snowflake. The connector integrates with data frame libraries like pandas and polars. This example uses pandas.

Use the posit-sdk package to retrieve the service account OAuth credentials at runtime on Connect.

Install the required Python packages:

uv add snowflake-connector-python posit-sdk pandas

pip install snowflake-connector-python posit-sdk pandas

The connection patterns in this guide use odbc and the odbc::snowflake() function to connect to Snowflake via an ODBC driver. Use the connectcreds package to authenticate with service account OAuth credentials.

Administrator configuration required

An administrator must install and configure database drivers on the server before you can use them in your code. See the Data sources admin guide for more information.

Install odbc and connectcreds:

install.packages(c("odbc", "connectcreds"))

The example below also uses dplyr.

install.packages("dplyr")

Connection patterns

To authenticate with Snowflake using service account credentials, your code must retrieve the content session token from the CONNECT_CONTENT_SESSION_TOKEN environment variable. Connect automatically provides this environment variable when the OAuth integration is enabled. Your code must then exchange it for a Snowflake OAuth access token. The posit.connect.external.snowflake module from the posit-sdk package simplifies this token exchange process, with PositAuthenticator handling the exchange when deployed to Connect.

This code example uses different authentication approaches depending on the environment:

PositAuthenticator retrieves and exchanges the content session token when deployed to Connect
connection_name="workbench" uses Workbench managed credentials when running on Workbench
EXTERNALBROWSER provides browser-based authentication for local development

This approach allows the same code to run across all three environments.

import os

import pandas as pd
import snowflake.connector
from posit.connect.external.snowflake import PositAuthenticator

# Environment variables
database = os.getenv("SNOWFLAKE_DATABASE", "LENDING_CLUB")
schema = os.getenv("SNOWFLAKE_SCHEMA", "PUBLIC")

# Determine if the app is running on Connect or Workbench
on_connect = os.getenv("POSIT_PRODUCT") == "CONNECT"
on_workbench = os.getenv("POSIT_PRODUCT") == "WORKBENCH"

if on_connect:
    # CONNECT_CONTENT_SESSION_TOKEN is automatically set by Connect
    # when a service account OAuth integration is enabled.
    content_session_token = os.environ["CONNECT_CONTENT_SESSION_TOKEN"]
    # SNOWFLAKE_ACCOUNT is automatically set by the Posit Connect service
    # account integration.
    account = os.environ["SNOWFLAKE_ACCOUNT"]
    auth = PositAuthenticator(content_session_token=content_session_token)

    con = snowflake.connector.connect(
        account=account,
        database=database,
        schema=schema,
        authenticator=auth.authenticator,
        token=auth.token,
    )
elif on_workbench:
    con = snowflake.connector.connect(
        connection_name="workbench",
        database=database,
        schema=schema,
    )
else:
    # Local development - browser-based authentication
    # USER is only required when running locally with external browser auth
    user = os.environ["SNOWFLAKE_USER"]
    account = os.environ["SNOWFLAKE_ACCOUNT"]
    con = snowflake.connector.connect(
        user=user,
        account=account,
        database=database,
        schema=schema,
        authenticator="externalbrowser",
    )

# Query data
query = "SELECT * FROM LOAN_DATA LIMIT 10;"
df = pd.read_sql(query, con)
df

The odbc::snowflake() function is the recommended approach for connecting to Snowflake from R content developed on Workbench and published to Connect. It handles both Workbench managed credentials and Connect service account OAuth credentials without requiring code changes.

The only difference between this code and what is shown in the Posit Workbench Managed Credentials section is the addition of library(connectcreds), which automatically detects the environment and retrieves the appropriate credentials at runtime.

See the snowflake() helper function in the odbc package documentation for more information.

library(dplyr)
# When deployed to Posit Connect connectcreds needs
# to be loaded even though it is never called directly.
library(connectcreds)

create_connection <- function() {
    # Environment variables
    database <- "LENDING_CLUB"
    schema <- "PUBLIC"

    on_connect <- Sys.getenv("POSIT_PRODUCT") == "CONNECT"
    on_workbench <- Sys.getenv("POSIT_PRODUCT") == "WORKBENCH"

    if (on_workbench) {
        # When running on Workbench, managed credentials will be used to
        # authenticate with Snowflake.
        con <- DBI::dbConnect(
            odbc::snowflake(),
            database = database,
            schema = schema
        )
    } else if (on_connect) {
        # The connectcreds package will automatically retrieve the content session
        # token and use it to authenticate with Snowflake when a connection is created.
        con <- DBI::dbConnect(
            odbc::snowflake(),
            database = database,
            schema = schema
        )
    } else {
        # Local development - use browser-based authentication
        con <- DBI::dbConnect(
            odbc::snowflake(),
            user = Sys.getenv("SNOWFLAKE_USER"),
            account = Sys.getenv("SNOWFLAKE_ACCOUNT"),
            database = database,
            schema = schema,
            authenticator = "externalbrowser"
        )
    }
    return(con)
}


ui <- fluidPage(
    titlePanel("Snowflake Data"),
    DT::dataTableOutput("data")
)


server <- function(input, output, session) {
    con <- create_connection()

    df <- tbl(con, "LOAN_DATA") |>
        select(ID, LOAN_AMNT, TERM, INT_RATE, GRADE) |>
        filter(!is.na(LOAN_AMNT)) |>
        arrange(desc(LOAN_AMNT)) |>
        head(10) |>
        collect()

    output$data <- DT::renderDataTable(df)
}

shinyApp(ui = ui, server = server)