2.4 Identity Federation, SSO, SCIM & Authentication Policies

2.4 Identity Federation, SSO, SCIM & Authentication Policies

Identity governance in modern cloud enterprises demands that data platforms never maintain isolated identity silos. To adhere to zero-trust standards, Snowflake provides comprehensive support for Federated Authentication (SAML 2.0 Single Sign-On), automated user and group lifecycle provisioning via SCIM, cryptographic RSA Key Pair authentication for headless service accounts, and native Authentication Policies. A SnowPro Advanced Architect must be adept at designing end-to-end identity architectures that eliminate manual user administration while closing authentication attack vectors.

Federated Authentication & Single Sign-On (SAML 2.0)

Single Sign-On (SSO) delegates the authentication of human users to an external corporate Identity Provider (IdP), such as Microsoft Entra ID (formerly Azure AD), Okta, PingFederate, or CyberArk. Snowflake acts as the Service Provider (SP).

+-----------------------------------------------------------------------------------------+
|                                Federated SSO Architecture                               |
|                                                                                         |
|  +------------------+         1. Login Request         +-----------------------------+  |
|  | User / Browser   | -------------------------------> | Snowflake (Service Provider)|  |
|  | (Client Session) |                                  +-----------------------------+  |
|  +------------------+                                                 │                 |
|           │                                                           │ 2. Redirect     |
|           │                    3. Authenticates + MFA                 ▼                 |
|           └──────────────────────────────────────────────> +-------------------------+  |
|                                                            | Identity Provider (IdP) |  |
|           ┌─────────────────────────────────────────────── | (Okta, Entra ID, Ping)  |  |
|           │                    4. Issues SAML Assertion    +-------------------------+  |
|           ▼                                                                             |
|  +------------------+         5. Posts Assertion       +-----------------------------+  |
|  | Browser Session  | -------------------------------> | Snowflake validates X.509   |  |
|  +------------------+                                  | & Establishes User Session  |  |
+-----------------------------------------------------------------------------------------+

SAML 2.0 Security Integration Configuration

Federated SSO is managed natively in Snowflake using a SAML2 Security Integration:

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE SECURITY INTEGRATION okta_sso_integration
  TYPE = SAML2
  ENABLED = TRUE
  SAML2_ISSUER = 'http://www.okta.com/exk12345abcdef'
  SAML2_SSO_URL = 'https://mycompany.okta.com/app/snowflake/exk12345abcdef/sso/saml'
  SAML2_PROVIDER = 'OKTA'
  SAML2_X509_CERT = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAz...' 
  SAML2_SP_INITIATED_LOGIN_PAGE_LABEL = 'Corporate Okta SSO'
  SAML2_ENABLE_SP_INITIATED = TRUE;

SP-Initiated vs IdP-Initiated SSO

