Snowflake¶
Supported authentication¶
- Username/password
- Snowflake OAuth
- External OAuth with Okta or AzureAD
Username/password¶
Prerequisites¶
The following is required before connecting to Snowflake in DataRobot:
- A Snowflake account
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.
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 |
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:
-
Follow the instructions for creating a data connection and testing the connection.
-
After clicking Test Connection, a window appears and you must enter your Snowflake client ID and client secret, which can be retrieved from your database account. Click Save and sign in.
-
Enter your Snowflake username and password. Click Sign in.
-
To provide consent to the database client, click Allow.
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.
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:
- A Snowflake account.
- External OAuth configured in Snowflake for Okta.
External OAuth with security integrations
If using Okta as the external identity provider (IdP), you must specify http://localhost/account/snowflake/snowflake_authz_return
as a Sign-in redirect URI when creating a new App integration in Okta.
- A Snowflake account.
- External OAuth configured in Snowflake for Microsoft Azure AD.
External OAuth with security integrations
If using Azure AD 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 Azure AD.
External IdP setup¶
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 Azure AD.
In the appropriate external IdP, create the Snowflake application(s):
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.
-
Make sure the following options are selected:
- Client Credentials
- Authorization Code
- Refresh Token
- Require consent
-
Under LOGIN, add
http://localhost/account/snowflake/snowflake_authz_return
to the Sign-in redirect URIs. - This results in your
Client ID
andClient secret
.
Now, create a new Authorization Server:
-
Go to Security > API > Add Authorization Server.
- Set Audience to
https://<partner_name>.snowflakecomputing.com/
.<partner_name>
is thedatarobot_partner
for the current DataRobot Snowflake instance.
- Set Audience to
-
Go to Scopes > Add Scope.
- Set Name to
session:role:public
(refers to the Snowflake role). - For Check-in, add
Require user consent for this scope
andBlock services from requesting this scope
. - (Optional) Set the
offline_access
scope to require consent.
- Set Name to
-
Go to Access Policies > Add Rule and add the following rules:
- Add Check-in
Client Credentials
. - Add Check-in
Authorization Code
. - Add the client integration (created above) to the
Assigned to clients
field.
- Add Check-in
-
Go to Token and click Create 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
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>'
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>'
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>'
Register an application for Snowflake Resource in Azure AD:
- Go to MS Azure > Azure AD > App registrations.
-
Click New registration.
- Under Name, enter Snowflake Resource.
- Under Supported account types, select Accounts in this organizational directory only.
- Under Redirect URI, select Web and enter
http://localhost/account/snowflake/snowflake_authz_return
. - Click Register.
-
Expose the API.
- Set the Application ID URI to refer to the Snowflake URI (e.g.,
https://hl91180.us-east-2.aws.snowflakecomputing.com
). - Add a scope to reference the existing Snowflake role; the scope is prefixed with the Application ID URI (e.g.,
https://hl91180.us-east-2.aws.snowflakecomputing.com/session:scope:public
).
- Set the Application ID URI to refer to the Snowflake URI (e.g.,
Register an application for Snowflake Client App in Azure AD:
- Go to MS Azure > Azure AD > App registrations.
-
Click New registration.
- Under Name, enter Snowflake Resource.
- Under Supported account types, select Accounts in this organizationl directory only.
- Under Redirect URI, select Web and enter
http://localhost/account/snowflake/snowflake_authz_return
. - Click Register.
-
Go to Client Credentials > New client secret and copy the value. Note that this value will not be available after this step.
- Go to
API Permission > Add Permission > My APIs > Snowflake Resource
and choose the scope created above for Snowflake Resource (session:scope:public
). -
This results in the following:
- Snowflake Client App:
Client ID
andClient secret
-
Issuer URL (
<external_oauth_jws_keys_url>
in Snowflake integration)- Go to Snowflake Client App > Overview > Endpoints > OpenID Connect metadata document, open the document in a browser, and search for
jwks_uri
(e.g.,https://login.microsoftonline.com/6064c47c-80e4-4a2b-82ee-1fc5643b37a2/discovery/v2.0/keys
).
- Go to Snowflake Client App > Overview > Endpoints > OpenID Connect metadata document, open the document in a browser, and search for
-
Entity ID (
<external_oauth_issuer>
in Snowflake integration)- Go to Snowflake Client App > Overview > Endpoints > Federation metadata document, open the document in a browser, and search for
entityID
(e.g.,https://sts.windows.net/6064c47c-80e4-4a2b-82ee-1fc5643b37a2/
).
- Go to Snowflake Client App > Overview > Endpoints > Federation metadata document, open the document in a browser, and search for
- Snowflake Client App:
Related reading
Snowflake setup¶
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, create an integration for the appropriate external IdP:
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/aus15ca55wkdOxplJ5d7
AUDIENCE
:https://hl91180.us-east-2.aws.snowflakecomputing.com/
JWKS_URI
:https://dev-11863425.okta.com/oauth2/aus15ca55wkdOxplJ5d7/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 )
create security integration external_oauth_azure_1
type = external_oauth
enabled = true
external_oauth_type = azure
external_oauth_issuer = 'https://sts.windows.net/6064c47c-80e4-4a2b-82ee-1fc5643b37a2/'
external_oauth_jws_keys_url = 'https://login.microsoftonline.com/6064c47c-80e4-4a2b-82ee-1fc5643b37a2/discovery/v2.0/keys'
external_oauth_audience_list = ('https://hl91180.us-east-2.aws.snowflakecomputing.com/')
external_oauth_token_user_mapping_claim = 'upn'
external_oauth_snowflake_user_mapping_attribute = 'login_name';
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:
-
Follow the instructions for creating a data connection and testing the connection.
-
After clicking Test Connection, select your OAuth provider from the dropdown—either Okta or Azure AD— and fill in the additional required fields.Then, click Save and sign in.
-
Enter your Okta or Azure AD username and password. Click Sign in.
-
To provide consent to the database client, click Allow.
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.
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 Azure AD, this is also known as the applicationID . |
Okta or Azure AD 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 Azure AD, this is also known as the application secret . |
Okta or Azure AD 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 or Azure AD documentation |
Scopes | Contains the name of your Snowflake role. Examples: Parameters for a Snowflake Analyst.
|
Snowflake documentation |
Reach out to your administrator for the appropriate values for these fields.
Caveats¶
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.
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 whitelisted IPs for DataRobot. | See Source IP addresses for whitelisting. If you've already added the whitelisted IPs, check the existing IPs for completeness. |