Setting Up a Snowflake API Connection

To enable the use of the Control-M for Snowflake plug-in, you must perform the following setup tasks:

Creating a Snowflake Security Integration

  1. Connect to Snowflake with a user that has AccountAdmin permissions.
  2. Open a Snowflake worksheet.

  3. Run the Create Security Integration command, as follows:

    create security integration <Interface_Name>
    type=oauth
    enabled=true
    oauth_client=CUSTOM
    oauth_client_type='CONFIDENTIAL'
    oauth_redirect_uri=<Callback_URI>
    oauth_issue_refresh_tokens=true
    oauth_refresh_token_validity=<Token_Validity>

Obtaining Security Integration Connection Details

  1. Open a Snowflake worksheet.
  2. Run the Describe Security Integration command, as follows:
    describe security integration <Interface_Name>
  3. Copy the values of the following properties for later use:
    • OAUTH_AUTHORIZATION_ENDPOINT
    • OAUTH_TOKEN_ENDPOINT
    • OAUTH_CLIENT_ID
    • OAUTH_REDIRECT_URI
  4. Obtain the value of the OAUTH_CLIENT_SECRET property by running the following command:
    SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<Interface_Name>');

Obtaining the Security Code

  1. Open a web-browser.

  2. Navigate to the following URL. 

    Note

    In this URL, include the property values that you obtained in the previous task.

    The Client ID and the Redirect URI must be URL-encoded.

    https://<OAUTH_AUTHORIZATION_ENDPOINT>?client_id=<OAUTH_CLIENT_ID> &response_type=code&redirect_uri=<OAUTH_REDIRECT_URI>

  3. Enter credentials for a designated Snowflake user.
    The URL is processed and an adjusted URL is displayed.
  4. Copy the code value (for example, code=1CC89D1884C6B18B97F56082209562C567317D59) that appears within the query string in the adjusted URL.

Generating a Refresh Token

  1. Set up a connection to the API endpoint using the token endpoint that you obtained earlier.
    For this connection, use a POST call with the following Header and Body details:

    • Headers:

      User-Agent

      6.10.2

      Content-Type

      application/x-www-form-urlencoded

      Authorization

      Basic <client credentials>

      In this string, <client credentials> are a Base64-encoded string with the following format:

      <client_id>:<client_secret>

    • Body:
      A body with content type x-www-form-urlencoded and the following key values:

      Key

      Value

      grant_type

      authorization_code

      response_type

      code

      redirect_uri

      as obtained from the Security Integration

      code

      as obtained in the previous task

  2. Run the API call to the token endpoint.
    The response includes a "refresh_token" value that begins with ver:1-hint:
  3. Copy the provided Refresh Token value and URL-encode it.
    The Refresh Token value and various other parameters that you obtained and copied are required during the setup of a Snowflake connection profile in Control-M.