D2: Account & Governance Week 3

DAY 20

Authentication — MFA, SSO, OAuth, Key-Pair

Authentication is the topic where the C03 has changed the most in the last 18 months. Single-factor password sign-in is being phased out completely. The final enforcement window runs August–October 2026, after which no human user can log in with a password alone and no service user can use a password at all. The exam reflects this shift directly — questions on MFA enforcement, key-pair authentication for service accounts, OAuth for third-party tools, and the TYPE = PERSON | SERVICE | LEGACY_SERVICE user attribute show up consistently. When I run SnowPro sessions for my colleagues, two traps catch nearly everyone: “key-pair authentication still needs a password” (false — the private key signs a JWT, that’s the entire credential) and “the account locator is stable across region migrations” (false — orgname-accountname is the stable format). This is the last concept day of Week 3, and it closes sub-objective 2.1.

🗣️ Plain-English First

Term	Plain meaning
Authentication	Proving you are who you say you are. Different from authorization, which decides what you’re allowed to do once you’re in (that’s RBAC/DAC, from Days 18 and 19).
MFA	Multi-Factor Authentication. Two things: something you know (password) plus something you have (authenticator app, hardware token, passkey).
SSO / SAML	Single Sign-On using the SAML 2.0 standard. You authenticate once to your corporate IdP, and the IdP vouches for you when you reach Snowflake.
IdP	Identity Provider. The external service that holds the identity — Okta, Microsoft Entra ID (Azure AD), Google Workspace, PingFederate.
OAuth	A token-based authorization protocol. A third-party tool receives a scoped token and uses it to call Snowflake on your behalf — without seeing your password.
Key-pair authentication	RSA cryptographic authentication. Generate a public/private key pair, register the public key on the user, sign requests with the private key. No password.
PAT	Programmatic Access Token. A short-lived token used like an API key. One of the newer service-account options.
Authentication policy	A Snowflake object that controls which authentication methods are allowed and whether MFA is required, applied at the account or user level.
User TYPE	A property on the user object: `PERSON` (human, MFA), `SERVICE` (no password allowed), or `LEGACY_SERVICE` (transitional, being phased out by October 2026).

📘

Today’s Concept

Micro-Concept 1: The Four Authentication Methods to Know

The C03 tests recognition. You see a scenario, you pick the method. Learn the table below to the point you can answer in a few seconds.

Method	Best for	How it works
MFA (with password)	Interactive human users signing in to Snowsight or via a password client	Password plus a second factor — passkey, authenticator-app TOTP, Duo push, or hardware token.
SSO / SAML 2.0	Enterprise users with a corporate IdP (Okta, Microsoft Entra ID, and so on)	Federated — Snowflake trusts the IdP’s SAML assertion. No password stored in Snowflake.
OAuth	Third-party tools and apps that need delegated user access	A token, issued by Snowflake (Snowflake OAuth) or by an external IdP (External OAuth), is presented instead of credentials.
Key-pair	Service accounts, ETL pipelines, CI/CD, automated workloads	RSA public/private key. Public key registered on the user; private key signs the JWT used to authenticate. No password involved.

The exam-canonical pairings:

Service account / ETL / CI/CD → Key-pair authentication.
Enterprise user signing in through the corporate IdP → SSO/SAML.
BI tool, partner app, or anything that needs delegated user access → OAuth.
Interactive password user → MFA (mandatory on most accounts now).

Micro-Concept 2: MFA — What’s Mandatory Now

Snowflake is partway through a three-phase rollout that ends single-factor password authentication for good. The phases, taken from the official deprecation timeline:

Phase 1 (Sept 2025 – Jan 2026): MFA enforced for Snowsight logins by password users.
Phase 2 (May 2026 – Jul 2026): All newly created human users must use MFA. No new LEGACY_SERVICE users can be created.
Phase 3 (Aug 2026 – Oct 2026): All existing human users must use MFA for any password-based login. All service users must use key-pair, OAuth, PAT, or WIF. LEGACY_SERVICE fully deprecated.

The end state, as of October 2026:

Human users (TYPE = PERSON or NULL): Password sign-in requires MFA. Single-factor passwords are blocked.
Service users (TYPE = SERVICE): No password allowed at all. Must use key-pair, OAuth, PAT, or WIF.
LEGACY_SERVICE: Transitional bucket for services that still need a password. Going away.
Reader accounts, trial accounts, and Snowflake Postgres are out of scope for this rollout.

