SNOW-745025: Error:OAuth access token expired after JWT Token expires

[sagar-raythatha]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using? - 3.10.8
2. What operating system and processor architecture are you using? - macOS-12.6-arm64-arm-64bit
3. What are the component versions in the environment (pip freeze)?

snowflake-connector-python==2.8.3
snowflake-sqlalchemy==1.4.3
SQLAlchemy==1.4.46

5. What did you do?
   Trying to connect via Engine and sessionmaker, it is expiring the token after 60 mins.

engine = create_engine(
            URL(
                account="account_name",
                user=username,
                role="role_name",
                database="database_name",
                warehouse="warehouse",
                authenticator="oauth",
                schema="schema",
                token=token,
            )
        )
sessionmaker(autocommit=False, autoflush=False, bind=engine)

6. What did you expect to see?
   There could be any one of two solutions should be present:
   i. Specific exception, that we can catch this error and connect again with the refreshed token
   ii. Support for SQL Alchemy event listener which might help us get the token every time it tries to connect

7. Can you set logging to DEBUG and collect the logs?

[sfc-gh-aling]

hey @sagar-raythatha , thanks for reaching out.
I would love to help, can you share me with the exception you got as well as the loggings, also is there a minimal reproducible script?

[sagar-raythatha]

@sfc-gh-aling
Thank you for looking into it.

Exception:
snowflake.connector.errors.DatabaseError: 250001 (08001): Failed to connect to DB: <our tenant>.gcp.snowflakecomputing.com:443. OAuth access token expired. [d08e9a54-252a-415b-bd86-bcf63d750646]

It is the script which gets a token from Okta, and passing that token to Snowflake for authentication. While Okta stay connected for 20+ hours (based on application config in Okta) the access_token provided by the okta is valid for 60 mins. So after 60 mins of time if engine connection it gets expired and throws the exception above.

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

import time
from datetime import datetime

def get_sf_engine(username: str, token: str) -> create_engine:
    # Use URL object for better readability
    url = URL(
        account="account",
        user=username,
        role="role",
        database="database",
        warehouse="warehouse",
        authenticator="oauth",
        schema="schema_name",
        token=token,
    )
    # Create new engine and store it in the session storage with creation time
    snow_engine = create_engine(url)
    return snow_engine


def get_sf_session(username: str, oauth_access_token: str) -> sessionmaker:
    # Get engine from the function above
    snow_engine = get_sf_engine(username, oauth_access_token)
    # Create session and return
    SnowSession = sessionmaker(autocommit=False, autoflush=False, bind=snow_engine)
    return SnowSession()

try:
    session = get_sf_session(username, token)
    results = session.execute("select current_version()").fetchone()
    print(results[0])
except Exception as e:
    print(e)
    raise e

[sfc-gh-dszmolka]

if this is still an unresolved issue, would it be possible to try adding

        authenticator="oauth",
..
        client_session_keep_alive=True,
        client_session_keep_alive_heartbeat_frequency=900

to the. connection options and see if it helps?

which should send a keepalive to Snowflake every hour (by default) but with client_session_keep_alive_heartbeat_frequence you can set the frequency of these keepalives between 900 - 3600 seconds.