Snowflake SSO, User Types, Passwords and MFA

1.Federated authentication: SP, IdP, SAML and OIDC

A federated environment has two roles. Snowflake is the service provider (SP). The identity provider (IdP) creates and stores user credentials and authenticates users for SSO into Snowflake. Snowflake supports two protocols for this, each configured as its own security integration: SAML 2.0 (TYPE = SAML2) and OpenID Connect (TYPE = OIDC).

Okta and Microsoft Entra ID have native Snowflake support. Most other SAML 2.0-compliant vendors also work, for example Google Workspace, OneLogin and Ping Identity PingOne, but with those you have to define a custom application for Snowflake in the IdP. On the OIDC side, Snowflake manages the client configuration for Google and Microsoft. With any other OIDC provider, you register an application in that IdP and give Snowflake its client credentials.

A login can start in either place. In a Snowflake-initiated login, the user picks the IdP on the Snowflake sign-in page. In an IdP-initiated login, the user opens the Snowflake application from the IdP's portal. Logging out behaves differently from logging in. Snowflake never performs a global logout, so when a user signs out of one Snowflake session, their other Snowflake sessions and their IdP session stay open. You can also give different users different IdPs. Among the drivers, only JDBC, ODBC and Python currently support multiple IdPs.

Sources1

2.Configuring the IdP and the SAML2 security integration

Setting up SAML SSO takes work on both sides. In the IdP (Okta, for example): create a user for each person who needs access, including an email address. Then create a Snowflake application whose SubDomain is your account identifier, with privatelink appended if you use private connectivity, and assign the users to it. Identity mapping is the part that most often breaks logins: the email address in the IdP has to match the Snowflake user's login_name, and it has to match the SAML NameID. Unless you set SAML2_REQUESTED_NAMEID_FORMAT, Snowflake requests the emailAddress NameID format.

In Snowflake: create a SAML2 security integration. This replaces the deprecated SAML_IDENTITY_PROVIDER account parameter. The recommended way to describe the IdP is METADATA_URL. Snowflake then reads the IdP's settings, certificate included, and ALTER SECURITY INTEGRATION ... REFRESH METADATA_URL picks up changes, which simplifies certificate rotation. The issuer URL and the ACS URL have to match the URL you gave the IdP, and the ACS URL ends in /fed/login.

CREATE SECURITY INTEGRATION my_idp
  TYPE = saml2
  ENABLED = true
  METADATA_URL = 'https://integrator-26580.okta.com/app/ex2kbcS30N697/sso/saml/metadata'
  SAML2_SNOWFLAKE_ISSUER_URL = 'https://<orgname>-<account_name>.privatelink.snowflakecomputing.com'
  SAML2_SNOWFLAKE_ACS_URL = 'https://<orgname>-<account_name>.privatelink.snowflakecomputing.com/fed/login';

IdP-initiated SSO works with no further Snowflake configuration. To allow Snowflake-initiated SSO, set SAML2_ENABLE_SP_INITIATED, and set SAML2_SP_INITIATED_LOGIN_PAGE_LABEL to control the button text on the login page. The integration also supports encrypted assertions and signed requests (SAML2_SIGN_REQUEST). Both rely on the certificate in SAML2_SNOWFLAKE_X509_CERT.

Once SSO is running, users can still have Snowflake passwords, but Snowflake recommends keeping passwords only in the IdP. Creating a user without a password, or removing one, turns off Snowflake authentication for that user. Do not use MUST_CHANGE_PASSWORD for federated users. Keep at least one account administrator with a Snowflake password so someone can still sign in if SSO has problems.

ALTER SECURITY INTEGRATION my_idp SET  ?  = true;

Sources23

3.User types: PERSON, NULL and SERVICE

Which authentication methods a user can have depends on its TYPE property. The reason for the split is that people should enroll in MFA, while services and applications should not, because no person is there to provide a second factor. If you don't set TYPE in CREATE USER, it defaults to PERSON.

