---
title: "Snowflake"
visibility: "public"
---

See the [Snowflake documentation](https://docs.snowflake.com/en/sql-reference/sql/create-security-integration-oauth-external) for more details on security integrations in Snowflake.

<Callout type="info" title="Prerequisites">

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.

</Callout>

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:scope:CONVIER_SNOWFLAKE_USER` to `$snowflakeAppId`.

In Snowflake:

```txt
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'
```

<Callout type="info" title="User provisioning prerequisite">

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

</Callout>

### 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:

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

Then create security integration:

```txt
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:

1. Click “SQL database”
2. Enter config:
   - JDBC URL (as rendered in the original): `jdbc:snowflake://.snowflakecomputing.com/”`
   - Set schema as below
   - Additional properties:

     ```json
     {
       "account": "<account-identifier>",
       "db": "<database id>",
       "schema": "<schema>",
       "warehouse": "<warehouse>",
       "role": "CONVIER_SNOWFLAKE_USER"
       "JDBC_QUERY_RESULT_FORMAT": "JSON"
     }
     ```
3. Click “Authentication”
   - If system user, select “App token”
4. Enter scope `api://$snowflakeAppId/.default`
   - If authenticating end user, select “On behalf of”
5. Enter scope `api://$snowflakeAppId/.default`
6. Click save

<Callout type="warning" title="Source example issues">

**Source example issues:** The original JDBC URL is malformed/incomplete (including a stray closing quotation mark and a link to [the original URL fragment](http://snowflakecomputing.com/%E2%80%9D)); the JSON example is missing a comma after `"role"`. Both are preserved rather than silently inventing or correcting configuration values. Review before using these examples.

</Callout>

## Misc

- You may need to open firewall from app service to `ocsp.snowflakecomputing.com:80`, or add `"disableOCSPChecks": "true"` to the properties json
- You will also need to make sure that the firewall is open for all `STAGE` endpoints in snowflake, so that Convier is able to load results larger than 100kb (`SELECT SYSTEM$ALLOWLIST_PRIVATELINK()`)
- Users can be provisioned from Entra to Snowflake with SCIM. Direct group memberships only (groups in groups not supported).
- Make sure the audience in OBO token mathces external security integration. If using scope starting with `api://$snowflakeAppId/session:scope:..`, then audience must be `api://$snowflakeAppId`. If scope is `$snowflakeAppId/session:scope:..`, audience must be just `$snowflakeAppId`. If there is a mismatch, you will get an error `EXTERNAL_OAUTH_JWS_CANT_RETRIEVE_PUBLIC_KEY`.
- You may need to add `?appid=$snowflakeAppId` to the end of `EXTERNAL_OAUTH_JWS_KEYS_URL` to be able to locate the correct `kid`

---