MFA second-factor options:

Passkey (FIDO2 / WebAuthn) — Snowflake’s recommended option on modern devices.
Authenticator app (TOTP) — Google Authenticator, Microsoft Authenticator, Authy.
Duo Push — historically the default; still supported.

Enable MFA on a user, then enforce it with an authentication policy:

SQL

-- Inspect users' MFA enrollment
SHOW USERS;
-- Look for HAS_MFA, HAS_PASSWORD, HAS_RSA_PUBLIC_KEY columns

-- An authentication policy that requires MFA for password users
CREATE AUTHENTICATION POLICY require_mfa_pol
  AUTHENTICATION_METHODS = ('PASSWORD', 'SAML')
  MFA_ENROLLMENT = 'REQUIRED'
  MFA_POLICY = (ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION = 'ALL');

-- Apply it at the account level (or attach to a specific user)
ALTER ACCOUNT SET AUTHENTICATION POLICY require_mfa_pol;

One naming note worth knowing for the exam. The older parameter MFA_AUTHENTICATION_METHODS is being replaced by MFA_ENROLLMENT plus ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION. Both names may show up in C03 questions. Recognize either form.

Micro-Concept 3: Key-Pair Authentication — The Service-Account Standard

Whenever a stem describes a service account, ETL job, or CI/CD pipeline, key-pair is the answer. The flow has four steps:

Generate a 2048-bit (or larger) RSA key pair. Two files: rsa_key.p8 (private) and rsa_key.pub (public).
Register the public key on the Snowflake user via ALTER USER … SET RSA_PUBLIC_KEY = '<contents>'.
The client (driver, SDK, SnowSQL, Snowpark) signs a JWT with the private key. Snowflake validates the signature using the registered public key.
No password is sent. No password is required. That’s the trap.

SQL

-- On the user object
ALTER USER etl_service_user
  SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0...';

-- Optional: a second key for zero-downtime rotation
ALTER USER etl_service_user
  SET RSA_PUBLIC_KEY_2 = 'MIIBIjANBgkqhkiG9w0...';

-- Inspect
DESC USER etl_service_user;
-- Look for RSA_PUBLIC_KEY_FP and RSA_PUBLIC_KEY_2_FP (fingerprints)

Four facts to commit to memory:

Key-pair authentication does NOT require a password. This is the single most-tested trap on this topic. The private key signs a JWT. That JWT is the entire credential.
Two public-key slots (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) exist so you can rotate keys with zero downtime — register the new key in the second slot, switch clients over, remove the old key.
The private key can be encrypted with a passphrase. That passphrase is a local secret on the client machine. It is not a Snowflake credential and Snowflake never sees it.
For TYPE = SERVICE users, key-pair (or OAuth / PAT / WIF) is required. Passwords are not an option for service users.

In my EY Guidewire-to-Snowflake project, every pipeline service account uses key-pair. The pattern that scales cleanly: generate the key pair in a secrets manager, register the public key on a dedicated TYPE = SERVICE user, rotate quarterly using the second slot. Anyone who hardcoded passwords two years ago is now scrambling to migrate ahead of the October 2026 deadline.

Micro-Concept 4: SSO/SAML and OAuth — Different Problems

When I run the certification training at EY, this is the slide where everyone stops taking notes and starts asking questions. People mix these up because both involve external identity providers, but they solve different problems.

SSO/SAML is about authentication. You sign in to your corporate IdP — Okta, Microsoft Entra ID, Google Workspace. The IdP issues a SAML assertion to Snowflake saying “this is Alice.” Snowflake trusts the assertion (because the IdP is configured as a SAML security integration) and lets Alice in. Snowflake never sees Alice’s password.

Setup: CREATE SECURITY INTEGRATION … TYPE = SAML2.
Where it shines: Enterprises with central identity management. One credential gets a user into Snowflake, Slack, GitHub, and the rest of the corporate stack.
MFA: By default, SSO users do not trigger Snowflake MFA — Snowflake relies on the IdP to enforce strong authentication. If you want defense-in-depth, layer Snowflake MFA on top using an authentication policy with ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION = 'ALL'.

