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 and Google Cloud Private Service Connect. 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.

  • Enabling or disabling password synchronization from Microsoft Azure AD to Snowflake.

    Setting the SYNC_PASSWORD property in the Snowflake security integration will not synchronize user passwords from Microsoft Azure AD to Snowflake. This is a Microsoft Azure AD limitation. To request support, please contact Microsoft Azure.

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 URL of the Snowflake SCIM endpoint (i.e. Tenant URL in the Microsoft Azure Active Directory SCIM configuration guide). The Snowflake SCIM endpoint consists of the Snowflake account URL appended with /scim/v2/. For example, if you use the account name URL format, the SCIM endpoint is https://myorg-myaccount.snowflakecomputing.com/scim/v2/. For a list of supported formats for the Snowflake account URL, see Connecting with a URL.

  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.

Azure Active Directory configuration

To use Microsoft Azure Active Directory as a SCIM identity provider, follow the instructions in the Microsoft documentation. While completing these steps, do not re-use an existing enterprise application in Azure AD. Failure to create a new enterprise application for provisioning can result in unexpected behavior.

Note

If you are creating custom attributes and would like the name and login_name fields for the Snowflake user to have different values, contact Snowflake Support to enable separate mappings for your account before creating the custom attributes.

Snowflake configuration

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');
Copy

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;
    
    Copy
  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;
    
    Copy
  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';
    
    Copy
  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');
    
    Copy

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

Applying a network policy to a SCIM security integration allows the SCIM network policy to be distinct from network policies that apply to the entire Snowflake account. It allows the SCIM provider to provision users and groups without adding IP addresses to a network policy that controls access for normal users.

A network policy applied to a SCIM integration overrides a network policy applied to the entire Snowflake account, but is overridden by a network policy assigned to a user.

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>;
Copy

To unset the SCIM network policy, use this command:

alter security integration aad_provisioning unset network_policy;
Copy

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 Controlling network traffic with network policies and ALTER SECURITY INTEGRATION.

Using secondary roles with SCIM

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

For a representative example, see Update a user.

Replicating the Azure SCIM security integration

Snowflake supports replication and failover/failback with the SCIM security integration from the source account to the target account.

For details, see Replication of security integrations & network policies across multiple accounts.

Troubleshooting

  • 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, where demo_db is the name of your database.

    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;
    
    Copy
  • If the user update fails, check the ownership of the user in Snowflake. If it is 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;
    
    Copy
  • 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: