Snowflake connection guide for Jitterbit App Builder

Introduction

This guide shows you how to connect an App Builder app to a Snowflake account using OAuth or certificate-based authentication.

OAuth authentication

Prerequisites

An active Snowflake account, configured for OAuth.

1. Set up a security provider

Select IDE > Security Providers.
Click the + User Authentication button.
Set values for the following fields:
- In the Settings section:
  - Name: Enter a name for the security provider.
  - Type: Select OAuth.
  - Enabled: Selected.
- In the Authentication section:
  - OAuth Grant: Select Authorization Code.
  - OAuth Client Authentication: Select Basic.
  - OAuth Resource Authentication: Select Bearer.
- In the Tokens section:
  - Owner: Select User.
- In the Sign In section:
  - Redirect On Challenge: Selected.
Click the Save button. Additional panels appear.
In the Endpoints section, click the + Endpoint button, then add the following endpoints:

Endpoint Type URL

Authorization Endpoint https://{Snowflake account URL}/oauth/authorize

Token Endpoint https://{Snowflake account URL}/oauth/token-request
In the Credentials section, click the + Credential button, then add the following credential:

Type User Name Password

Client OAUTH_CLIENT_ID OAUTH_CLIENT_SECRET

Note

OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET are outputs of the SYSTEM$SHOW_OAUTH_CLIENT_SECRETS command.
In the Properties section, click the + Property, then add the following property:

Parameter Value

Scopes session:role:SYSADMIN

2. Set up a data server

Select IDE > Data Servers.
Click the + Server button.
In the Server popup, set values for the following:
- In the Server Settings section:
  - Server Name: Enter a name for this Snowflake server.
  - Type: Select Snowflake. Additional fields appear. Set values for the following:
    - URL: The URL.
    - Warehouse: Your Snowflake warehouse name.
    - Database Name: Your Snowflake database name.
    - Schema Name: Your Snowflake schema name.
- In the Security section:
  - Security Provider: Select the OAuth security provider created earlier.
  - User Name: Leave blank.
  - Password: Leave blank.
- Description: (Optional) Enter a description for this data server.
Click the Save button.
In the Server Settings section, click the Advanced Settings button. The Server (Advanced Settings) popup opens.
Click the Edit button.
In the Advanced field, enter AuthScheme=OAuth.
Click the Save button, then close the Server (Advanced Settings) and Server popups.

You can now use the data server in your apps.

Certificate-based authentication

Certificate-based authentication uses an RSA key pair. The public key is registered with the Snowflake user account, and App Builder uses the private key file to authenticate.

Prerequisites

An active Snowflake account.
A generated RSA key pair. See Generate the private key in the Snowflake documentation for instructions. The private key file (.p8) must be on the App Builder server's file system and readable by the App Builder process.

1. Configure Snowflake

Run the following SQL commands in Snowflake as a user with the ACCOUNTADMIN role.

Create a Snowflake user with the public key assigned:

CREATE USER {username}
  LOGIN_NAME = {username}
  DISPLAY_NAME = 'App Builder (Key Auth)'
  -- The public key must be a single, continuous string.
  -- Do not include the '-----BEGIN PUBLIC KEY-----' or '-----END PUBLIC KEY-----' header lines.
  RSA_PUBLIC_KEY = '{public_key_content}';

Grant the required role to the user:

GRANT ROLE {role_name} TO USER {username};

Grant usage on the database and schema to the role:

GRANT USAGE ON DATABASE {database_name} TO ROLE {role_name};
GRANT USAGE ON SCHEMA {database_name}.{schema_name} TO ROLE {role_name};

Set the default context for the user:

ALTER USER {username} SET
    DEFAULT_ROLE = {role_name},
    DEFAULT_WAREHOUSE = '{warehouse_name}',
    DEFAULT_NAMESPACE = '{database_name}.{schema_name}';

2. Set up a data server

Select IDE > Data Servers.
Click the + Server button.
In the Server popup, set values for the following:
- In the Server Settings section:
  - Server Name: Enter a name for this Snowflake server.
  - Type: Select Snowflake. Additional fields appear. Set values for the following:
    - URL: The URL.
    - Warehouse: Your Snowflake warehouse name.
    - Database Name: Your Snowflake database name.
    - Schema Name: Your Snowflake schema name.
- In the Security section:
  - Security Provider: Leave blank.
  - User Name: Enter the Snowflake username created in step 1.
  - Password: Leave blank.
- Description: (Optional) Enter a description for this data server.
Click the Save button.
In the Server Settings section, click the Advanced Settings button. The Server (Advanced Settings) popup opens.
Click the Edit button.
In the Advanced field, enter the following connection string, replacing {path_to_private_key_file} with the absolute path to the .p8 private key file on the App Builder server:
```
AuthScheme=PrivateKey;PrivateKeyType=PEMKEY_FILE;PrivateKey={path_to_private_key_file}
```
Click the Save button, then close the Server (Advanced Settings) and Server popups.

You can now use the data server in your apps.

Troubleshooting

If you experience issues with Snowflake, these troubleshooting steps are recommended:

Enable data logging to generate log files and data, which you can download and inspect.
Refer to the CData ADO.NET Provider for Snowflake documentation for detailed information.

Endpoint Type	URL
Authorization Endpoint	`https://{Snowflake account URL}/oauth/authorize`
Token Endpoint	`https://{Snowflake account URL}/oauth/token-request`