OAuth is about authorization delegation. A third-party tool — Tableau, Power BI, a partner app — needs to query Snowflake on a user’s behalf. OAuth issues that tool a scoped access token. The tool never sees the user’s password, and the token can be revoked independently of the user account.

Two flavors:
- Snowflake OAuth — Snowflake itself is the authorization server, issuing tokens via CREATE SECURITY INTEGRATION … TYPE = OAUTH.
- External OAuth — tokens come from an external authorization server (Okta, Azure AD, PingFederate, custom IdP) and Snowflake validates them.
Where it shines: BI tools (Tableau, Power BI, Looker), data apps, partner integrations, Snowpark sessions — anywhere you want token-based access instead of stored credentials.

The shortest way to keep them separate: SSO/SAML is for humans logging in. OAuth is for tools acting on behalf of humans.

Micro-Concept 5: Account Identifiers — Two Formats, One Recommended

Every Snowflake account has two identifiers. The exam asks which one is recommended and why.

Format	Example	Stable across…
Organization name + Account name (recommended)	`myorg-myaccount`	Region migrations, replication failover, account moves — stays the same.
Account locator (legacy)	`xy12345.us-east-1.aws`	NOT stable — the locator embeds region and cloud, so it changes if you migrate.

Both formats work for connecting today. The reason orgname-accountname is the recommendation is operational stability. Replication and failover flip an account between regions; connection strings that depend on the locator break the moment that happens. Production drivers, JDBC URLs, BI tool configurations — all of these should use the org-name form. The locator is fine for a one-off worksheet, but never for anything you’d want to survive a region migration.

SQL

-- See both forms for your current account
SELECT
  CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() AS preferred_id,
  CURRENT_ACCOUNT()                                            AS locator_form,
  CURRENT_REGION()                                             AS region;

Micro-Concept 6: LOGIN_HISTORY — Auditing Authentication

The audit trail for who logged in, how, and from where lives in SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY. Retention is 365 days, and per the official view documentation, latency is up to 120 minutes. For real-time auditing, the corresponding INFORMATION_SCHEMA.LOGIN_HISTORY() table function holds 7 days of data with near-zero latency.

SQL

-- Last 20 logins, with the auth method used
SELECT event_timestamp, user_name, client_ip, reported_client_type,
       first_authentication_factor, second_authentication_factor,
       is_success, error_code, error_message
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
ORDER BY event_timestamp DESC
LIMIT 20;

Two columns matter on the exam:

FIRST_AUTHENTICATION_FACTOR — PASSWORD, SAML_ASSERTION, OAUTH_ACCESS_TOKEN, RSA_KEYPAIR, and so on.
SECOND_AUTHENTICATION_FACTOR — DUO, TOTP, WEBAUTHN (passkey), or null if no second factor was used.

This is the source of truth for “did MFA actually fire?” and “is anyone still using single-factor password?” Filter for first_authentication_factor = 'PASSWORD' AND second_authentication_factor IS NULL and you have your remediation list before the deprecation deadlines hit.

⚡

Cheat Sheet

Concept	What to remember
Four auth methods	MFA, SSO/SAML, OAuth, Key-Pair
Service account = ?	Key-Pair authentication. No password.
Enterprise user via IdP = ?	SSO/SAML 2.0. Snowflake trusts the IdP’s assertion.
Third-party BI tool = ?	OAuth. Token-based, password never seen.
Interactive password login = ?	MFA. Mandatory for most accounts now.
Key-pair & passwords	Key-pair does NOT require a password. Private key + JWT = full credential.
RSA key slots	Two: `RSA_PUBLIC_KEY` and `RSA_PUBLIC_KEY_2` — supports zero-downtime rotation.
User TYPE attribute	`PERSON` (humans / MFA), `SERVICE` (no password), `LEGACY_SERVICE` (transitional, being deprecated).
MFA enforcement deadline	Phased rollout Sept 2025 – Oct 2026. All single-factor passwords blocked by end of phase.
Authentication policy	`CREATE AUTHENTICATION POLICY` with AUTHENTICATION_METHODS, MFA_ENROLLMENT, MFA_POLICY. Apply at account or user level.
SSO & MFA	SSO doesn’t require Snowflake MFA by default — IdP handles it. Layer on with `ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION`.
Two OAuth flavors	Snowflake OAuth (Snowflake issues tokens) and External OAuth (Okta/Azure issues tokens).
Account ID — preferred	`orgname-accountname` — stable across region migrations.
Account ID — legacy	`account_locator.region.cloud` — changes if you migrate.
Audit logins	`SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY` — check first/second authentication factor.

