Guide for connecting Snowflake as a data source in Convier

Contents of this page

See the Snowflake documentation for more details on security integrations in Snowflake.

This page assumes you are using Entra ID / Azure AD, and that the OIDC client for Convier is already set up. We also assume the use role session:scope:CONVIER_SNOWFLAKE_USER. Adjust role according to your setup.

Refefences:

You find Object ID under “Entprise Applications” in the Azure Portal, next to “Application ID”

Create Application Registration in Azure that will be added to a Snowflake security integration

References:

Add security integration in Snowflake

If you connect to Snowflake using a security integration, you can choose between delegated and application access. In delegated mode, the Convier will authenticate on behalf of users, meaning that users in Convier also must exist in Snowflake. In application mode, the Convier application is given access directly to Snowflake, and any user who has access to the created project and source in Convier, will have access to Snowflake. The decision between these two will affect where audit logging should occur.

Oauth2 On-Behalf-Of user access

Use this security integration if you want to authenticate individual users in Snowflake.

In Azure, add “scope” session:scope:CONVIER_SNOWFLAKE_USER to $snowflakeAppId.

In Snowflake:

CREATE OR REPLACE SECURITY INTEGRATION external_oauth_azure_1
  TYPE = external_oauth
  ENABLED = true
  EXTERNAL_OAUTH_TYPE = azure
  EXTERNAL_OAUTH_ISSUER = '<https://sts.windows.net/$tenantId/>'
  EXTERNAL_OAUTH_JWS_KEYS_URL = '<https://login.microsoftonline.com/$tenantId/discovery/v2.0/keys>'
  EXTERNAL_OAUTH_AUDIENCE_LIST = ('api://$snowflakeAppId')
  EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'upn'
  EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'login_name'

This assumes users are automatically synced with Entra ID (e.g. using SCIM), and that the user has role CONVIER_SNOWFLAKE_USER in Snowflake.

Oauth2 application access

Use this if you want to give the OIDC client itself access to Snowflake.

In Azure, add “app role” session:role:CONVIER_SNOWFLAKE_USER to $snowflakeAppId

In Snowflake, create system user:

CREATE USER CONVIER_APP
  LOGIN_NAME = '$convierObjectId'
  DISPLAY_NAME = 'Convier';

Then create security integration:

CREATE OR REPLACE SECURITY INTEGRATION external_oauth_azure_1
  TYPE = external_oauth
  ENABLED = true
  EXTERNAL_OAUTH_TYPE = azure
  EXTERNAL_OAUTH_ISSUER = '<https://sts.windows.net/$tenantId/>'
  EXTERNAL_OAUTH_JWS_KEYS_URL = '<https://login.microsoftonline.com/$tenantId/discovery/v2.0/keys>'
  EXTERNAL_OAUTH_AUDIENCE_LIST = ('api://$snowflakeAppId')
  EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'sub'
  EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'login_name'

You’ll need to include EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE' to the query above if you use the default session:role-any.

Using a system user and password

It is also possible to connect to Snowflake using basic credentials. In these cases, create a user with password in Snowflake, then select “Basic credentials” under authentication below.

Configure connection

In Convier:

Misc