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 concurrent requests per account per SCIM endpoint (e.g. the /Users endpoint, the /Groups endpoint). After your account exceeds this threshold, Snowflake returns a 429 HTTP status code (i.e. too many requests). Note that this request limit usually only occurs during the initial provisioning when relatively large numbers of requests (i.e. more than 10 thousand) occur to provision users or groups.

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.

  • If you are using Azure Private Link to access Snowflake, ensure that you are not using the Azure Private Link URL in the integration settings. Enter the public endpoint (i.e. without .privatelink), and ensure that the network policy allows access from the Azure IP addresses as shown in the Prerequisites section (in this topic), otherwise you cannot use this integration.

  • 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.

    • During the configuration process in Microsoft, you will need to input the Snowflake base URL (i.e. Tenant URL in the Microsoft Azure Active Directory SCIM configuration guide) for SCIM. The base URL for SCIM is the Snowflake account URL with /scim/v2/ appended to the end. For example, if your Snowflake account name is acme and your Azure region is east-us-2, the base URL for SCIM is https://acme.east-us-2.azure.snowflakecomputing.com/scim/v2/.

  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 (i.e. the Secret Token in the Microsoft Azure Active Directory SCIM configuration guide) is valid for six months. Upon expiration, create a new access token manually using SYSTEM$GENERATE_SCIM_ACCESS_TOKEN as shown below.

Note

To invalidate an existing access token for a SCIM integration, execute a DROP INTEGRATION statement.

To continue using SCIM with Snowflake, recreate the SCIM integration with a CREATE SECURITY INTEGRATION statement and generate a new access token using SYSTEM$GENERATE_SCIM_ACCESS_TOKEN.

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. Each of the following statements is explained below.

use role accountadmin;
create role if not exists 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');

Important

The example SQL statements use the ACCOUNTADMIN system role and the AAD_PROVISIONER custom role is granted to the ACCOUNTADMIN role.

It is possible not to use the ACCOUNTADMIN role in favor of a less-privileged role. Using a less-privileged role can help to address compliance concerns relating to least-privileged access, however, using a less-privileged role can result in unexpected errors during the SCIM configuration and management process.

These errors could be the result of the less-privileged role not having sufficient rights to manage all of the roles through SCIM due to how the roles are created and the resultant role hierarchy. Therefore, in an effort to avoid errors in the configuration and management processes, choose one of the following options:

  1. Use the ACCOUNTADMIN role as shown in the example SQL statements.

  2. Use a role with the global MANAGE GRANTS privilege.

  3. If neither of these first two options are desirable, use a custom role that has the OWNERSHIP privilege on all of the roles that will be managed using SCIM.

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

  2. Use 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 role if not exists 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.

    grant role aad_provisioner to role accountadmin;
    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 policies 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 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.

Using Secondary Roles with SCIM

Snowflake supports setting the user property DEFAULT_SECONDARY_ROLE to ALL with SCIM to allow users to use secondary roles in a Snowflake session.

For a representative example, see PUT scim/v2/Users/{id}.

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.

Next Topics: