Azure Active Directory (AD) is a popular identity and access management service provided by Microsoft which works well as a Single Sign On (SSO) for the Snowflake Data Cloud. For those companies that are already using Azure AD for SSO, it is recommended to also utilize System for Cross-domain Identity Management (SCIM) protocol integration for security, to provide an easy process for onboarding users into Snowflake, and for managing functional role memberships in Snowflake.
In this blog post, we will guide you through the steps of connecting Azure AD SCIM to Snowflake and provide some tips and tricks for ease of implementation.
Why Connect Azure AD SCIM to Snowflake?
Using Azure AD SCIM greatly eases managing functional roles within Snowflake. Once SCIM is deployed between AD and Snowflake, Azure AD Groups can be synced to functional roles within Snowflake. This means that once a user is added to an AD Group within Azure, the user will automatically be created within Snowflake (if it doesn’t already exist) and be assigned a functional role associated with the Azure AD Group.
This process can also be tied to a ticketing system, thereby automating the entire user provisioning process.
How to Connect Azure AD SCIM to Snowflake
Prerequisites:
- A Snowflake account with administrative privileges.
- An Azure AD account with administrative privileges.
- A custom Snowflake role with the necessary permissions to manage users and roles.
- A custom Snowflake virtual warehouse to manage user provisioning and deprovisioning.
The following steps will ensure a successful connection:
Step 1: Snowflake custom role set up
- Log in as an admin and use the accountadmin role by running the following command:
- use role accountadmin;
- Create the custom role AAD_PROVISIONER; this will be the role that all users and roles created in Azure AD will be owned by:
- 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;
- Create a security integration called aad_provisioning.
- grant role aad_provisioner to role accountadmin;
- create or replace security integration aad_provisioning
- type=scim
- scim_client=‘azure’
- run_as_role=‘AAD_PROVISIONER’;
- Create and copy the auth token to the clipboard and store it somewhere secure for later steps. This token will be used in each SCIM REST API request in the request header. NOTE: This token expires after six months and must be regenerated.
- select system$generate_scim_access_token(‘AAD_PROVISIONING’);
- Along with this token, you’ll also need your “SCIM Endpoint,” which is your Snowflake account URL along with /scim/v2/ added at the end.
- Example: https://myorg-myaccount.snowflakecomputing.com/scim/v2/
Step 2: Configure user provisioning settings in Snowflake
- In the Snowflake web interface, go to the “Security” tab and click on the “Users” tab.
- Click on the “Provisioning” button to configure user provisioning settings.
- In the “General” tab, select the custom virtual warehouse you created in the prerequisites as the warehouse to use for provisioning and deprovisioning users.
- In the “SCIM Provisioning” tab, select the custom Snowflake role you created in the prerequisites as the role to assign to new users.
- Click on the “Save” button to save the configuration.
Step 3: Create an enterprise application for Snowflake in Azure AD
- Log in to the Azure portal using your Azure AD administrator account.
- Click on “Enterprise applications” in the left-hand menu, then click the “New application” button.
- Search for “Snowflake” in the search box, then select the “Snowflake” application from the list.
- Click on the “Create” button to create the enterprise application.
- 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.
Step 4: Configure SCIM provisioning in Azure AD
- In the Azure portal, go to the “Enterprise applications” section and select the Snowflake application you created in step 2.
- In the Snowflake application page, click on the “Provisioning” tab, then click on the “Get Started” button.
- In the “Configure” tab, select “Automatic” as the provisioning mode and paste the “SCIM Endpoint” and “Bearer Token” values you copied from Snowflake in the first step.
- Click on the “Test Connection” button to verify that Azure AD can connect to Snowflake.
- Once the test connection is successful, click on the “Save” button to save the configuration.
Step 5: Synchronize Azure AD Users and groups to Snowflake
- In the “Mappings” section of the Snowflake enterprise app, select “Synchronize Azure Active Directory Users to Snowflake”
- This will display the user attributes in the Attribute Mapping section that will be used to join users between Azure AD and Snowflake.
- Make any changes required and hit “Save”
- At this point, custom user attributes can be created for default role, default warehouse, default secondary roles, and (if applicable) Snowflake name and login name fields will be different.
- Snowflake has a good article here that walks you through this process.
- Next, select “Synchronize Azure Active Directory Groups to Snowflake”
- This will display the group attributes used to join groups between Azure AD and Snowflake.
- Make any changes required and hit “Save”
Step 6: Turn on the provisioning service
- In the “Settings” section, change “Provisioning Status” to “On”
- Define the Scope by choosing either “Sync all users and groups” or “Sync only assigned users and groups”
- This setting may require admin privileges.
- When ready to provision, select “Save”
- This will start the initial synchronization of users and groups defined in the “Scope”
- This will take longer than subsequent syncs, which occur every 40 minutes.
How phData’s Elastic Platform Operations Play a Vital Role
Building and configuring enterprise applications like Azure AD and Snowflake can be complex. The experienced Snowflake experts at phData’s Elastic Platform Operations (EPO) can elevate and accelerate the success of your entire organization.
EPO provides:
- Continuous monitoring and support to deliver a reliable platform for developers, analysts, and data scientists.
- Expert implementation and configuration support on new, complex data and machine learning technologies used with Snowflake.
- Cost savings on your platform administration costs (on average, around 45%).
Learn more about how you can take advantage of EPO!
Tips and Tricks for connecting Azure AD SCIM to Snowflake
- Snowflake accounts using AWS PrivateLink or Google Cloud Private Service Connect are not supported. 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.
- Set up an alert/reminder to renew the SCIM access token; it will expire every six months.
- Regex can be used in the role-group mappings between AD and Snowflake. Microsoft provides documentation on how to accomplish this here.
Closing
Integrating Azure AD SCIM with Snowflake will drastically reduce the amount of time spent by Snowflake admins in dealing with user provisioning. Effectively taking humans and coding out of the equation will also reduce errors in who has access to your data.
Looking for help with your next data engineering project?
From data engineering projects to cloud migrations, phData helps organizations make more data-driven decisions.
Frequently Asked Questions
With the configuration detailed in this blog, when adding pre-existing users to an AD group, you’ll receive an error message: “Failed to create User ‘<username>’ in SnowFlake.” This is because the custom role we created is not the owner of pre-existing users. To alleviate this issue, simply grant ownership of this user to the aad_provisioner role you created in step 1.
Azure AD for SSO can be configured in much the same way as the Azure AD SCIM configuration. It does require some work in Snowflake and the Snowflake Enterprise Application in Azure. For a tutorial on how to accomplish this, visit this documentation by Microsoft.
