I was recently trying to setup an OAuth Authentication in Snowflake, to authenticate users and managed identities from Azure AD, and found a couple issues trying to follow the documentation.

I am an absolute Snowflake novice, so while some of these may be obvious to Snowflake experts, these are my notes that hopefully will help others do this.

Problem #1: The ANALYST role isn't default.

According to the documentation, the example App Role provides the session:role:analyst scope.

That role doesn't exist by default. The doc writer was referencing this other page in the docs, which clearly says "Maps to a custom Snowflake role. For example, if your custom role is ANALYST, your scope is session:role:analyst."

Problem #2: If you're getting an error like snowflake.connector.errors.DatabaseError: 250001 (08001): Failed to connect to DB: ***.***.snowflakecomputing.com:443. Incorrect username or password was specified., remove the host field from your connection config.

According to the documentation, Snowflake says to use an example configuration like below:

ctx = snowflake.connector.connect(
   user="<username>",
   host="<hostname>",
   account="<account_identifier>",
   authenticator="oauth",
   token="<external_oauth_access_token>",
   warehouse="test_warehouse",
   database="test_db",
   schema="test_schema"
)

However, this doesn't work. According to the connector docs, the host field should not be used.

A more complete example is:

import base64
jwt = access_token.split('.')[1]
sub = json.loads(base64.b64decode(jwt + "==").decode('utf-8'))['sub']

# Gets the version
ctx = snowflake.connector.connect(
    user = sub,  # 'sub' attribute in JWT
    account = "aebtcef-yh51098",  # Account from Admin > Accounts
    authenticator = "oauth",
    token = access_token,
    warehouse = "COMPUTE_WH",  # Default warehouse
    database = "SNOWFLAKE_SAMPLE_DATA",  # Built-in sample data
    schema = "WEATHER",  # Built-in sample data
)

Note: the reasons for using sub and not upn as per the Snowflake example, is that sub doesn't change when the username does (i.e., name changes), and sub is present on Managed Identity access tokens, whereas upn isn't.

Problem #3: Snowflake doesn't allow hyphens in the 'User Name' field.

If you're trying to use the sub attribute from the JWT to authenticate (like in the below example), you'll see that the sub will map to the login_name attribute in Snowflake. When you go to the admin portal, if you try to create a user with User Name containing hyphens, you'll get the error: "Contains invalid characters. Consider enclosing in double quotes."

But this is an easy fix: the User Name can be anything; the login_name attribute is hidden below the Advanced User Options fold on the New User page.

create security integration external_oauth_azure_1
    type = external_oauth
    enabled = true
    external_oauth_type = azure
    external_oauth_issuer = 'https://sts.windows.net/{tenant_id}/'
    external_oauth_jws_keys_url = 'https://login.microsoftonline.com/{tenant_id}/discovery/v2.0/keys'
    external_oauth_audience_list = ('https://{tenant_domain}.onmicrosoft.com/{app_id}')
    external_oauth_token_user_mapping_claim = 'sub'
    external_oauth_snowflake_user_mapping_attribute = 'login_name';