PowderInsights | Snowflake Authentication Hardening: MFA Enforcement, Service Users, Key Pairs, PATs, and Workload Identity

Snowflake spent 2025 closing the door on single-factor passwords. Multi-factor authentication is now enforced for human users signing in with a password, TYPE = SERVICE users cannot use passwords at all, and the platform has grown a proper set of non-password credentials: key pairs, programmatic access tokens (PATs), and workload identity federation (WIF). If your account still has a shared ETL_USER with a password in a Jenkins credential store, the change window has closed.

This tutorial walks through a hardened identity setup: classifying users, writing authentication policies, issuing the right credential for each caller, federating cloud workloads without secrets at all, and auditing the result. Every statement below runs as ACCOUNTADMIN or a role with MANAGE ACCOUNT LEVEL POLICIES unless noted.

Step 1: classify every user by type

The single highest-value change is setting TYPE on every user. It is not cosmetic — Snowflake enforces different rules per type.

`TYPE`	Who it is	Password allowed?	MFA	Typical credential
`PERSON`	A human being	Yes, but MFA is enforced	Required (Duo or a registered authenticator)	SSO / SAML, or password + MFA
`SERVICE`	Automation: dbt, Airflow, Fivetran, CI	No	N/A	Key pair, PAT, or WIF
`LEGACY_SERVICE`	Service account not yet migrated	Yes	Exempt from MFA enforcement	Password (escape hatch, treat as debt)
`SNOWFLAKE_SERVICE`	Snowflake-internal	—	—	—
`NULL`	Unclassified — treated as `PERSON`	Yes	Enforced	—

Start by finding out what you have:

SELECT name, type, has_password, has_rsa_public_key, has_mfa,
       ext_authn_duo, disabled, last_success_login, owner
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
WHERE deleted_on IS NULL
ORDER BY type NULLS FIRST, last_success_login DESC;

Anything with has_password = TRUE and no type is a landmine: either a human who needs MFA or a robot that needs a key pair. Classify them:

ALTER USER jsmith        SET TYPE = PERSON;
ALTER USER svc_dbt_prod  SET TYPE = SERVICE;   -- fails if the user still has a password
ALTER USER svc_legacy_bi SET TYPE = LEGACY_SERVICE;

ALTER USER ... SET TYPE = SERVICE will error while a password is set, which is the point. Unset it after the new credential is proven:

ALTER USER svc_dbt_prod UNSET PASSWORD;
ALTER USER svc_dbt_prod SET TYPE = SERVICE;

Give yourself a rule: LEGACY_SERVICE requires a ticket and an expiry date. It is a migration state, not a destination.

Step 2: authentication policies as the enforcement layer

An authentication policy declares which methods and client types are acceptable. Attach one to the account for the baseline and override per user or role.

-- Humans: SSO first, password+MFA as a fallback, no client-side caching of MFA
CREATE OR REPLACE AUTHENTICATION POLICY sec.policies.humans_ap
  AUTHENTICATION_METHODS = ('SAML', 'PASSWORD', 'MFA')
  MFA_AUTHENTICATION_METHODS = ('PASSWORD')
  MFA_ENROLLMENT = REQUIRED
  CLIENT_TYPES = ('SNOWFLAKE_UI', 'DRIVERS', 'SNOWSQL')
  COMMENT = 'Baseline for TYPE=PERSON users';

-- Automation: key pair or OAuth/WIF only, and never the UI
CREATE OR REPLACE AUTHENTICATION POLICY sec.policies.service_ap
  AUTHENTICATION_METHODS = ('KEYPAIR', 'OAUTH')
  CLIENT_TYPES = ('DRIVERS', 'SNOWSQL')
  COMMENT = 'Baseline for TYPE=SERVICE users';

ALTER ACCOUNT SET AUTHENTICATION POLICY sec.policies.humans_ap;
ALTER USER svc_dbt_prod SET AUTHENTICATION POLICY sec.policies.service_ap;

Two traps worth knowing before you attach anything to the account:

Lock yourself out check. Test the policy on one user first. An account-level policy that omits PASSWORD while nobody has SSO configured will strand every admin. Keep one break-glass user with a documented exemption and a monitored login alert.
CLIENT_TYPES is not a firewall. It restricts which client families may authenticate; network policies still do the IP work. Use both.

Add a password policy for whatever passwords remain, and lengthen it aggressively — long passwords hurt less when humans use SSO anyway:

