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.

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.

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 the Azure AD IP addresses for the Public Cloud or the US Government Cloud. For more information, see Managing SCIM Network Policies.

Configuration

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 are 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 a user and role in Snowflake and assign that user and role to the AAD_PROVISIONER role. 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. Grant the AAD_PROVISIONER role to the ACCOUNTADMIN role in Snowflake. The ACCOUNTADMIN role is necessary to create an integration.

    grant role aad_provisioner to role accountadmin;
    
  5. Let the ACCOUNTADMIN role create the security integration using the AAD_PROVISIONER role.

    create or replace security integration aad_provisioning
        type=scim
        scim_client='azure'
        run_as_role='AAD_PROVISIONER';
    
  6. Create and copy the authorization token to the clipboard. Use this token for each SCIM REST API request and place 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');
    

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;