🎯 Exam Tip

Five canonical traps on this topic:

(1) “Key-pair authentication requires a password.” FALSE. The private key signs the JWT — that’s the whole credential. A passphrase encrypting the private key on disk is a client-side secret, not a Snowflake credential. This is the single most-tested trap on Day 20 content; if you see “key-pair” and “password” together in an answer, it’s almost always the wrong one. (2) “The account locator stays the same when you migrate to a new region.” FALSE. The locator embeds region and cloud, so it changes on migration. orgname-accountname is the format that survives. (3) “SSO and OAuth are the same thing.” FALSE. SSO/SAML authenticates humans through an IdP. OAuth delegates tool access via a token. Different problems, different protocols. (4) “SERVICE-type users can still use passwords if MFA is enabled.” FALSE. Service users cannot authenticate with passwords at all. Key-pair, OAuth, PAT, or WIF are the only options. (5) “MFA is configured the same way for every user, regardless of auth method.” FALSE. By default MFA applies to password logins; SSO users rely on the IdP. To force MFA on SSO logins too, use ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION = 'ALL' in an authentication policy.

🛠️

Hands-On Lab

Type: Mostly inspection (no objects created; SSO/OAuth/key-pair setup requires external infra) | Time: ~10 minutes | Credits: ~0 (metadata queries) | Prerequisite: ACCOUNTADMIN role; trial accounts may not have populated LOGIN_HISTORY yet.

See both account identifier formats for your account.

SQL

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE lab_xs;

SELECT
  CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() AS preferred_identifier,
  CURRENT_ACCOUNT()                                            AS legacy_locator,
  CURRENT_REGION()                                             AS region;

👀 Observe: The preferred_identifier is the same string after any future region migration. The legacy_locator plus region combination would change. Drivers and BI tools that hardcode the locator break on migration. The org-name form survives it.

Inspect users’ authentication setup.

SQL

SHOW USERS;
-- Columns of interest:
--   HAS_PASSWORD            = TRUE if a password is set
--   HAS_RSA_PUBLIC_KEY      = TRUE if key-pair is configured
--   HAS_MFA                 = TRUE if the user has enrolled in MFA
--   TYPE                    = PERSON | SERVICE | LEGACY_SERVICE | NULL (legacy)
--   DEFAULT_SECONDARY_ROLES = ALL or NONE (default is ALL for new users since BCR 1692)

-- Just your own user, in detail
DESC USER IDENTIFIER(CURRENT_USER());

👀 Observe: The HAS_* columns show which credential types each user can present. In a trial account, your own user is usually HAS_PASSWORD=TRUE, HAS_RSA_PUBLIC_KEY=FALSE, and HAS_MFA reflects whether you’ve enrolled. The TYPE column is the control point for the password-deprecation rollout — this is where the C03 trap “what TYPE allows password use?” lives.

Inspect the account’s authentication parameters.

SQL

SHOW PARAMETERS LIKE '%AUTH%' IN ACCOUNT;
-- Look for ALLOW_CLIENT_MFA_CACHING, ALLOW_ID_TOKEN, ENABLE_UNHANDLED_EXCEPTIONS_REPORTING, etc.

-- Show the current authentication policies in the account (if any)
SHOW AUTHENTICATION POLICIES;

👀 Observe: SHOW AUTHENTICATION POLICIES returns an empty result on a fresh trial account — no policy has been created yet. In a production account you’d see policies that enforce MFA, restrict allowed auth methods (for example, disallow password for certain roles), or both.

Audit recent login attempts — see which auth methods were used.

SQL

SELECT
  event_timestamp,
  user_name,
  client_ip,
  reported_client_type,
  first_authentication_factor,
  second_authentication_factor,
  is_success,
  error_message
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
ORDER BY event_timestamp DESC
LIMIT 20;

