Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
Alteryx 2022.3 release introduced Azure Active Directory (Azure AD) authentication support, Single Sign-On (SSO), for Snowflake. This update allows users to authenticate to Snowflake with Azure AD accounts. Alteryx Designer 2022.3 release enables users leverage two types of Azure AD application configurations to access Snowflake data: single-tenant and multi-tenant application. This post covers single-tenant application configuration. If you want to use multi-tenant application, please refer to this post.
To access Snowflake data with Azure AD accounts, users are required to have the following configuration in place:
Have the latest version of Snowflake ODBC driver installed on users' machines. The latest version can be downloaded from Alteryx Data sources page: Data Sources | Alteryx Help
Security integration, also known as Trust, configured between organisation’s Azure AD tenant and Snowflake instance;
Obtain authentication details required to setup a new connection between Alteryx Designer and Snowflake.
Single-tenant application setup
In this post we will cover how to:
Register and configure single-tenant OAuth applications in Azure AD tenant,
Create a security integration between Azure AD and Snowflake,
Access Snowflake using Azure AD account from Alteryx Designer.
Please note, the following example is intended for demonstration purposes only. We recommend engaging your systems team to help you with configuration. This example covers single-tenant OAuth implementation that is only intended for authentication of users existing within same tenant.
To access Snowflake from Alteryx Designer with your Azure AD account, you first need to establish “trust” between Snowflake and your Azure Active Directory. This trust is also known as security integration. To create this integration, you will need to obtain the following details from your Azure AD account:
external_oauth_issuer,
external_oauth_jws_keys_url,
external_oauth_audience_list.
If you don’t know where to find these details, don’t worry as below we provide a step-by-step instructions.
Step 1: Register and Configure OAuth Applications in Azure AD Tenant
Now that we’re clear about what we need, let’s focus on what we need to do to access above details.
Register a new single-tenant Resource application in your Azure AD tenant
Go to your Azure AD portal, select Active Directory which you want to connect to Snowflake with, and click on App registrations tab;
Click on New registration - this will be known as resource application;
Provide a descriptive name e.g. Snowflake OAuth Resource, and select single-tenant as supported account types that can use this application;
- Register this application;
- Next, go to “Expose an API” tab;
- Add application id, it will be know as your <AZURE_AD_SCOPE_NAME> - save it, you will need it for next steps, and click on Add scope button;
- Provide session:role-any as scope name;
- Set “Who can consent” to Admins and users;
- Provide descriptive scope name and description in “Admin consent display name” and “Admin consent description” fields;
- Set state to “enabled” and save the scope;
Copy and save the scope, you will need it later.
Register a new single-tenant Client application in your Azure AD tenant
Go to your Azure AD portal, select Active Directory with which you want to connect to Snowflake, and click on App registrations tab;
Click on New registration - this will be known as client application;
Provide a descriptive name e.g. Snowflake OAuth Client and select single-tenant as supported account types that can use this application;
Set type to web, Redirect url to http://localhost and click register;
On the overview page, copy and save the id of the client application. This will be known as your client id;
8. Next, go to Certificates and Secrets and generate a new secret for this application. Save the value of the secret, you will need it later.
Link applications
Now that you created your resource and client applications, you need to link them. To do this, assign your client application with the scope you created for the resource. Here is what you need to do:
Go to Snowflake OAuth Client application and select API permissions;
Next, click on Add permission, select “My APIs” and add the Resource API you created earlier. At this stage, you should see “session-role:any” on the list of available permissions. If you don’t see the permission, check if you followed the steps above;
3. Set “What type of permissions does your application require?” value to Delegated permissions and click save;
4. Now, you need to grant an admin consent for this application to be used by your Active Directory → click on “Grant admin consent”.
Add the Client App as and authorised client application for Resource:
In this step, you’re adding the client application to the list of applications that are authorised to use the resource.
First, copy the id of Snowflake OAuth Client application - can be found on the overview page of the application;
Next, select Snowflake OAuth Resource application and go to Expose API;
Select Authorized client applications → Add client application → paste the id of the client created;
Save changes.
After creating and configuring applications in Azure AD, you need to create a trust between Azure AD and Snowflake. Let’s take a look at how it can be done.
First, go to your Snowflake OAuth Client application and select overview;
Next, click on endpoints button and look for Federation metadata document and copy the url;
Paste url in a new browser tab and locate entityID - this will be known as <AZURE_AD_ISSUER>. The entityID value should be similar to https://sts.windows.net/90288a9b-97df-4c6d-b025-95713f21cef9/. Save the value and close the tab;
Now, copy the OpenID Connect metadata document from your Azure endpoints and paste it in a new tab;
Finally, locate and copy the value of jwks_uri - this will be known as your <AZURE_AD_JWS_KEY_ENDPOINT>.
Now, that you copied all values, you can finally create a new security integration by running the following query in Snowflake:
Important!
To execute below query you need to be signed in with ACCOUNTADMIN role.
create security integration <integration_name>
type = external_oauth
enabled = true
external_oauth_type = azure
external_oauth_issuer = <AZURE_AD_ISSUER>
external_oauth_jws_keys_url = <AZURE_AD_JWS_KEY_ENDPOINT>
external_oauth_audience_list = (<AZURE_AD_SCOPE_NAME>)
external_oauth_token_user_mapping_claim = 'upn'
external_oauth_any_role_mode = 'ENABLE'
external_oauth_snowflake_user_mapping_attribute = 'login_name';
Upon running this SQL query, you should see a message confirming new security integration has been successfully created.
Step 3: Connect to Snowflake from Alteryx Designer
Now that you established trust between your Snowflake instance and Azure AD, you access your Snowflake data from Alteryx Designer. Add input or output tool, tick off the “Use Data Connection Manager (DCM)” box and select Snowflake from the list of available data sources in Alteryx Designer. Next, select Quick Connect option, provide your Snowflake instance details, create new credential and select the Azure AD - private authentication method. You will then be prompted to provide the following inputs:
Tenant id - id of your Azure Active Directory. You can find it in your Azure portal;
Client id - id of your Snowflake Client application that you registered earlier;
Client secret - the client secret you generated for Client application;
Scope - <AZURE_AD_SCOPE_NAME> you assigned earlier to your resource application;
Upon filling out the above details and clicking connect, you will be redirected to the Azure AD login page and will be prompted to login with your account. Next, upon providing consent, you will be able read your Snowflake data from Alteryx Designer.
Now that you’ve integrated your Azure AD with Snowflake, you can share tenant id, client id, client secret, and scope values with other members of your team. They will need those parameters to use the integration you’ve just configured.
Related Documentation
Please refer to the following documentation to learn more about Azure Active Directory and Snowflake integration.
Configure Microsoft Azure AD for External OAuth | Snowflake Documentation
Common issues
Depending on your Azure AD configuration, individual users might not be able to grant required consent to Azure AD application. In this case you might be presented with one of the following cases:
Approval request submitted to account admin. You are presented with the following prompt asking you to submit approval request to your account admin who would need to approve and grant required access to this application.
Upon submitting request, your account admin receives a notification and needs to approve your request. Once approval is granted, you can reset the connection and be able to access your Snowflake data.
Need admin approval. This screen appears when your Azure AD settings don’t allow individual users grant consent to applications. In this specific case, accounts setting doesn’t allow users to submit approval requests to your directory admin. To resolve this problem, you should ask your account admin to use his/her credentials when first setting up connection from Alteryx Designer and provide consent to this app on behalf of the organisation. Upon completing these steps, all further Azure AD tenant users will be able to grant consent without further approvals. Alternatively, you might want to ask your Azure AD admin to update your tenant consent policy to allow users submit approval requests to account admin.
