# Snowflake
URL: https://docs.datarobot.com/en/docs/reference/data-ref/data-sources/dc-snowflake.html

# Snowflake

## Supported authentication

- Username/password
- Key pair
- Snowflake OAuth
- External OAuth with Okta or Microsoft Entra ID (formerly, Azure AD)

## Username/password

### Prerequisites

The following is required before connecting to Snowflake in DataRobot:

- A Snowflake account

> [!WARNING] OAuth with security integrations
> If you create a security integration when configuring OAuth, you must specify the `OAUTH_REDIRECT_URI` as `https://<datarobot_app_server>/account/snowflake/snowflake_authz_return`

### Required parameters

In addition to the required fields listed below, you can learn about other available configuration options in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/jdbc-parameters.html).

| Required field | Description | Documentation |
| --- | --- | --- |
| address | A connection object that stores a secure connection URL to connect to Snowflake.Example: {account_name}.snowflakecomputing.com | Snowflake documentation |
| warehouse | A unique identifier for your virtual warehouse. | Snowflake documentation |
| db | A unique identifier for your database. | Snowflake documentation |

## Key pair

### Prerequisites

The following is required before connecting to Snowflake in DataRobot:

- A Snowflake account
- A private key file (for instructions on generating a private key, see the Snowflake documentation )

### Set up the connection in DataRobot

The tabs below show how to configure a Snowflake data connection using key pair authentication:

