Understanding Snowflake MFA

Published in

Snowflake Builders Blog: Data Engineers, App Developers, AI/ML, & Data Science

5 min readJun 16, 2024

Strawberry bed in backyard, Markham ON, June 16 2024

Multi-Factor Authentication (MFA) capability in Snowflake uses more than one factor to authenticate user when accessing Snowflake. The key point of MFA is to use at least one more dynamic factor in additional to traditional static factor for enhanced security.

Traditionally Snowflake uses username/password or PKI keypair static factor to authenticate user access. Dynamic factors are one time access credentials which require mobile phone interaction (key in code or use approval button in mobile app) during access process.

1 Major Snowflake access methods

We’re talking the statid first factor — username and password which can be used for Snowsight, SnowSQL and programmatic access to Snowflake. Another first factor is PKI keypair which can be used in SnowSQL command line and programmatic access. Some programmatic access sample Python code are as follows.

from snowflake.snowpark.session import Session

# Connect Snowflake using user/pwd
def create_session_object_user_pwd():
   connection_parameters = {
      "account": "hbvoopz-xjb94615",
      "user": "fengliplatform",
      "password": "xxxx",
      "role": "sysadmin",
      "warehouse": "compute_WH",
      "database": "fengdb",
      "schema": "public"
   }
   session = Session.builder.configs(connection_parameters).create()
   print(session.sql('select current_warehouse(), current_database(), current_schema()').collect())
   return session

session_user_pwd = create_session_object_user_pwd()

# Connect Snowflake using interactive SSO via popped browser
def create_session_object_sso():
   connection_parameters = {
      "account": "hbvoopz-xjb94615",
      "user": "fengliplatform",
      "authenticator": "externalbrowser",
      "role": "sysadmin"
   }
   session = Session.builder.configs(connection_parameters).create()
   print(session.sql('select current_warehouse(), current_database(), current_schema()').collect())
   return session

session_sso = create_session_object_sso()

# Connecto Snowflake using PKI keypair
# full code: https://community.snowflake.com/s/article/How-to-create-a-session-via-Snowpark-python-using-key-pair-authentication-in-jupyter
def create_session_object_pki():
   my_private_key = <read local private key file>
   connection_parameters = {
      "account": "hbvoopz-xjb94615",
      "user": "fengliplatform",
      "private_key": ,my_private_key
      "role": "sysadmin"
   }
   session = Session.builder.configs(connection_parameters).create()
   print(session.sql('select current_warehouse(), current_database(), current_schema()').collect())
   return session

session_pki = create_session_object_pki()

2 How does Snowflake MFA go with each access method?

Snowflake MFA is a one time code or a approval button that is sent to user’s device (mostly mobile phone) during authentication process. User interaction using mobile phone is needed to approve the authentication as a second (dynamic) factor.

Snowflake MFA can be enabled for individual users only by themselves. Currently Snowsight logged-in user can “enroll” MFA on “My Profile” page: Scroll down to allocate “Multi-factor Authentication” and click “Enroll” button to start.

Following the instruction, we’ll see support devices where we can see “Mobile phone” is recommended device.

Following the instruction to install Duo mobile app (Snowflake MFA only supports Duo as of now June 2024.), scan QR code in next page and continue.

Each individual user has to “enroll” for themselves. But users can not disable MFA for themselves, it has to be accountadmin to disable MFA for given user or bypass MFA for a while in case user reports mobile phone lose etc.

2.1 Snowsight login process with MFA enabled

Then Snowflake shows “Duo” MFA page. Choose one method to receive second factor and finish login.

For example, mobile phone received a “Duo push” like this:

2.2 SnowSQL process with MFA enabled

Following example shows SnowSQL uses SSO and MFA login.

# SnowSQL login - popped out browser is waiting for SSO authentication now
PS C:\Windows\System32> snowsql -a <account name> -u fengliplatform 
--authenticator externalbrowser
Initiating login request with your identity provider. A browser window should have opened for you to complete the login. If you can't see it, check existing browser windows, or your OS settings. Press CTRL+C to abort and try again...
Going to open: https://sso.okta....

And then SnowSQL tells “Waiting for MFA to approval…” while you do approval with your phone.

PS C:\Windows\System32> snowsql -a <account name> -u fengliplatform 
--authenticator externalbrowser
Initiating login request with your identity provider. A browser window should have opened for you to complete the login. If you can't see it, check existing browser windows, or your OS settings. Press CTRL+C to abort and try again...
Going to open: https://sso.okta....

* SnowSQL * v1.2.31 Waiting for MFA to approval -
Type SQL statements or !help
fengliplatform#(no warehouse)@(no database).(no schema)>

Alternatively, parameter “mfa-passcode” can be used to provide second factor so login in one shot.

PS C:\Windows\System32> snowsql -a <account name> -u fengliplatform -p xxxx 
--mfa-passcode yyyy

2.3 Programmatic access adding MFA?

Programmatic access + MFA? NO.

Programmatic access is mainly for machine to machine communication for automation workflow. MFA asks human interaction for a dynamic one-time use code. They ayre for different use cases.

Programmatic access does support OAuth access token for authorization user. This access code has an expiry time for example 1800 seconds. So this access token can be used to call Snowflake API in 30 minutes as a bearer token. While that expires, user interaction is needed to do consent again.

Or if there is a refresh token also generated for the client the refresh token can be used to ask a new access token automatically until refresh token expires itself for example 86400 seconds.

Find a little more in this post or check Tableau doc to see how it’s connecting Snowflake using OAuth access/refresh token.

But this is NOT MFA — it’s one factor and it’s authorization (not authentication).