• SP-Initiated SSO: A user navigates directly to the Snowflake account URL (e.g., https://myorg-myaccount.snowflakecomputing.com). When SAML2_ENABLE_SP_INITIATED = TRUE, the login page displays a dedicated SSO button with the custom label. Clicking it redirects the user to the IdP for authentication.
• IdP-Initiated SSO: A user logs into their corporate portal (e.g., Okta dashboard) and clicks the Snowflake application tile. The IdP generates an unsolicited SAML assertion and redirects the user into Snowflake.

User Matching & Zero-Downtime Certificate Rotation

• Identity Mapping: By default, Snowflake matches the SAML assertion's NameID subject to the Snowflake user's LOGIN_NAME. If the IdP sends email addresses as the NameID, the user's LOGIN_NAME in Snowflake must match their corporate email.
• X.509 Certificate Rotation: Enterprise IdP certificates expire periodically (e.g., every 1–2 years). When rotating certificates, an administrator updates the integration in place without service disruption:

ALTER SECURITY INTEGRATION okta_sso_integration SET 
  SAML2_X509_CERT = 'MIIE...NewBase64EncodedCert...';

System for Cross-domain Identity Management (SCIM)

While SAML 2.0 handles authentication, it does not manage the lifecycle of users. If an employee leaves the company, SAML prevents them from logging in via SSO, but their user object, default roles, and personal schema objects remain active in Snowflake. Furthermore, SAML does not automatically provision users before their first login or assign them to department roles.

SCIM (System for Cross-domain Identity Management) solves this by establishing an automated, push-based API synchronization mechanism between the IdP and Snowflake.

-- Step 1: Create the SCIM role (OKTA_PROVISIONER, AAD_PROVISIONER, GENERIC_SCIM_PROVISIONER, or a custom role)
USE ROLE USERADMIN;
CREATE ROLE OKTA_PROVISIONER;

USE ROLE SECURITYADMIN;
GRANT CREATE USER, CREATE ROLE ON ACCOUNT TO ROLE OKTA_PROVISIONER;
GRANT ROLE OKTA_PROVISIONER TO ROLE SECURITYADMIN;

-- Step 2: Create the SCIM Security Integration
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE SECURITY INTEGRATION okta_scim_int
  TYPE = SCIM
  SCIM_CLIENT = 'OKTA' -- Supported: 'OKTA', 'AZURE', 'GENERIC'
  RUN_AS_ROLE = 'OKTA_PROVISIONER';

-- Step 3: Generate the OAuth Bearer Access Token for the IdP
SELECT SYSTEM$GENERATE_SCIM_ACCESS_TOKEN('okta_scim_int');

SCIM Synchronization Mechanics

1. User Provisioning & Deprovisioning: When an employee is assigned the Snowflake app in Okta/Entra ID, SCIM calls Snowflake REST endpoints to execute CREATE USER. When terminated, the IdP calls the SCIM endpoint to deactivate the user (ALTER USER ... SET DISABLED = TRUE), immediately terminating active sessions.
2. Group-to-Role Mapping: IdP security groups (e.g., Okta-Sales-Analysts) are synchronized directly to Snowflake roles (SALES_ANALYSTS). Adding a user to the IdP group automatically issues GRANT ROLE SALES_ANALYSTS TO USER jsmith;.
3. Token Lifecycle: SCIM bearer tokens generated via SYSTEM$GENERATE_SCIM_ACCESS_TOKEN are valid for six months. Architects must document operational procedures to regenerate and update the token in the IdP prior to expiration to prevent sync outages.
4. Ownership Boundary: Users and roles created through SCIM are owned by the SCIM role (RUN_AS_ROLE). Keep that role narrowly privileged and grant it into the hierarchy deliberately; privileged system roles such as ACCOUNTADMIN should be assigned by administrators, not driven by IdP group membership.

Service Account Authentication: Key Pair Rotation Architecture

Automated pipelines, ETL/ELT orchestrators (e.g., Apache Airflow, dbt Cloud), microservices, and CI/CD runners should be created as service users (TYPE = SERVICE). Service users cannot sign in with a password, and interactive MFA does not fit headless automation. Supported options include key-pair authentication (the classic choice for drivers and the Snowpipe REST API), OAuth, workload identity federation, and programmatic access tokens.

Cryptographic Standards

• Algorithm: RSA key pairs with a minimum key length of 2048-bit (4096-bit recommended for enhanced security posture).
• Format: PKCS#8 encrypted private key using AES-256-CBC.
• Client Execution: Client applications generate a JSON Web Token (JWT) signed with the private key and present it to Snowflake Cloud Services upon connection.

Zero-Downtime Key Pair Rotation Pattern

A classic enterprise dilemma is rotating service account public keys without causing pipeline downtime. Snowflake solves this by providing two public key slots on every user: RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2.

-- Step 1: Assign initial public key (Key A) to the service account
USE ROLE SECURITYADMIN;

ALTER USER svc_dbt_orchestrator SET 
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...KeyA...';

-- Step 2: When rotating keys, assign the new public key (Key B) to the secondary slot
ALTER USER svc_dbt_orchestrator SET 
  RSA_PUBLIC_KEY_2 = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...KeyB...';

-- At this point, Snowflake validates connections signed with EITHER Key A OR Key B!

-- Step 3: Update client orchestration pipelines (Airflow, dbt) to use Private Key B
-- (Verify connections are succeeding with Key B in ACCOUNT_USAGE.LOGIN_HISTORY)

-- Step 4: Complete rotation by promoting Key B to the primary slot and unsetting slot 2
ALTER USER svc_dbt_orchestrator SET 
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...KeyB...';
ALTER USER svc_dbt_orchestrator UNSET RSA_PUBLIC_KEY_2;

Authentication Policies & Multi-Factor Authentication (MFA)

To establish perimeter defense around identity, Snowflake provides Authentication Policies (CREATE AUTHENTICATION POLICY). Authentication policies govern how clients are allowed to authenticate, which client drivers can connect, and whether multi-factor authentication is mandatory.

USE ROLE SECURITYADMIN;

-- Creating a zero-trust Authentication Policy for Service Accounts
CREATE OR REPLACE AUTHENTICATION POLICY service_account_auth_policy
  AUTHENTICATION_METHODS = ('KEYPAIR')
  CLIENT_TYPES = ('DRIVERS')
  COMMENT = 'Disables password login and restricts connection strictly to client drivers using RSA Key Pairs';

-- Creating a strict Authentication Policy for Human Users
CREATE OR REPLACE AUTHENTICATION POLICY human_user_auth_policy
  AUTHENTICATION_METHODS = ('SAML', 'PASSWORD')
  MFA_ENROLLMENT = 'REQUIRED'                       -- SNOWFLAKE_UI must be allowed for enrollment
  MFA_POLICY = (ALLOWED_METHODS = ('PASSKEY', 'TOTP'))
  CLIENT_TYPES = ('SNOWFLAKE_UI', 'DRIVERS');

-- Requiring Snowflake MFA even after an SSO (SAML/OIDC) sign-in
CREATE OR REPLACE AUTHENTICATION POLICY admin_double_mfa
  AUTHENTICATION_METHODS = ('PASSWORD', 'SAML')
  MFA_ENROLLMENT = 'REQUIRED'
  MFA_POLICY = (ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION = 'ALL');

-- Applying policies at the user or account level
ALTER USER svc_dbt_orchestrator SET AUTHENTICATION POLICY service_account_auth_policy;
ALTER ACCOUNT SET AUTHENTICATION POLICY human_user_auth_policy;

Snowflake Multi-Factor Authentication

• Methods: Passkeys (recommended), authenticator apps that generate time-based one-time passcodes (TOTP), and Duo. MFA_POLICY = (ALLOWED_METHODS = (...)) restricts which methods users may register; Duo is not replicated like the other methods.
• Enrollment: Users enroll in Snowsight, so CLIENT_TYPES must include SNOWFLAKE_UI when MFA_ENROLLMENT = 'REQUIRED'.
• Password sign-ins: Snowflake is deprecating single-factor password sign-ins; accounts created after the 2024_08 behavior change bundle already require human password users to enroll in MFA.
• SSO users: By default Snowflake relies on the IdP to enforce MFA for SAML/OIDC sign-ins. To harden privileged SSO users, add MFA_POLICY = (ENFORCE_MFA_ON_EXTERNAL_AUTHENTICATION = 'ALL') so they must also complete Snowflake MFA.