Snowflake

How to get to the “Manage sources” 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 of a default role session:role-any.

Refefences:

  • Azure tenant ID: $tenantId
  • Object ID: $convierObjectId

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:

  • Application ID: $snowflakeAppId

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:role-any 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.

Oauth2 application access

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

In Azure, add “app role” session:role-any 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:

  • Click “SQL database”
  • Enter config:
    • JDBC URL: “jdbc:snowflake://.snowflakecomputing.com/"
    • Leave schema empty (defined below)
    • Additional properties: 
      {
  "account": "<account-identifier>",
  "db": "<database id>",
  "schema": "<schema>",
  "warehouse": "<warehouse>",
  "JDBC_QUERY_RESULT_FORMAT": "JSON"
}
  • Click “Authentication”
    • If system user, select “App token”
      • Enter scope api://$snowflakeAppId/.default
    • If authenticating end user, select “On behalf of”
      • Enter scope api://$snowflakeAppId/.default
  • Click save
  • Connect and save config ->