Azure SCIM Integration with Snowflake

Snowflake supports Microsoft Azure Active Directory as a SCIM identity provider.

This guide provides the steps required to configure Provisioning (in Azure AD) for Azure AD users and groups in Snowflake, and includes the following sections:

Features

  • Automatic Azure AD User Provisioning to Snowflake.

  • Automatic Azure AD Group Provisioning to Snowflake.

  • Synchronizing Azure AD users and groups to Snowflake.

  • If Azure AD is configured for SAML SSO to Snowflake, Azure AD users provisioned to Snowflake can access Snowflake using SAML SSO.

    Note

    By default, Azure AD users provisioned to Snowflake using SCIM are not assigned a password in Snowflake. This means that if SAML SSO is configured in Azure AD, users will authenticate to Snowflake using SSO.

    SAML SSO is not a requirement if using SCIM to provision users and groups from Azure AD to Snowflake. For additional options, see Configure Azure AD single sign-on.

Limitations

  • Snowflake supports a maximum of 500 requests per account per SCIM endpoint. After your account exceeds this threshold, Snowflake returns a 429 HTTP status code (i.e. too many requests).

Not Supported

  • AWS PrivateLink. Customers wanting to provision users and groups to Snowflake from Microsoft Azure AD without traversing the public Internet need to have their Snowflake account in Microsoft Azure.

  • Transferring ownership of existing users and roles. Azure AD is the authoritative source for its users and groups. Group membership can be updated in Azure AD. However, existing users and groups in Snowflake cannot be transferred to Microsoft Azure AD.

  • Microsoft Azure AD does not currently support reading or provisioning nested groups. Therefore, you cannot use the Snowflake Azure SCIM integration to provision or manage nested groups in Snowflake. Please contact Microsoft to request the support of nested groups.

Prerequisites

Before using SCIM to provision Azure AD users and groups to Snowflake, verify the following:

  1. An existing Azure AD tenant

  2. An existing Snowflake tenant

  3. At least one user in Snowflake with the ACCOUNTADMIN role

  4. Before provisioning users or groups, as it pertains to your account, ensure that the network policy in Snowflake allows access from all of the Azure AD IP addresses for the Public Cloud or the US Government Cloud. Currently, all Azure AD IP addresses are required to create an Azure SCIM network policy. For more information, see Managing SCIM Network Policies.

Configuration

The Snowflake configuration process creates a SCIM security integration to allow users and roles created in Azure AD to be owned by the AAD_PROVISIONER SCIM role in Snowflake and creates an access token to use in SCIM API requests. The access token is valid for six months. Upon expiration, create a new access token manually using SYSTEM$GENERATE_SCIM_ACCESS_TOKEN as shown below.

To use Microsoft Azure Active Directory as a SCIM identity provider, follow the instructions in the Microsoft documentation.

To facilitate the Snowflake configuration, you can copy the SQL below for use in this first step.

use role accountadmin;
create or replace role aad_provisioner;
grant create user on account to role aad_provisioner;
grant create role on account to role aad_provisioner;
grant role aad_provisioner to role accountadmin;
create or replace security integration aad_provisioning
    type=scim
    scim_client='azure'
    run_as_role='AAD_PROVISIONER';
select system$generate_scim_access_token('AAD_PROVISIONING');

Each of the following statements is explained below.

  1. Login to Snowflake as an administrator and execute the following from either the Snowflake worksheet interface or SnowSQL.

  2. Verify the ACCOUNTADMIN role.

    use role accountadmin;
    
  3. Create the custom role AAD_PROVISIONER. All users and roles in Snowflake created by Azure AD will be owned by the scoped down AAD_PROVISIONER role.

    create or replace role aad_provisioner;
    grant create user on account to role aad_provisioner;
    grant create role on account to role aad_provisioner;
    
  4. Let the ACCOUNTADMIN role create the security integration using the AAD_PROVISIONER custom role. For more information, see CREATE SECURITY INTEGRATION.

    create or replace security integration aad_provisioning
        type=scim
        scim_client='azure'
        run_as_role='AAD_PROVISIONER';
    
  5. Create and copy the authorization token to the clipboard and store securely for later use. Use this token for each SCIM REST API request and place it in the request header. The access token expires after six months and a new access token can be generated with this statement.

    select system$generate_scim_access_token('AAD_PROVISIONING');
    

Enabling Snowflake-initiated SSO

The SCIM provisioning process does not automatically enable single sign-on (SSO).

To use SSO after the SCIM provisioning process is complete, enable Snowflake-initiated SSO.

Managing SCIM Network Policies

The SCIM network policy has its own setting so that the SCIM provider can be specifically allowed to provision users and groups without adding these IP addresses for normal user access.

Setting up a network policy specific to the SCIM integration allows SCIM to be distinct from other network policies that may apply to the Snowflake account. The SCIM network policy does not affect other network policies on the account nor do other account network polices affect the SCIM network policy. Therefore, the SCIM network policy allows the Snowflake SCIM integration to provision users and groups as intended.

After creating the SCIM security integration, create the SCIM network policy using this this command:

alter security integration aad_provisioning set network_policy = <scim_network_policy>;

To unset the SCIM network policy, use this command:

alter security integration aad_provisioning unset <scim_network_policy>;

Where:

aad_provisioning

Specifies the name of the Azure AD SCIM security integration.

<scim_network_policy>

Specifies the Azure AD SCIM network policy in Snowflake.

For more information, see Network Policies and ALTER SECURITY INTEGRATION.

Troubleshooting Tips

  • To verify that Azure AD is sending updates to Snowflake, check the log events in Azure AD for the Snowflake application and the SCIM audit logs in Snowflake to ensure Snowflake is receiving updates from Azure AD. Use the following SQL to query the Snowflake SCIM audit logs.

    use role accountadmin;
    use database demo_db;
    use schema information_schema;
    select * from table(rest_event_history('scim'));
    select *
        from table(rest_event_history(
            'scim',
            dateadd('minutes',-5,current_timestamp()),
            current_timestamp(),
            200))
        order by event_timestamp;
    
  • If the user update fails, check the ownership of the user in Snowflake. If it’s not owned by the aad_provisioner role (or the role set in the run_as_role parameter when creating the security integration in Snowflake), then the update will fail. Transfer the ownership by running the following SQL statement in Snowflake, and try again.

    grant ownership on user <username> to role AAD_PROVISIONER;
    
  • If there are changes to the UPN attribute value in Azure AD after the initial SCIM provisioning, subsequent updates to the user will not work. A change to the UPN attribute value breaks the link between the Azure AD user object and the Snowflake User Object. If a change to the UPN attribute value occurs, reprovision the user with the correct UPN attribute value.