👀 Observe: Each row tells you exactly how someone (or some tool) authenticated. FIRST_AUTHENTICATION_FACTOR = 'RSA_KEYPAIR' proves key-pair was used. SAML_ASSERTION proves SSO. OAUTH_ACCESS_TOKEN proves OAuth. ACCOUNT_USAGE latency on this view is up to 120 minutes per the official docs, so very recent logins may not be visible yet. For real-time data, use INFORMATION_SCHEMA.LOGIN_HISTORY() — 7-day retention, near-zero latency.

Concept-only: registering a key pair on a service user. This step requires an actual key pair generated on your machine, so the SQL is commented out. Read it for shape; don’t run it casually in a worksheet.

SQL

-- Concept check only. Generate the key pair locally first:
--   openssl genrsa -out rsa_key.pem 2048
--   openssl rsa -in rsa_key.pem -pubout -out rsa_key.pub
--   cat rsa_key.pub  → copy everything between BEGIN/END headers

/*
CREATE USER etl_service
  TYPE = SERVICE
  DEFAULT_ROLE = some_role
  COMMENT = 'ETL pipeline service account';

ALTER USER etl_service
  SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKC...';

-- Second slot for zero-downtime key rotation
ALTER USER etl_service
  SET RSA_PUBLIC_KEY_2 = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKC...';

-- Confirm the fingerprints
DESC USER etl_service;
-- Look for RSA_PUBLIC_KEY_FP and RSA_PUBLIC_KEY_2_FP
*/

💡 Why concept-only: Running the block would create a service user that needs manual cleanup, and you’d need a real private key to actually test the connection. For C03 purposes, the shape is what matters: TYPE = SERVICE, RSA_PUBLIC_KEY, two slots for rotation, no password set anywhere.

Concept-only: an authentication policy that enforces MFA.

SQL

/*
CREATE AUTHENTICATION POLICY require_mfa_for_passwords
  AUTHENTICATION_METHODS = ('PASSWORD', 'SAML', 'KEYPAIR')
  MFA_ENROLLMENT = 'REQUIRED'
  MFA_POLICY = (ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION = 'ALL')
  COMMENT = 'Force MFA for password and SSO users';

-- Apply at the account level…
ALTER ACCOUNT SET AUTHENTICATION POLICY require_mfa_for_passwords;

-- …or attach to a specific user
ALTER USER alice SET AUTHENTICATION POLICY require_mfa_for_passwords;
*/

💡 Why concept-only: Applying an authentication policy can lock you out of a trial account if you don’t already have MFA enrolled. Read it, recognize the shape, do not run it without a tested fallback path.

No cleanup needed. This lab created no persistent objects.

💡 Pro tip: The production migration path I follow on EY projects: (1) enroll all human users in MFA, (2) move service accounts to TYPE = SERVICE with key-pair, (3) create an authentication policy that allows MFA + key-pair + SAML only, (4) apply that policy at the account level, (5) audit LOGIN_HISTORY for a week to surface anything that still fails before tightening further. Tighten before October 2026, not after.

❄️

Snowflake Documentation

Official docs for today’s topics.

❄️

Authentication Overview

docs.snowflake.com/en/user-guide/admin-security-fed-auth-overview

→

❄️

Multi-Factor Authentication (MFA)

docs.snowflake.com/en/user-guide/security-mfa

→

❄️

Key-Pair Authentication

docs.snowflake.com/en/user-guide/key-pair-auth

→

🔗

External References

Account identifiers, password deprecation, OAuth.

Docs Snowflake

Account Identifiers (org-name vs locator)

Docs Snowflake

Single-Factor Password Deprecation Rollout

Docs Snowflake

Snowflake OAuth & External OAuth

❓

Practice Questions

Q1: An organization wants to run nightly ETL jobs from a CI/CD pipeline into Snowflake. The service account performing the jobs should authenticate without storing or transmitting any password. Which authentication method is the recommended choice? ▸

Options:

A. MFA with a TOTP authenticator app
B. SAML 2.0 single sign-on
C. Key-pair authentication
D. Password with a long, random value

✅ Answer: C