CREATE OR REPLACE PASSWORD POLICY sec.policies.strict_pp
  PASSWORD_MIN_LENGTH = 16
  PASSWORD_MIN_NUMERIC_CHARS = 1
  PASSWORD_MAX_AGE_DAYS = 180
  PASSWORD_MAX_RETRIES = 5
  PASSWORD_LOCKOUT_TIME_MINS = 30;

ALTER ACCOUNT SET PASSWORD POLICY sec.policies.strict_pp;

Step 3: key-pair auth for tools that hold a secret

Key pairs remain the default for dbt, Airflow, Spark, and CI runners. Generate an encrypted private key and register only the public half:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes-256-cbc -inform PEM -out sf_dbt_prod.p8
openssl rsa -in sf_dbt_prod.p8 -pubout -out sf_dbt_prod.pub

ALTER USER svc_dbt_prod SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...';
DESC USER svc_dbt_prod;   -- check RSA_PUBLIC_KEY_FP

Rotation is the part teams skip. Snowflake supports two keys concurrently so you never need a maintenance window:

ALTER USER svc_dbt_prod SET RSA_PUBLIC_KEY_2 = 'MIIBIjANBgkqh...new...';
-- deploy the new private key to the caller, confirm logins succeed, then:
ALTER USER svc_dbt_prod UNSET RSA_PUBLIC_KEY;
ALTER USER svc_dbt_prod SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...new...';
ALTER USER svc_dbt_prod UNSET RSA_PUBLIC_KEY_2;

Put that four-statement dance in a runbook with a 90-day calendar reminder, or wire it into your secret manager's rotation hook.

Step 4: programmatic access tokens for the awkward cases

Some callers cannot do RSA signing: a REST client, a low-code tool, a notebook, an internal script hitting the SQL API. PATs are the sanctioned answer — a bearer token scoped to a user, bounded by a role and an expiry, and revocable on its own without touching the user.

-- The user's authentication policy must allow PROGRAMMATIC_ACCESS_TOKEN
CREATE OR REPLACE AUTHENTICATION POLICY sec.policies.pat_ap
  AUTHENTICATION_METHODS = ('PROGRAMMATIC_ACCESS_TOKEN', 'KEYPAIR')
  PAT_POLICY = (
    DEFAULT_EXPIRY_IN_DAYS = 30,
    MAX_EXPIRY_IN_DAYS = 90,
    NETWORK_POLICY_EVALUATION = ENFORCED_REQUIRED
  );

ALTER USER svc_sqlapi SET AUTHENTICATION POLICY sec.policies.pat_ap;

ALTER USER svc_sqlapi ADD PROGRAMMATIC ACCESS TOKEN reporting_api
  ROLE_RESTRICTION = 'RPT_READER'
  DAYS_TO_EXPIRY = 30
  COMMENT = 'Ticket SEC-412, owner: data-platform';

The token secret is returned once, at creation. Capture it into your secret manager in the same automation step — there is no "show me again."

curl -X POST "https://<account>.snowflakecomputing.com/api/v2/statements" \
  -H "Authorization: Bearer $SNOWFLAKE_PAT" \
  -H "X-Snowflake-Authorization-Token-Type: PROGRAMMATIC_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"statement":"SELECT CURRENT_USER(), CURRENT_ROLE()","warehouse":"RPT_WH"}'

Always set ROLE_RESTRICTION. Without it a PAT inherits everything the user can do, which turns a leaked token into an account incident rather than a scoped one. Inventory and revoke:

SHOW USER PROGRAMMATIC ACCESS TOKENS FOR USER svc_sqlapi;
ALTER USER svc_sqlapi REMOVE PROGRAMMATIC ACCESS TOKEN reporting_api;

Require NETWORK_POLICY_EVALUATION = ENFORCED_REQUIRED so a PAT only works from your egress ranges. That single setting is what makes PATs defensible to a security reviewer.

Step 5: workload identity federation — no secret at all

The best credential is the one you never store. If the caller runs on AWS, Azure, GCP, or GitHub Actions with an OIDC identity, Snowflake can trust that identity directly.

CREATE USER svc_airflow_prod
  TYPE = SERVICE
  WORKLOAD_IDENTITY = (
    TYPE = AWS,
    ARN = 'arn:aws:iam::123456789012:role/airflow-prod-task'
  )
  DEFAULT_ROLE = ETL_PROD
  DEFAULT_WAREHOUSE = ETL_WH;

