OAuth 2.0 for Snowflake
Configure Alteryx Analytics Cloud (AAC) to integrate with your Snowflake deployment using OAuth 2.0 to authenticate.
Create OAuth 2.0 Client App in Snowflake
In your Snowflake console, you must create the client app, which includes execution of several SQL statements.
Note
You must have Workspace Admin role to create the client app.
In Snowflake, this object is called a security integration. For more information, see https://docs.snowflake.com/en/sql-reference/sql/create-security-integration.html.
Steps:
Login to the Snowflake console as an account admin.
Click Worksheets.
For your role, select ACCOUNTADMIN.
Paste the following command in the worksheet and modify its parameters:
CREATE [ OR REPLACE ] SECURITY INTEGRATION [IF NOT EXISTS] <NAME> TYPE = OAUTH OAUTH_CLIENT = CUSTOM OAUTH_CLIENT_TYPE = 'CONFIDENTIAL' OAUTH_REDIRECT_URI = '<URI>' ENABLED = TRUE OAUTH_ALLOW_NON_TLS_REDIRECT_URI = FALSE [PRE_AUTHORIZED_ROLES_LIST = ( '<role_name_1>' [ , '<role_name_2>' , ... ]) ] [ BLOCKED_ROLES_LIST = ( '<role_name_3>' [ , '<role_name_4>' , ... ] ) ] OAUTH_ISSUE_REFRESH_TOKENS = TRUE OAUTH_REFRESH_TOKEN_VALIDITY = 7776000 (90 Days) [ NETWORK_POLICY = '<network_policy>'] [ COMMENT = '<Description of your Integration>' ]Parameter
Description
<NAME>
Name of the integration. Example:
OAuth 2.0 Client<URI>
Callback URI of AACAAC.
https://us1.alteryxcloud.com/oauth2/callbackPRE_AUTHORIZED_ROLES_LIST
A comma-separated list of Snowflake roles that do not need user consent when accessing Snowflake. The roles SECURITYADMIN and ACCOUNTADMIN cannot be included in this list.
Astuce
The roles in this list should match up with the roles that are scoped in the OAuth 2.0 client in AACAAC. In the client, you can specify the Snowflake roles that are permitted to use the client for authentication. Roles that are scoped for access that are not in this list must consent to access Snowflake after login. In some use cases, such as API access or scheduled executions, this can be problematic.
BLOCKED_ROLES_LIST
A comma-separated list of Snowflake roles that cannot explicitly consent to use when accessing Snowflake. The roles SECURITYADMIN and ACCOUNTADMIN are included by default in this list. If you need to remove either of those roles, please contact Snowflake Support.
<NETWORK_POLICY>
(Optional) Provide the identifier for any applicable Snowflake network policy.
<COMMENT>
(Optional) Add a comment if needed.
Run the above command. The security integration is created.
Paste the following command and run it to acquire the following information: Client ID, Authorization URL, Token URL, and Refresh Token Expires In, where
<NAME>Is the name you provided above:DESC integration <NAME>
Retain the values for the following parameters. You must apply these parameters to the OAuth 2.0 client that you create in AACAAC:
Snowflake parameter
AACAAC Client parameter
OAUTH_CLIENT_ID
Client Id
OAUTH_AUTHORIZATION_ENDPOINT
Authorization URL
OAUTH_TOKEN_ENDPOINT
Token URL
OAUTH_REFRESH_TOKEN_VALIDITY
Refresh Token Expires In
Paste the following command and run it to acquire the client secret, where
<NAME>Is the name you provided above:SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<NAME>')Retain the values for the following. You must apply these parameters to the OAuth 2.0 client that you create in AACAAC:
Snowflake parameter
AACAAC Client parameter
OAUTH_CLIENT_SECRET
Client Secret
Save your changes.
Create OAuth 2.0 Client for Snowflake
After the Snowflake client app is created, you must create an OAuth 2.0 client in AACAAC, which is used to integrate with the OAuth 2.0 Client app (security integration) that you created above.
Note
You must create one OAuth 2.0 client in AACAAC for each Snowflake role that you wish to use. See "Scopes" below for more information.
Steps:
Login to AACAAC as a workspace administrator.
In the lefthand menu, select User menu > Admin console > OAuth 2.0 Clients.
In the OAuth 2.0 Clients page, click Register OAuth 2.0.0 Client.
Specify the new client.
For the Type value, select
snowflake.You must apply the values listed in the previous section to your client object.
For more information on Scopes, see "Scopes for Snowflake" below.
Access Token Expires in:
600000Note
The value of
600000is required for Snowflake.
To save your OAuth 2.0 client, click Save.
For more information, see Create OAuth2 Client.
Scopes are space-delimited strings that are passed from the client to the client app as part of the authentication process.
The following scope must be specified as part of your Snowflake client definition:
refresh_token session:role:<role_name>
Scope | Description |
|---|---|
refresh_token | (required) Snowflake session tokens have a short duration. By adding this scope, a refresh token is issued for the session. This token allows the OAuth 2.0 client to refresh the connection with Snowflake without user interaction. |
role:<role_name> | (optional) The Snowflake role for which you wish to access its databases, schemas, and tables. If this value is not provided, then the default role is used. Note Only one role can be specified per client. This role must provide access to the databases, schemas, and objects that you wish to make accessible through this client. Note The value for |
Create Snowflake Connection
After you have created the two OAuth 2.0 client references, you can create a connection to your Snowflake databases.
Note
You must create a separate connection for each OAuth 2.0 client that is available in AACAAC.
For more information, see Snowflake Connections.
Troubleshooting
The following may occur when trying to connect to Snowflake databases using OAuth 2.0.
If you receive an invalid consent request error, then the user that is passed for OAuth 2.0 authorization does not have access to the role that is referenced in the corresponding OAuth 2.0 client that you created in AACAAC.
You can do one of the following:
Specify a different user in the connection.
Create a new OAuth 2.0 client in AACAAC which is scoped for a role that the database user has.
Note
This new role must also be authorized to use the security integration within Snowflake.