Overview:
In a federated environment, user authentication is managed separately from access control by using external entities to verify user credentials.
This setup enables Single Sign-On (SSO), allowing users to log in once and access multiple services seamlessly. Here’s how it works:
-
- Service Provider (SP): In a Snowflake federated environment, Snowflake acts as the Service Provider, relying on an external Identity Provider for user authentication.
- Identity Provider (IdP): The IdP is an independent, trusted entity (Microsoft Entra ID, in this case) that manages user credentials and profile information. It verifies the user’s identity and authenticates them for SSO access to the Service Provider (Snowflake, in this case).
With this setup, the Identity Provider authenticates the user and passes the credentials to Snowflake, allowing the user to access Snowflake services without needing to log in separately.
As data and AI strategies continue to expand, it is essential for organizations to develop and maintain a security strategy that ensures the delivery of value in a secure manner to their end users.
Snowflake, with its robust security features and policy enforcement capabilities, remains a leader in providing end-to-end secured solutions to customers.
In this blog, we will primarily discuss how to leverage Microsoft Entra’s user repository to automatically provision users in Snowflake and implement SSO (Single Sign-On), using password less authentication to applications.
Configuration Steps
The Setup is Configured in Two Portions,
-
- Microsoft Entra to Snowflake (User Provisioning).
- Connecting to Snowflake using Power BI via OAuth.
User Provisioning with Microsoft Entra ID :
Step 1: Register the Snowflake SSO application as an enterprise application in your organization’s Entra ID.
Step 2: After Registering the application you can provision the users , add the required users which you want to migrate to snowflake.
Add the users/user group which you want to migrate to your snowflake account to the snowflake SSO application.
Step 3: In your snowflake account you must create “AAD provisioning” role and “AAD provisioning” security integration.
Use the below commands to create the security integration, once the security integration is created you must take note of the SCIM token which is being generated by the security integration.
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';
#Generate the SCIM-token
select system$generate_scim_access_token('AAD_PROVISIONING');
The generated token must be saved and it will be used in the further steps.
Step 4: In the SSO application created in Microsoft Admin Center use the token and the tenant URL to create a provisioning .
The tenant URL is nothing but your snowflake account URL, and token is the same which we got after creating the security integration.
Just modify the existing URL so that it ends with “scim/v2/”,
Tenant URL- https://<org_Account>. snowflakecomputing.com/scim/v2/
Secret Token The SCIM Token which is saved while creating security integration in step-3.
Step 5: Map the existing Azure Entra ID attributes to the snowflake user attributes.
After URL and token validation we can add users to the provision and map the existing user’s attributes to snowflake’s user attributes.
After adding the required attributes and mapping we can start the provisioning for the existing users.
There are two ways to provision the users,
1.We can automate the provisioning for every 40 minutes which will add and update all the users in our snowflake application.
2.Provision on Demand in which we can select the specific users/groups and migrate them to snowflake.
Once after the provisioning is complete for the selected user, you can clearly check the users has been created in the snowflake account.
Step 6: Now as the Provisioning part is done, we can enable SSO for the account and check the user credentials.
Add these URLs, as formatted below
Identifier (Entity ID)
<a href=”https://https://<org_Account>.snowflakecomputing.com
Reply URL (Assertion Consumer Service URL)
<a href=”https://https://<org_Account>.snowflakecomputing.com/fed/login
Sign on URL
<a href=”https://https://<org_Account>.snowflakecomputing.com
Logout Url (Optional)
<a href=”https://https://<org_Account>.snowflakecomputing.com/fed/logout
Once the configuration is done, the SAML Certificate must be downloaded . The downloaded SAML Certificate must be stored in a secure location, as this certificate content is used in the upcoming steps after we create a security integration in snowflake.
Step 8: Create and configure a security integration for SSO in snowflake.
Using the below command, we can create the snowflake integration to enable SSO for the user.
create or replace SECURITY INTEGRATION AADINTEGRATION
TYPE = SAML2
ENABLED = TRUE
SAML2_ISSUER = 'https://sts.windows.net/<Microsoft-Tenant-ID>/'
SAML2_SSO_URL = 'https://login.microsoftonline.com/<Microsoft-Tenant-ID>/saml2'
SAML2_PROVIDER = 'CUSTOM'
SAML2_X509_CERT = '<CERTIFICATE CONTENT>'
SAML2_SP_INITIATED_LOGIN_PAGE_LABEL = 'AzureADSSO'
SAML2_ENABLE_SP_INITIATED = TRUE;
After creating the security integration, we must update certain properties of the integration so that the handshake between snowflake and Microsoft Entra ID is completed and setup properly.
To update the properties, you can run the below commands by changing the account identifier.
ALTER SECURITY INTEGRATION <Security Integration Name> SET SAML2_SNOWFLAKE_ACS_URL='https://<org_Account>.snowflakecomputing.com/fed/login';
ALTER SECURITY INTEGRATION <Security Integration Name> SET SAML2_SNOWFLAKE_ISSUER_URL = 'https://<org_Account>.snowflakecomputing.com';
Step 9: Testing SSO in snowflake UI
Clicking the icon will redirect the page to Microsoft login screen where you can login to your specific account.
Once we login, we can check the current user by running the below command and confirm that the SSO provisioning is confirmed.
Connecting to Snowflake using Power BI via OAuth.
The only step which is required to allow Power BI to handshake with snowflake, is by creating a security integration for power bi.
create security integration powerbi
type = external_oauth
enabled = true
external_oauth_type = azure
external_oauth_issuer = 'https://sts.windows.net/<Microsoft-Tenant-ID>/'
external_oauth_jws_keys_url = 'https://login.windows.net/common/discovery/keys'
external_oauth_audience_list = ('https://analysis.windows.net/powerbi/connector/Snowflake', 'https://analysis.windows.net/powerbi/connector/snowflake')
external_oauth_token_user_mapping_claim = 'upn'
external_oauth_snowflake_user_mapping_attribute = 'login_name';
Now we can seamlessly access snowflake via Power BI by just logging in with your Microsoft account (Power BI Desktop / Power BI Service).
Conclusion:
In conclusion, this integration provides secure access to Snowflake data in both the Snowflake UI and Power BI. For enhanced security, an additional layer can be added by enabling Multi-Factor Authentication (MFA) for human users, both through the identity provider (Microsoft Entra ID) and Snowflake’s built-in MFA. This dual MFA setup strengthens access control, ensuring a more robust protection for sensitive data. We specialize in modern data platform implementations and are a Snowflake Select Partner. Please feel free to reach out to us for your Snowflake needs.
Referral Links:
Power BI SSO to Snowflake | Snowflake Documentation
Using multiple identity providers for federated authentication | Snowflake Documentation