CREATE USER svc_gha_deploy
  TYPE = SERVICE
  WORKLOAD_IDENTITY = (
    TYPE = OIDC,
    ISSUER = 'https://token.actions.githubusercontent.com',
    SUBJECT = 'repo:acme/data-platform:environment:prod'
  )
  DEFAULT_ROLE = DEPLOYER;

The connector then authenticates with authenticator=WORKLOAD_IDENTITY and picks up the ambient token from the instance metadata service or the CI runner. Nothing to rotate, nothing to leak, and the GitHub subject pins the credential to a specific repository and environment — a stolen workflow file in a fork cannot use it. Migrate your highest-privilege automation here first: CI deployers and production orchestrators.

Step 6: pair identity with least-privilege roles

Hardened credentials on an over-privileged user is a lateral move waiting to happen. Two rules cover most of it:

-- Automation gets a purpose-built access role, never a functional admin role
CREATE ROLE etl_prod;
GRANT USAGE ON DATABASE prd TO ROLE etl_prod;
GRANT USAGE ON SCHEMA prd.staging TO ROLE etl_prod;
GRANT INSERT, UPDATE, DELETE, SELECT ON ALL TABLES IN SCHEMA prd.staging TO ROLE etl_prod;
GRANT INSERT, UPDATE, DELETE, SELECT ON FUTURE TABLES IN SCHEMA prd.staging TO ROLE etl_prod;
GRANT ROLE etl_prod TO USER svc_airflow_prod;

ALTER USER svc_airflow_prod SET DEFAULT_ROLE = etl_prod;
ALTER USER svc_airflow_prod SET DEFAULT_SECONDARY_ROLES = ();   -- no implicit privilege union

Leaving DEFAULT_SECONDARY_ROLES = ('ALL') on a service user means every role it has ever been granted is active at once, quietly defeating your separation of duties. Humans can keep ALL; robots should not.

Also lock automation to a network policy and its own warehouse so a runaway job is both contained and attributable:

CREATE NETWORK POLICY etl_np ALLOWED_IP_LIST = ('203.0.113.0/24');
ALTER USER svc_airflow_prod SET NETWORK_POLICY = etl_np;

Step 7: verify, then watch

Audit what actually happened, not what you intended:

-- Who is still authenticating with a bare password?
SELECT user_name, first_authentication_factor, second_authentication_factor,
       client_ip, reported_client_type, COUNT(*) AS attempts
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE event_timestamp > DATEADD('day', -30, CURRENT_TIMESTAMP())
  AND is_success = 'YES'
  AND first_authentication_factor = 'PASSWORD'
  AND second_authentication_factor IS NULL
GROUP BY ALL
ORDER BY attempts DESC;

-- Service users that are classified but still hold a password
SELECT name, type, has_password, has_rsa_public_key
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
WHERE deleted_on IS NULL AND type IN ('SERVICE','LEGACY_SERVICE') AND has_password;

-- Dormant credentials: no successful login in 90 days
SELECT name, type, last_success_login
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
WHERE deleted_on IS NULL
  AND (last_success_login IS NULL OR last_success_login < DATEADD('day', -90, CURRENT_TIMESTAMP()));

Turn the first query into a scheduled alert that posts to your security channel, and the third into a quarterly disable-and-drop review. SESSIONS and QUERY_HISTORY will tell you which client and driver version each service user is on, which is exactly what you need before flipping a CLIENT_TYPES restriction on.

A migration order that does not cause an outage

Inventory users, credentials, and 90 days of login history. Nothing changes yet.
Set TYPE on every human. Enroll MFA or finish the SSO rollout.
For each service account, add a key pair alongside the password, cut the caller over, verify logins, then drop the password and set TYPE = SERVICE.
Replace stored secrets with workload identity federation wherever the caller runs in a cloud or CI environment you control.
Attach authentication policies per role/user, then the account baseline last, with a break-glass user documented.
Tighten roles: purpose-built access roles, DEFAULT_SECONDARY_ROLES = (), network policies on automation.
Schedule the audit queries and the key/PAT rotation runbook.

Steps 3 and 4 are where most of the effort lives, because they touch every pipeline. Do them one caller at a time and keep the old credential live until the new one has run a full production cycle.

PowderInsights handles Snowflake security and platform hardening as part of our consulting work — user and credential inventories, SSO and MFA rollouts, service-account migration to key pairs and workload identity, RBAC redesign, and the monitoring to keep it all from drifting back. Get in touch with a note about your current account and we will scope the cleanup.