SERVICE users also can't have FIRST_NAME, LAST_NAME, PASSWORD, MUST_CHANGE_PASSWORD or MINS_TO_BYPASS_MFA, and authentication-policy MFA enforcement doesn't apply to them. They authenticate with key pairs or tokens, which are covered in the programmatic-authentication lesson.

Sources45

4.Password policies and password resets

A password policy is an object that lives in a schema. Creating one requires CREATE PASSWORD POLICY on the schema, and setting it requires APPLY PASSWORD POLICY on the account or on the user. You can set it on the account with ALTER ACCOUNT or on individual users with ALTER USER. You can't swap one policy for another in place: to change the policy on an account or user, unset the current one first and then set the new one. To make users meet a new policy, set MUST_CHANGE_PASSWORD = TRUE and they will have to change their password at the next login.

An administrator can reset a password in two ways. They can set a new one directly with ALTER USER ... SET PASSWORD, or they can generate a single-use reset URL:

ALTER USER janesmith RESET PASSWORD;

The URL works once and expires after 12 hours. Generating it does not change anything about the current password. If an ACCOUNTADMIN is locked out, another ACCOUNTADMIN can reset that password. If no other administrator exists, Snowflake Support has to do it.

Sources67

5.MFA enrollment, recovery and reporting

In accounts created after the 2024_08 bundle, every human user who signs in with a password must enroll in MFA by default. Service users are not included. Authentication policies control MFA. MFA_ENROLLMENT makes enrollment required, MFA_POLICY = (ALLOWED_METHODS = ...) limits which methods users can choose (passkey, TOTP authenticator app, or Duo), and ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION='ALL' requires Snowflake MFA after SSO. Snowflake recommends passkeys.

CREATE AUTHENTICATION POLICY mfa_policy
  MFA_ENROLLMENT = REQUIRED
  MFA_POLICY = (ALLOWED_METHODS = ('PASSKEY', 'TOTP'));

Recovering a locked-out user can go one of two ways. ALTER USER joe ENROLL MFA; emails the user, or returns a URL, so they can register a new method. ALTER USER joe SET MINS_TO_BYPASS_MFA = 30; turns MFA off for that user for 30 minutes, so a single-factor password works during that window. To delete a method that has been lost, look up its name with SHOW MFA METHODS FOR USER joe and then run ALTER USER ... REMOVE MFA METHOD.

Reporting: SHOW USERS returns type and has_mfa columns, and DESC USER shows HAS_MFA. You can filter the SHOW USERS output with the pipe operator (->>) or RESULT_SCAN, for example to list PERSON users where has_mfa is false. Because SHOW output column names are lowercase, refer to them with double-quoted identifiers. Most columns are filtered by privilege. Without OWNERSHIP on the user or MANAGE GRANTS on the account, they come back NULL.

Sources8910

6.OAuth 2.0: Snowflake OAuth vs External OAuth, custom clients, network policies and private connectivity

Snowflake supports the OAuth 2.0 protocol for authentication and authorization, and you configure it with a security integration. There are two flavours. With Snowflake OAuth (TYPE = OAUTH), Snowflake itself is the authorization server: the client sends the user to a Snowflake authorization page, the user signs in and consents to a role, and Snowflake issues an access token and optionally a refresh token. The user can sign in to that authorization page through SSO, so federated authentication and OAuth combine: the IdP authenticates the person, and OAuth authorizes the application. With External OAuth (TYPE = EXTERNAL_OAUTH), your own authorization server (Okta, Microsoft Entra ID, PingFederate or a custom server) issues a JSON Web Token, Snowflake validates it and maps a token claim such as sub to a user attribute such as login_name. Because no browser is needed, External OAuth is the best fit for programmatic clients.

In both cases the client connects with authenticator = oauth and passes the access token in the token parameter. In login history, OAuth logins show FIRST_AUTHENTICATION_FACTOR = OAUTH_ACCESS_TOKEN.

