Snowflake Azure OAuth2 Account

In this article

Overview

You can use this account type to connect Snowflake Snaps with data sources that use Snowflake Azure OAuth2 Account.

Prerequisites

Create a Security Integration in Snowflake to generate a client ID and a client secret. Learn more about generating a Client ID and a Client Secret in Snowflake.

Limitations and Known Issues

When refreshing the access token, the Snowflake API prevents you from getting a new refresh token as the refresh tokens are short lived with a validity of 90 days (7776000 seconds).

Solution: To get a new token after every 90 days you must reauthorize your Snowflake account for the token to be valid for the next 90 days. We recommend you to set the oauth_refresh_token_validity to 7776000 seconds as shown below when creating the Security Integration in Snowflake.

alter integration <integration name> set oauth_refresh_token_validity = 7776000;

Account Settings

azure.png

 

  • Asterisk ( * ): Indicates a mandatory field.

  • Suggestion icon ( ): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon ( ): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon ( ): Indicates that you can add fields in the field set.

  • Remove icon ( ): Indicates that you can remove fields from the field set.

Field Name

Field Type

Description

Field Name

Field Type

Description

Label*

 

Default Value: [None]
Example: Snowflake Azure OAuth2 Account

String

Specify a unique name for your account instance.

 

Client ID*

 

Default Value: N/A
Example: GZxuj932klnbue8=

String

Specify the OAuth Client ID (to be used for token request) that you obtain from the Snowflake Console. Learn more about How to generate OAuth Client ID and Client secret.

 

Client secret

 