**DataRobot Classic:**
When creating a Snowflake [data connection](https://docs.datarobot.com/en/docs/classic-ui/data/connect-data/data-conn.html#create-a-new-connection) in DataRobot Classic, select Key-pair as your credential type. Then, fill in the [required parameters](https://docs.datarobot.com/en/docs/reference/data-ref/data-sources/dc-snowflake.html#required-parameters).

[https://docs.datarobot.com/en/docs/images/snow-keypair-2.png](https://docs.datarobot.com/en/docs/images/snow-keypair-2.png)

**Workbench:**
When creating a Snowflake [data connection](https://docs.datarobot.com/en/docs/workbench/nxt-workbench/dataprep/add-data/connect.html) in Workbench, select Key-pair as your credential type. Then, fill in the [required parameters](https://docs.datarobot.com/en/docs/reference/data-ref/data-sources/dc-snowflake.html#required-parameters).

[https://docs.datarobot.com/en/docs/images/snow-keypair-1.png](https://docs.datarobot.com/en/docs/images/snow-keypair-1.png)


### Required parameters

In addition to the required fields listed below, you can learn about other available configuration options in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/jdbc-parameters.html).

| Required field | Description |
| --- | --- |
| Username | A unique identifier of a user inside a Snowflake account (i.e., the name you use to log into Snowflake). |
| Private key | The string copied from your private key file. |
| Display name | A unique identifier for your Snowflake credentials within DataRobot. |

For more information on Snowflake key pair authentication, including generating private keys and configuring key pair authentication in Snowflake, see the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/key-pair-auth).

## Snowflake OAuth

### Prerequisites

The following is required before connecting to Snowflake in DataRobot:

- A Snowflake account
- Snowflake OAuth configured

### Set up the connection in DataRobot

When connecting with OAuth parameters, you must create a new data connection.

To set up a data connection using OAuth:

1. Follow the instructions forcreating a data connectionandtesting the connection.
2. After clickingTest Connection, a Credentials window appears. Enter your Snowflake client ID, client secret, and account name. SelectSnowflakeas the OAuth provider.
3. ClickSave and sign in.
4. Enter your Snowflake username and password. ClickSign in.
5. To provide consent to the database client, clickAllow.

If the connection is successful, the following message appears in DataRobot:

### Required parameters

In addition to the required fields listed below, you can learn about other available configuration options in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/jdbc-parameters.html).

| Required field | Description | Documentation |
| --- | --- | --- |
| Required fields for data connection |  |  |
| address | A connection object that stores a secure connection URL to connect to Snowflake.Example: {account_name}.snowflakecomputing.com | Snowflake documentation |
| warehouse | A unique identifier for your virtual warehouse. | Snowflake documentation |
| db | A unique identifier for your database. | Snowflake documentation |
| Required fields for credentials |  |  |
| Client ID | The public identifier for your application. | Snowflake documentation |
| Client secret | A confidential identifier used to authenticate your application. | Snowflake documentation |
| Snowflake account name | A unique identifier for your Snowflake account within an organization. | Snowflake documentation |

## Snowflake External OAuth

### Prerequisites

The following is required before connecting to Snowflake in DataRobot using OAuth:

**Okta:**
A Snowflake account.
External OAuth
configured in Snowflake for Okta.

> [!WARNING] External OAuth with security integrations
> If using Okta as the external identity provider (IdP), you must specify `https://<datarobot_app_server>/account/snowflake/snowflake_authz_return` as a Sign-in redirect URI when [creating a new App integration in Okta](https://docs.datarobot.com/en/docs/reference/data-ref/data-sources/dc-snowflake.html#external-idp-setup).

**Microsoft Entra ID:**
Microsoft Entra ID is the new name for Azure Active Directory.

A Snowflake account.
External OAuth
configured in Snowflake for Microsoft Entra ID.

> [!WARNING] External OAuth with security integrations
> If using Entra ID as the external identity provider (IdP), you must specify `https://<datarobot_app_server>/account/snowflake/snowflake_authz_return` as a Redirect URI when [registering both applications in Entra ID](https://docs.datarobot.com/en/docs/reference/data-ref/data-sources/dc-snowflake.html#external-idp-setup).


### External IdP setup

> [!NOTE] Note
> This section uses example configurations for setting up an external IdP. For information on setting up an external IdP based on your specific environment and requirements, see the documentation for Okta or Entra ID.

In the appropriate external IdP, create the Snowflake application(s):

**Okta:**
Create a new App Integration in Okta:

Go to
Applications > Applications
.
Click
Create App Integration
.
For the
Sign-in method
, select
OIDC - OpenID Connect
.
For the
Application type
, select
Web Application
.
Click
Next
.
Enter a name for the new application.
Under
Grant type
, make sure the following options are selected:
Client Credentials
Authorization Code
Refresh Token
Under
LOGIN
, add
http://<datarobot_app_server>/account/snowflake/snowflake_authz_return
to the
Sign-in redirect URIs
.
Click
Save
—this generates your
Client ID
and
Client secret
.

Create a new Authorization Server:

Go to
Security > API > Authorization Server
. Click
Add Authorization Server
.
Enter a name.
Set
Audience
to
https://<account_identifier>.snowflakecomputing.com/
.
Click
Save
.
Go to
Scopes > Add Scope
.
Set
Name
to
session:role:public
.
public
refers to the Snowflake role called Public. This can be a different role name, but it must also exist within Snowflake.
For
User Consent
, set
Required
.
Add
Require user consent for this scope
and
Block services from requesting this scope
.
(Optional) Set the
offline_access
scope to require consent.
Go to
Access Policies
and click
Add Policy
.
For
Assign to
, select
The following clients
and select the application you just created.
Click
Create Policy
.
Click
Add Rules
.
Enter a name and make sure the following options are selected:
Add Check-in
Client Credentials
.
Add Check-in
Authorization Code
.
Click
Create Rule
.

> [!WARNING] Required information
> Before proceeding with Snowflake and DataRobot setup, make sure you've copied the following information in Okta:
> 
> Client ID
> Client secret
> Okta Issuer URL
> JWKS URI
> Audience
> 
> Audience is listed under the Settings tab, and to find the issuer URL and JWKS URI, click on the Metadata URI link. This loads the JSON items that includes the required information.

(Optional) Test the application
To test the application you just created in Okta:
Go to
Token Preview
and fill in the
Request Properties
with the grant type, a user assigned to the application, and the specified scope. Click
Preview Token
.
This results in the following:
Issuer
, for example,
https://dev-11863425.okta.com/oauth2/aus15ca55wkdOxplJ5d7
.
Auth
Token
for programmatic access to the Okta API.
Auth server metadata JSON (found in
Settings > Metadata URI
).
Okta API calls
Get current user
curl --location --request GET 'https://<OKTA_ACCOUNT>.okta.com/api/v1/users/me' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: SSWS <TOKEN>'
Get the user's grants
curl --location --request GET 'https://<OKTA_ACCOUNT>.okta.com/api/v1/users/<USER_ID>/clients/<CLIENT_ID>/grants' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: SSWS <TOKEN>'
Revoke grant/consent
curl --location --request DELETE 'https://<OKTA_ACCOUNT>.okta.com/api/v1/users/<USER_ID>/grants/<GRANT_ID>' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: SSWS <TOKEN>'

**Microsoft Entra ID:**
Register an application for Snowflake Resource in Microsoft Entra ID:

Go to
MS Azure > Microsoft Entra ID > App registrations
.
Click
New registration
.
Under
Name
, enter
Snowflake OAuth Resource
.
Under
Supported account types
, select
Accounts in this organizational directory only
.
Under
Redirect URI
, select
Web
and enter
https://<datarobot_app_server>/account/snowflake/snowflake_authz_return
.
Click
Register
.
Expose the API.
Click the set link next to
Application ID URI
make sure it is a unique ID (this does not need any change ). This will be the
<SNOWFLAKE_APPLICATION_ID_URI>
value.
Click
Add a scope
to add a scope representing the Snowflake role.
Using the name of the role in Snowflake, enter the scope as follows:
session:scope:<snowflake_role_name>
. If it's a custom role, set the role name in Snowflake, and enter the scope as follows:
session:scope:<custom_role_name>
.
Add another scope name as
session:role-any
.
Copy the value of the newly created scope. This will be
<OAUTH_SCOPES>
value.

Register an application for Snowflake Client App in Microsoft Entra ID:

Go to
MS Azure > Microsoft Entra ID > App registrations
.
Click
New registration
.
Under
Name
, enter
Snowflake OAuth Client
.
Under
Supported account types
, select
Accounts in this organizational directory only
.
Under
Redirect URI
, select
Web
and enter
https://<datarobot_app_server>/account/snowflake/snowflake_authz_return
.
Click
Register
.
In the
Overview
section, copy the client ID from the
Application (client) ID
field. This will be known as the
<OAUTH_CLIENT_ID>
.
Open
Certificates & secrets
and select
New client secret
.
Click
Add
. Copy the secret. This will be known as the
<OAUTH_CLIENT_SECRET>
.
Optional
For programmatic clients that need to request an access token on behalf of a user, configure delegated permissions for applications as follows:
Click
API Permissions
.
Click
Add Permission
.
Click on My APIs.
Click on the Snowflake OAuth Resource that you created in Step 1: Configure the OAuth Resource in Entra ID.
Click on the Delegated Permissions box.
Check on the Permission related to the Scopes created in step 3
session:role-any
Click Add Permissions.

Collect the following information for the Snowflake integration:

Click
App Registrations
.
Click the
Snowflake OAuth Resource
.
On the
Overview
page, click
Endpoints
.
Copy the first part of the
OAuth 2.0 token endpoint (v2)
URL, e.g.
https://login.microsoftonline.com/6064c47c-80e4-4a555b-82ee-1fc5643b37a2
. This will be
<ISSUER_URL>
value
Copy the value of
OpenID Connect metadata document
and paste it on a new window. Locate the
"jwks_uri"
parameter, which will be the
<JWS_KEY_ENDPOINT>
value (e.g.,
https://login.microsoftonline.com/6064c47c-80e4555b-82ee-1fc5643b37a2/discovery/v2.0/keys
).
Copy the value of
Federation metadata document
and open the URL in a new window. Locate the
"entityID"
parameter, which will be the
<ENTITY_ID>
value, also known as
<AZURE_AD_ISSUER>
(e.g.,
https://sts.windows.net/6064c47c-80e4-555582ee-1fc5643b37a2/
).

Make sure you've copied the following values:

<OAUTH_SCOPES>
copied from
Snowflake OAuth Resource
.
<APP_ID_URI>
,
<ISSUER_URL>
,
<JWS_KEY_ENDPOINT>
and
<ENTITY_ID>
values from the Overview and Endpoints view of
Snowflake OAuth Resource
.
<OAUTH_CLIENT_ID>
and
<OAUTH_CLIENT_SECRET>
copied from
Snowflake OAuth Client
.


### Snowflake setup

> [!NOTE] Note
> This section uses example configurations for setting up an external IdP in Snowflake. For information on setting up an external IdP in Snowflake based on your specific environment and requirements, see the Snowflake documentation.

In Snowflake, execute the following commands to create an integration for the appropriate external IdP:

**Okta:**
```
create security integration external_oauth_okta_2
    type = external_oauth
    enabled = true
    external_oauth_type = okta
    external_oauth_issuer = '<OKTA_ISSUER>'
    external_oauth_jws_keys_url = '<JWKS_URI>'
    external_oauth_audience_list = ('<AUDIENCE>')
    external_oauth_token_user_mapping_claim = 'sub'
    external_oauth_snowflake_user_mapping_attribute = 'login_name';

CREATE OR REPLACE USER <user_name>
  LOGIN_NAME = '<okta_user_name>';

alter user <user_name> set DEFAULT_ROLE = 'PUBLIC';
```

Reference values:

OKTA_ISSUER
:
https://dev-11863425.okta.com/oauth2/aus15ca55wxplJ5d7
AUDIENCE
:
https://hl91180.us-east-2.aws.snowflakecomputing.com/
JWKS_URI
:
https://dev-11863425.okta.com/oauth2/aus15ca55wxplJ5d7/v1/keys
(retrieved from Okta Auth server Metadata JSON)
okta_user_name
(retrieved from
Okta > Directory > People
, select a user, and then go to
Profile > Username/login
)

**Microsoft Entra ID:**
> [!NOTE] Note
> You must have the `accountadmin` role, or a role with the global `CREATE INTEGRATION` privilege to create the integration below.

```
create security integration external_oauth_azure_1
   type = external_oauth
   enabled = true
   external_oauth_type = azure
   external_oauth_issuer = '<ENTITY_ID>'
   external_oauth_jws_keys_url = '<JWS_KEY_ENDPOINT>'
   external_oauth_audience_list = ('<APP_ID_URI>')
   external_oauth_token_user_mapping_claim = 'upn'
   external_oauth_any_role_mode = 'ENABLE'
   external_oauth_snowflake_user_mapping_attribute = 'login_name';
```

Reference values:

<ENTITY_ID>
:
https://sts.windows.net/6064c47c-80e4-4a2b-4444-1fc5643b37a2/
<JWS_KEY_ENDPOINT>
:
https://login.microsoftonline.com/6064c47c-80e4-4a2b-4444-1fc5643b37a2/discovery/v2.0/keys
<APP_ID_URI>
:
api://8aa2572f-c9e6-4e91-4444-dcd84c856dd2

Grant access on the integration to the public role:

`grant USE_ANY_ROLE on integration external_oauth_azure_1 to PUBLIC;`

`grant USE_ANY_ROLE on integration external_oauth_azure_1 to <custom_role>;`

Ensure that the `LOGIN_NAME` of the user is the same as the Azure login. Verify using the following query in Snowflake:

`DESC USER <SNOWFLAKE_LOGIN_NAME>`

If the login names are different, Snowflake cannot validate the access token generated with Entra ID. In that case, use the command below to match Snowflake with Azure:

`ALTER USER <SNOWFLAKE_LOGIN_NAME> SET LOGIN_NAME='<EMAIL_USED_FOR_AZURE_LOGIN>'`

If you are using custom role and is not your default, use the command below to set it as your default to test connectivity:

`ALTER USER <USERNAME> SET DEFAULT ROLE='<custom_role>';`


### Set up the connection in DataRobot

When connecting with external OAuth parameters, you must create a new data connection.

To set up a Snowflake data connection using external OAuth:

1. Follow the instructions forcreating a data connectionandtesting the connection.
2. After clickingTest Connection, select your OAuth provider from the dropdown—either Okta or MS Azure AD— and fill in theadditional required fields.Then, clickSave and sign in.
3. In the OAuth modal, enter your Okta or Azure username and password. ClickSign in.
4. To provide consent to the database client, clickAllow.

If the connection is successful, the following message appears in DataRobot:

### Required parameters

In addition to the required fields listed below, you can learn about other available configuration options in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/jdbc-parameters.html).

| Required field | Description | Documentation |
| --- | --- | --- |
| Required fields for data connection |  |  |
| address | A connection object that stores a secure connection URL to connect to Snowflake.Example: {account_name}.snowflakecomputing.com | Snowflake documentation |
| warehouse | A unique identifier for your virtual warehouse. | Snowflake documentation |
| db | A unique identifier for your database. | Snowflake documentation |
| Required fields for credentials |  |  |
| Client ID | The public identifier for your application.In the Okta Admin console, go to Applications > Applications > Your OpenID Connect web app > Sign On tab > Sign On Methods.In Microsoft Entra ID/Azure AD, this is the value of Application(client) ID. You copied it as <OAUTH_CLIENT_ID> in the above instructions. | Okta or Entra ID documentation |
| Client secret | A confidential identifier used to authenticate your application.In the Okta Admin console, go to Applications > Applications > Your OpenID Connect web app > Sign On tab > Sign On Methods.In Microsoft Entra ID/Azure AD, this is the client secret. You copied it as <OAUTH_CLIENT_SECRET> in the above instructions. | Okta or Entra ID documentation |
| Snowflake account name | A unique identifier for your Snowflake account within an organization. | Snowflake documentation |
| Issuer URL | A URL that uniquely identifies your SAML identity provider. "Issuer" refers to the Entity ID of your identity provider.Examples: Okta: https://<your_company>.okta.com/oauth2/<auth_server_id>Microsoft Entra ID:You copied it as <ISSUER_URL> in the above instructions, e.g. https://login.microsoftonline.com/<Azure_Tenant_ID> | Okta or Entra ID documentation |
| Scopes | Contains the name of your Snowflake role. Examples: Parameters for a Snowflake Analyst. Okta: session:role:analystMicrosoft Entra ID: <OAUTH_SCOPES> e.g. api://8aa2572f-c9e6-4e91-9555-dcd84c856dd/session:role-any | Snowflake documentation |

Reach out to your administrator for the appropriate values for these fields.

## Feature considerations

- By default, Snowflake preserves the case of alphabetic characters when storing and resolving double-quoted identifiers; however, if you override this default in Snowflake, double-quoted identifiers are stored and resolved as uppercase letters. Because DataRobot is a case-sensitive platform, it's important to preserve the original case of the letters.
- To avoid potential issues related to case-sensitivity, go to your Snowflake data connection in DataRobot, add the QUOTED_IDENTIFIERS_IGNORE_CASE parameter, and set the value to FALSE . See the Snowflake documentation for more details.
- If you plan to set up scheduled jobs, such as refreshing datasets, key pair or basic (username/password) authentication are the recommended methods to use when connecting to Snowflake— not OAuth. When an access token is expired, it can be renewed with a refresh token without re-authentication. However, when the refresh token expires, you must re-authenticate.

## Troubleshooting

| Problem | Solution | Instructions |
| --- | --- | --- |
| When attempting to execute an operation in DataRobot, the firewall requests that you clear the IP address each time. | Add all allowed IPs for DataRobot. | See Allowed source IP addresses. If you've already added the allowed IPs, check the existing IPs for completeness. |
| DataRobot returns the following message when testing external OAuth Snowflake connection with Microsoft Entra ID: AADSTS700016: Application with identifier 'aa2572f-c9e6-4e91-9eb1-dcd84c856dd2' was not found in the directory 'Azure directory "datarobot" ("azuresupportdatarobot")'. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant. | Make sure scopes were created, granted, and assigned to the resource in Azure. | Refer to the Snowflake setup section for more details. |
| DataRobot returns the following message when testing external OAuth after adding the data connection:JDBC connect failed for jdbc:snowflake://datarobot_partner.snowflakecomputing.com?CLIENT_TIMESTAMP_TYPE_MAPPING=TIMESTAMP_NTZ&db=SANDBOX&warehouse=DEMO_WH&application=DATAROBOT&CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=false. Original error: The role requested in the connection or the default role if none was requested in the connection (ACCOUNTADMIN) is not listed in the Access Token or was filtered. Please specify another role, or contact your OAuth Authorization server administrator. | Make sure the user who is establishing a connection with Azure has default role assigned. | The default role needs to be anything other than ACCOUNTADMIN, ORGADMIN, or SECURITYADMIN. If the session:scope is created with scope:role-any, the user can log in with any role other than the admin roles stated. |
| DataRobot returns the following message when testing the connection: Invalid Request: The request tokens do not match the user context. Do not copy the user context values (cookies; form fields; headers) between different requests or user sessions; always maintain the ALL of the supplied values across a complete single user flow. Failure Reasons:[Token values do not match;] | Make sure the login name of the user matches the login name in both Snowflake and Azure to map user and create access tokens. | You can alter the login name in Snowflake to match the username of Azure if it does not already match. |
| DataRobot returns the following error message when attempting to authenticate Snowflake credentials: Incorrect username or password was specified. | Confirm that your parameters are valid; if they are, use the recommended driver version. | Check the username, private key, and passphrase; if all parameters are valid, use the recommended driver version from the dropdown under Show additional parameters > Driver.If you are using driver version 3.13.9:Click Show additional parameters.Click Add parameter and select account.Enter your account name in the field.For more information, see the Snowflake community article. |

---