Roles. Both flavours block the ACCOUNTADMIN, ORGADMIN, GLOBALORGADMIN and SECURITYADMIN roles from OAuth sessions by default. For Snowflake OAuth the account parameter OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST controls this, and for External OAuth it is EXTERNAL_OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST. External OAuth carries the role in the token scope, for example session:role:analyst, or session:role-any to use the user's default role. Snowflake OAuth sessions are single-role unless OAUTH_ANY_ROLE_MODE is enabled, and user-created Snowflake OAuth integrations don't support switching to secondary roles in the session. If you need that, use External OAuth.

Custom clients. Snowflake OAuth supports partner applications such as Tableau and Looker (OAUTH_CLIENT = TABLEAU_DESKTOP, LOOKER, and so on) and your own applications with OAUTH_CLIENT = CUSTOM. A custom client must declare OAUTH_CLIENT_TYPE, either 'CONFIDENTIAL' for a client that can keep a secret, such as a server-side service, or 'PUBLIC' for a desktop or app-store client, and must set OAUTH_REDIRECT_URI, which has to use TLS unless you allow otherwise. BLOCKED_ROLES_LIST and ALLOWED_ROLES_LIST bound which roles the user can consent to. The client calls <account_url>/oauth/authorize to get an authorization code and <account_url>/oauth/token-request to exchange it for tokens. Access tokens live 10 minutes by default; refresh-token validity for custom clients is 1 to 90 days. Rotate the client secret with ALTER SECURITY INTEGRATION ... REFRESH OAUTH_CLIENT_SECRET; Snowflake keeps two secrets so rotation is uninterrupted.

CREATE SECURITY INTEGRATION oauth_kp_int
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://localhost.com'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 86400
  BLOCKED_ROLES_LIST = ('SYSADMIN')
  OAUTH_CLIENT_RSA_PUBLIC_KEY ='
  MIIBI
  ...
  ';

Network policies. A Snowflake OAuth integration can carry its own NETWORK_POLICY. Which policy applies depends on who is talking to Snowflake. When the *user* authenticates in the browser, the network policy attached to the user governs, falling back to the account policy. When the *client* exchanges the authorization code, refreshes a token or queries with the access token, the policy attached to the integration governs, again falling back to the account policy. External OAuth integrations cannot have a network policy at all, so only account-wide policies apply; if you need an integration-specific policy, use Snowflake OAuth.

Private connectivity. External OAuth works with private connectivity. For Snowflake OAuth it depends on the client: Tableau has an embedded OAuth client that can use the private connectivity URL, while Looker with Snowflake OAuth needs the public internet. For a SaaS-hosted client, the browser can reach a PrivateLink URL for the authorization step but the vendor's server cannot, so the token endpoint must be a public Snowflake URL. Set USE_PRIVATELINK_FOR_AUTHORIZATION_ENDPOINT = TRUE on the integration if you want the user's browser redirected to the private authorization endpoint.

Sources11121314151617

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.With SAML SSO, Snowflake matches the asserted identity against the user's NAME, so the IdP email can be anything.Why is that wrong?

   The IdP email address has to match both the Snowflake user's login_name and the SAML NameID. A mismatch means the user cannot sign in.

   Covered in Configuring the IdP and the SAML2 security integration

2. 2.Turning on SAML SSO automatically requires Snowflake MFA for SSO users.Why is that wrong?

   By default Snowflake trusts the IdP to enforce MFA. Requiring Snowflake MFA after SSO takes an authentication policy with ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION.

   Covered in MFA enrollment, recovery and reporting

3. 3.A user with TYPE = SERVICE can be rescued with ALTER USER RESET PASSWORD.Why is that wrong?

   SERVICE users cannot sign in with a password. Both RESET PASSWORD and DISABLE_MFA are blocked for them.

   Covered in User types: PERSON, NULL and SERVICE

4. 4.You can restrict an External OAuth integration to specific IPs by setting NETWORK_POLICY on it, just like Snowflake OAuth.Why is that wrong?

   External OAuth integrations cannot carry a network policy. Only account-wide policies apply. An integration-specific policy requires Snowflake OAuth.

   Covered in OAuth 2.0: Snowflake OAuth vs External OAuth, custom clients, network policies and private connectivity