Default Value: N/A
Example: !tz@wld*(687

String

Specify the OAuth Client secret that you obtain from the Snowflake Console. 

 

Access token*

 

Default Value: N/A
Example: <Encrypted>

String

Auto-generated upon account authorization. The access token is used to make API requests on behalf of the user associated with the client ID.

Refresh token

 

Default Value: N/A
Example: <Encrypted>

String

Auto-generated upon account authorization. The token used to refresh the access token.

To access the API beyond the lifetime of a single access token, your application can obtain a refresh token. The application stores the refresh token for future use and automatically refreshes the access token before it expires.

Access token expiration

Default Value: N/A
Example: 16541

Integer

Auto-generated upon account authorization. The number of seconds after which the access token expires.

We recommend you to set the oauth_refresh_token_validity to 7776000 seconds when creating the Security Integration in Snowflake as this is the maximum time Snowflake allows for obtaining refresh tokens.

Header authenticated

 

Default Value: Deselected

Checkbox

Select this checkbox if the endpoint uses bearer header authentication.

 

OAuth2 authorization endpoint*

 

Default Value: N/A
Example: https://myaccount.snowflakecomputing.com/oauth/authorize

String

Specify the endpoint in this format https://<account_identifier>.snowflakecomputing.com/oauth/authorize to authorize the application.
Account identifier is the full name of your account that is provided by Snowflake.

 

OAuth2 token endpoint*

 

Default Value: N/A
Example: https://myaccount.snowflakecomputing.com/oauth/token-request

String

Specify the OAuth2 token in this format https://<account_identifier>.snowflakecomputing.com/oauth/token-request to get the access token.

 

Grant type

 

Default Value: authorization_code
Example: client_credentials

Dropdown list

Select one of the following Grant types for authorization:

  • password: Obtains access token using your login credentials (username and password). When selected, it populates the following fields:

    • Username: Enter the username of the account type.

    • Password: Enter the password of the account type.

  • authorization_code: Authentication using credentials (username and password), which return to the client through a redirect URL. The application then receives the authorization code from the URL and uses it to request an access token.

  • client_credentials: Obtains an access token for the client ID and client secret through the token endpoint URL.

Username

String

Specify the username to connect to the Snowflake database server.

Appears when you select Password for Authentication Type.

Password

String

Specify the password associated with the username specified above. This will be used as the default password while retrieving connections.

Token endpoint config

Use this field to define custom properties for the OAuth2 token endpoint. Depending on the request parameters, this endpoint returns access tokens or refresh tokens.

Token endpoint parameter

 

Default Value: N/A
Example: redirect_uri

String

Specify the parameter for the token endpoint.

 

Token endpoint parameter value

 

Default Value: N/A
Example: 

https://elastic.snaplogic.com/api/1/snowflake/admin/oauth2callback/snowflake

https://emea.snaplogic.com/api/1/snowflake/admin/oauth2callback/snowflake

String

Specify the value for the token endpoint parameter.

 

Authorization endpoint config

Use this fieldset to define custom properties for the OAuth2 authentication endpoint.

Authentication parameter

Default Value: N/A
Example: redirect_uri

String

Specify the parameter for OAuth2 authentication.

 

Authentication parameter value

Default Value: N/A
Example: 

https://elastic.snaplogic.com/api/1/snowflake/admin/oauth2callback/snowflake

https://emea.snaplogic.com/api/1/snowflake/admin/oauth2callback/snowflake

String

Specify the value for the OAuth2 authentication parameter.

 

Auto-refresh token

 

Default Value: Deselected 

Checkbox

Select this checkbox to enable auto-refresh of the access token before it expires.

 

Account properties*

 

JDBC JARs

Use this field set to add a list of JDBC JAR files to be loaded. By default, the Snowflake account is bundled with the JDBC driver version 3.16.0. However, you can add a custom JAR file.

Click + to add a new row for each JDBC JAR file. Add each JAR file in a separate row. See Downloading the JDBC Driver for more information about JDBC drivers and downloading the appropriate driver for your account.

JDBC driver

 

Default Value: N/A
Example: snowflake-jdbc-3.16.0.jar

String

Specify the fully-qualified name of the JDBC driver to be used for connecting to the server.

Hostname*

 

Default Value: N/A
Example: demo.snowflake.net

String/Expression

Specify the hostname of the Snowflake server to which you want to connect the new account.

 

Port number*

 

Default Value: 443 

Integer/Expression

Specify the port number associated with the Snowflake database server that you want to use for the account.

Database name*

 

Default Value: N/A
Example: snapsdb

String/Expression

Specify the name of the Snowflake database to which you want to connect.

 

Warehouse name*

 

Default Value: N/A
Example: SL_WH

String/Expression

Specify the name of the warehouse to use for performing the required actions.

 

JDBC driver class

 

Default Value: N/A
Example: net.snowflake.client.jdbc.SnowflakeDriver

String

Specify the JDBC driver class to use.

 

Azure storage account name

 

Default Value: N/A
Example: testazurestorage

String/Expression

Specify the name of the instance of the Azure storage account.

Azure storage account key

 

Default Value: N/A
Example: testazurestorage

String/Expression

Specify the key to connect to the instance of the Azure storage account listed above. You can use either of the two keys available in the Access Key tab of the dashboard in the Azure portal to populate this value.

 

Container

 

Default value: N/A
Example: Container1

String/Expression

Specify the name of the Azure storage blob container that you want to use for hosting files.

 

Path

 

Default value: N/A
Example: System Generated

String/Expression

Specify the location of the folder in the container listed above where you want to host files.

 

Shared Access Signature (SAS) token method

 

Default value: User Supplied
Example: System Generated

Dropdown list

The method of supplying the SAS token for the Snaps. You can choose one the following two options:

  • User Supplied: Choose this option if you intend to manually enter the shared access token signature.

    If you opt for the User Supplied option, then you need to ensure that your tokens are valid whenever the pipeline is run; else, the pipelines will fail. For more information, see Generating a SAS Token in Snowflake documentation.

  • System Generated: Choose this option if you want Snaps to generate and use the SAS tokens as and when required.

User token

 

Default Value: N/A

String/Expression

Specify the shared access token that you want to use to access the Azure storage blob folder specified in the path above. You can get a valid SAS token from the Azure portal.

Client side encryption

 

Default Value: N/A
Example: Custom_Key

Dropdown list

Select one of the following options to encrypt the blob before uploading to Microsoft Azure. You can choose one of the following two options:

  • None: Does not use client-side encryption.

  • Custom_Key: Uses a custom key to access the storage blob.

Custom key

String/Expression

Specify the custom key that you want to use to access the Azure storage blob. This property is applicable only when you select Custom_Key in the Client side encryption field above. The key should be a 128- or 256-bit Base64-encoded key.

Advanced properties

URL Properties

Use this field set to configure the URLs associated with this account. 

URL property name

 

Default Value: [None]
Example: queryTimeout

String

Specify the name of the URL property.

 

URL property value

 

Default Value: [None]
Example: 0

String

Specify the URL property value associated with the URL property name.

 

Batch size*

 

 

Default Value: 50
Example: 40

Integer/Expression

Specify the number of statements that you want to execute at a time.

Fetch size*

 

 

Default Value: 100
Example: 40

Integer/Expression

Specify the number of rows you want a query to fetch during each execution.

Min pool size*

Default Value: 3
Example: 2

Integer/Expression

Specify the minimum number of idle connections that you want the pool to maintain at a time. 

Minimum value: 0
Maximum value: No limit

Max pool size*


Default Value: 50
Example: 40

Integer/Expression

Specify the maximum number of connections that you want the pool to maintain at a time.

Minimum Value: 0
Maximum value: No limit

Max lifetime (minutes)*


Default Value: 60
Example: 50

Integer/Expression

Specify the maximum lifetime of a connection in the pool. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed.

Idle timeout (minutes)*


Default Value: 5
Example: 4

Integer/Expression

Specify the maximum amount of time a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool.

 

Checkout timeout (milliseconds)*


Default Value: 10000
Example: 9000

Integer/Expression

Specify the number of milliseconds you want the system to wait for a connection to become available when the pool is exhausted.

Failed to execute query because of SQL compilation error.

 

 

If database usage is not granted for the role, the account validation fails.

Run the following command in Snowflake worksheet:

GRANT USAGE ON DATABASE SNAPDEV TO ROLE public

If schema or table usage is not granted for the role, the account validation fails.

Run the following command in Snowflake worksheet based on requirements:

GRANT USAGE ON SCHEMA SNAPDEV.CUSTOMER TO ROLE public (or) GRANT ALL ON TABLE SNAPDEV.CUSTOMER.TEST TO ROLE public;

Snap Pack History


Related Content