Why C: Key-pair authentication is the canonical answer for any non-interactive service-account scenario. The private key signs a JWT and Snowflake validates the signature using the registered public key — nothing about a password enters the flow. Since the August–October 2026 phase of the password-deprecation rollout, service users (TYPE = SERVICE) are required to use key-pair, OAuth, PAT, or WIF; passwords are not an option for them at all.

Why not A: MFA requires an interactive second factor (push notification, TOTP code, passkey) — impractical for an unattended CI/CD job. The exam trap here is “MFA is the secure choice, therefore the right one.” MFA secures humans, not pipelines.

Why not B: SAML/SSO authenticates humans through an external IdP. There’s no human to redirect in a nightly job.

Why not D: Service users cannot authenticate with passwords once the deprecation phases complete, and even setting that aside, the stem explicitly forbids storing or transmitting a password.

Q2: Which statement about Multi-Factor Authentication (MFA) in Snowflake is TRUE? ▸

Options:

A. MFA is enforced uniformly for all users regardless of authentication method, including SSO logins, by default.
B. MFA is enforced per-user by default; SSO users rely on the identity provider for strong authentication unless an authentication policy specifies otherwise.
C. MFA can only be configured for ACCOUNTADMIN users.
D. MFA is a paid add-on available only on Enterprise edition or higher.

✅ Answer: B

Why B: Snowflake enforces MFA on password-based logins (per-user enrollment), but by default does not require Snowflake MFA for SSO users — the IdP is trusted to handle strong authentication. To layer Snowflake MFA on top of SSO, you create an authentication policy with ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION = 'ALL'.

Why not A: The exam trap. Uniform enforcement sounds right but is wrong — default behavior is method-dependent. SSO users skip Snowflake MFA by default.

Why not C: MFA is available for every user. ACCOUNTADMIN is strongly recommended to use it, but it is not restricted to that role.

Why not D: MFA is available across every edition. It is never a paid feature.

Q3: A team wants to integrate Tableau with Snowflake so that Tableau queries Snowflake on behalf of the logged-in user, without Tableau ever seeing the user’s Snowflake password. Which authentication mechanism is the right choice? ▸

Options:

A. Key-pair authentication for each Tableau user
B. OAuth (Snowflake OAuth or External OAuth)
C. SAML SSO — the same login is used for Snowflake and Tableau
D. Storing each user’s Snowflake password in Tableau’s encrypted credential store

✅ Answer: B

Why B: OAuth is purpose-built for third-party tools that need delegated access to Snowflake. Tableau redirects the user to authenticate, receives a scoped access token, and uses that token to query Snowflake on the user’s behalf. The user’s password is never exposed to Tableau, and tokens can be revoked independently of the user account.

Why not A: Key-pair could technically work, but distributing private keys to every Tableau user is operationally heavy and not how BI tools are meant to integrate. The exam trap: “key-pair is secure, therefore it’s right.” Security alone isn’t the test — fit for purpose is.

Why not C: SSO/SAML solves a different problem. It authenticates the user, not the tool. Tableau may use SSO to log the user in to Tableau, but it still needs a separate mechanism to call Snowflake on their behalf — that mechanism is OAuth.

Why not D: Storing user passwords in a downstream tool is the exact anti-pattern OAuth was invented to eliminate.

Q4: A company is planning to migrate its Snowflake account from one cloud region to another in the future. To avoid breaking connection strings on migration, which account identifier format should they use when configuring drivers and applications? ▸

Options:

A. Account locator with region and cloud (e.g., xy12345.us-east-1.aws)
B. Organization name and account name (e.g., myorg-myaccount)
C. The Snowflake account URL with the region embedded
D. The internal account ID returned by CURRENT_SESSION()

✅ Answer: B

Why B: The orgname-accountname format is stable across region migrations and replication failover. The organization name and account name do not change when the account physically moves between regions, so connection strings using this format keep working after a migration.

Why not A: The account locator embeds the region and the cloud provider. Both of those change on a region migration, so any driver hardcoded to the locator breaks the moment failover happens. This is the documented trap on account identifiers and a frequent C03 stem.

Why not C: The account URL contains the locator and the region, so it inherits the same instability as option A.

Why not D: CURRENT_SESSION() returns a session ID for the current connection, not an account identifier usable in a connection string.

Q5: A security administrator wants to identify which authentication methods are being used on their account — specifically, whether anyone is still logging in with a single password and no second factor. Which view should they query? ▸

