Leverage Snowflake OAuth with Alteryx Designer

[renat_isch]

Alteryx recently introduced native OAuth support for Snowflake. This update allows users to securely authenticate users to Snowflake using OAuth. In this post, we’re providing step-by-step instructions on required configuration that shall allow Alteryx Designer users establish connection to Snowflake using this authentication mechanism.

To access Snowflake data, users are required to have the following configuration in place:

• Create security integration in Snowflake to enable OAuth-based authentication;

• Collect Snowflake details.

• Access your Snowflake data 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.

Step 1: Create Security Integration to enable OAuth-based authentication

To allow users access Snowflake using OAuth, we first need to register a new security integration in Snowflake setting authentication type to OAuth. To do this, you need to run the following SQL command in your Snowflake instance:

create security integration <integration name>
  type = oauth
  enabled = true
  oauth_client = custom
  oauth_client_type = 'confidential'
  oauth_redirect_uri = <redirect uri>
  oauth_issue_refresh_tokens = true
  oauth_refresh_token_validity = 7776000
  OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE;

This command will create a new OAuth-enabled authentication with details specified in individual claims. Let’s take a closer look at specific claims and what they mean

• integration name - name of the integration. We recommend using descriptive names for integrations, e.g. Native OAuth;

• type - defines preferred authentication type. In this case, value is set to OAuth;

• oauth_redirect_uri - defines the client uri that access tokens will be provided to. For Alteryx Designer, this value shall be set to “http://localhost:<port number>/”, where port number indicates the port that Alteryx Designer will be expecting to receive tokens at. Save the port number, you will need it in next steps;

• oauth_issue_refresh_tokens - defines whether the OAuth server needs to issue refresh tokens or not. Alteryx Designer OAuth implementation requires this parameter to be set to “true”

• oauth_refresh_token_validity - sets validity of refresh tokens in milliseconds.

Here is an example query for reference:

create security integration OAuth
  type = oauth
  enabled = true
  oauth_client = custom
  oauth_client_type = 'confidential'
  oauth_redirect_uri = 'http://localhost:5010/'
  oauth_issue_refresh_tokens = true
  oauth_refresh_token_validity = 7776000
  OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE; 

Upon running this query, you shall see “Integration OAuth successfully created.” message.

Step 2: Collect Snowflake details

Now that we created a new OAuth authentication server, you need to collect following instance details to establish connection:

• Server - Snowflake instance uri without https prefix (required);

• Database - database you want to access (optional);

• Schema - schema you want to use (optional);

• Warehouse - warehouse you want to connect to (required);

• Role - Snowflake role (optional);

Additionally to the above details, we also need to collect details of authentication server we have just created. To achieve this, please go ahead and run the following SQL in Snowflake:

desc security integration OAuth;

Note, OAuth is the name of security integration we created in first step.

Upon running this SQL, you shall see your security integration details. Please locate OAuth client id parameter and copy its values - this will be known as your Client ID, you will need it later;

Next, run the following SQL to get your client secret value:

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OAUTH');

 Note, OAuth is the name of security integration we created in first step.

Copy and save value of the secret.

Finally, let’s prepare and list all details of our authentication server:

• OAuth authority uri - uri of your Snowflake instance without https prefix;

• OAuth redirect port - the port you specified when creating authentication server;

• Client ID - the OAuth server Client ID we collected earlier;

• Client Secret - the OAuth server client secret we collected earlier.

Step 3: Access your Snowflake data from Alteryx Designer

Now that we collected all details we need, we can proceed with creating a new Snowflake connection.

• Add input or output tool, check “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.
• Add input or output tool, check “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 credentials and select OAuth as authentication method. Next, provide required inputs we collected in previous steps:

After filling out above details and clicking connect, you will be redirected to the Snowflake login page. You will be prompted to login with your account, grant required permissions, and finally read your Snowflake data.

Related Documentation

Please refer to the following documentation to learn more about Snowflake OAuth

Configure Snowflake OAuth for Custom Clients | Snowflake Documentation