Options:

A. SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
B. SNOWFLAKE.ACCOUNT_USAGE.SESSIONS
C. SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
D. SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS

✅ Answer: C

Why C: LOGIN_HISTORY records every authentication attempt and includes FIRST_AUTHENTICATION_FACTOR (PASSWORD, SAML_ASSERTION, OAUTH_ACCESS_TOKEN, RSA_KEYPAIR…) and SECOND_AUTHENTICATION_FACTOR (DUO, TOTP, WEBAUTHN, or null). Filtering for first_authentication_factor = 'PASSWORD' AND second_authentication_factor IS NULL surfaces exactly the single-factor password logins the admin is looking for. This is the standard pre-deprecation audit query.

Why not A: QUERY_HISTORY records SQL execution, not authentication events. A user who logged in but never ran a query won’t appear there.

Why not B: SESSIONS shows active sessions but does not expose which authentication method opened each one.

Why not D: GRANTS_TO_USERS shows the authorization side (which roles are granted to whom). The question is about authentication. Re-read Day 18 if the difference is fuzzy.

📝 Recap

Today you learned: Four authentication methods to recognize on the exam — MFA (password + second factor, now mandatory for most human users), SSO/SAML 2.0 (federated via an external IdP, default doesn’t trigger Snowflake MFA), OAuth (Snowflake OAuth or External OAuth, used by third-party tools and BI clients for token-based delegated access), and Key-Pair (RSA, the standard for service accounts; no password required — the most-tested trap). The new TYPE attribute on users (PERSON, SERVICE, LEGACY_SERVICE) is the control point for the single-factor password deprecation, with final enforcement August–October 2026. Authentication policies (CREATE AUTHENTICATION POLICY … AUTHENTICATION_METHODS, MFA_ENROLLMENT, MFA_POLICY) let admins enforce auth requirements at the account or user level. Account identifiers come in two forms: orgname-accountname (recommended, stable across migrations) and account_locator.region.cloud (legacy, changes if you migrate). Audit logins via SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY, where FIRST_AUTHENTICATION_FACTOR and SECOND_AUTHENTICATION_FACTOR reveal exactly how each session was authenticated.

Key takeaway: Authentication on Snowflake is mid-transformation. The scenario-to-method mapping has been stable for years (service = key-pair, IdP-user = SAML, BI tool = OAuth, interactive password = MFA), but the backdrop has shifted from “MFA is optional” to “MFA is mandatory and passwords are going away.” The user TYPE attribute and the authentication-policy object are both recent C03 additions and both show up in the question pool.

Tomorrow (Day 21) — Week 3 Recap + Mixed Quiz (10 Qs). Sub-objective 1.6 (Snowpark + Cortex + Snowflake ML) and sub-objective 2.1 (RBAC/DAC + roles + auth) get consolidated into a single mixed quiz that mirrors the real exam’s pacing: shorter stems, fact-recall heavy, 2–3 scenario questions, 2–3 multi-selects. Then Week 4 opens Domain 2 in earnest — network policies, masking and row access policies, governance, encryption, and cost monitoring.

← Day 19 All 50 Days Day 21 →

About the Author

Abhay Krishnan

Senior Data & AI Consultant

Connect on LinkedIn

With over five years of data engineering experience at EY and Infosys, Abhay Krishnan specializes in building scalable data pipelines and cloud warehousing solutions. He is a certified SnowPro Core professional, alongside credentials in AWS and Azure. Abhay created this 50-day track to solve a problem he faced firsthand: the lack of a structured, free resource for Snowflake certification prep. Follow him on LinkedIn for more data engineering insights.

Authentication — MFA, SSO, OAuth, Key-Pair

Today’s Concept

Micro-Concept 1: The Four Authentication Methods to Know

Micro-Concept 2: MFA — What’s Mandatory Now

Micro-Concept 3: Key-Pair Authentication — The Service-Account Standard

Micro-Concept 4: SSO/SAML and OAuth — Different Problems

Micro-Concept 5: Account Identifiers — Two Formats, One Recommended

Micro-Concept 6: LOGIN_HISTORY — Auditing Authentication

Cheat Sheet

Hands-On Lab

Snowflake Documentation

External References

Practice Questions

Topics