Snowflake¶

• A Snowflake account.
• External OAuth configured in Snowflake for Okta.

• A Snowflake account.
• External OAuth configured in Snowflake for Microsoft Azure AD.

Create a new App Integration in Okta:

1. Go to Applications > Applications.
2. Click Create App Integration.
3. For the Sign-in method, select OIDC - OpenID Connect.
4. For the Application type, select Web Application.
5. Click Next.

6. Make sure the following options are selected:

   • Client Credentials
   • Authorization Code
   • Refresh Token
   • Require consent

7. Under LOGIN, add http://localhost/account/snowflake/snowflake_authz_return to the Sign-in redirect URIs.

8. This results in your Client ID and Client secret.

Now, create a new Authorization Server:

1. Go to Security > API > Add Authorization Server.

   • Set Audience to https://<partner_name>.snowflakecomputing.com/. <partner_name> is the datarobot_partner for the current DataRobot Snowflake instance.

2. 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 and Block services from requesting this scope.
   • (Optional) Set the offline_access scope to require consent.

3. 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.

4. Go to Token and click Create token.

5. 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:

1. Go to MS Azure > Azure AD > App registrations.

2. 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://app.datarobot.com/account/snowflake/snowflake_authz_return.
   • Click Register.

3. In the Overview section, copy the ClientID from the Application (client) ID field which will be <OAUTH_CLIENT_ID> value

4. Click on Certificates & secrets and then New client secret.
5. Add a description of the secret.
6. Click Add and copy the secret. Note that this value will not be available after this step. This will be <OAUTH_CLIENT_SECRET> value

7. Expose the API.

   • Click on 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 value
   • Click on Add a scope to add a scope representing the Snowflake role
   • Enter the scope name as session:scope:public

Register an application for Snowflake Client App in Azure AD:

1. Go to MS Azure > Azure AD > App registrations.

2. Click New registration.

   • Under Name, enter Snowflake OAuth Client.
   • Under Supported account types, select Accounts in this organizationl directory only.
   • Under Redirect URI, select Web and enter https://app.datarobot.com/account/snowflake/snowflake_authz_return.
   • Click Register.

3. Go to API Permission > Add Permission > My APIs > Snowflake Resource and choose the scope created above for Snowflake Resource (session:scope:public).

4. For programmatic clients that will request an Access Token on behalf of a user, configure Delegated permissions for Applications as follows.

   • Click on API Permissions.
   • Click on Add Permission.
   • Click on My APIs.
   • Click on the Snowflake OAuth Resource that you created in Step 1: Configure the OAuth Resource in Azure AD.
   • Click on the Delegated Permissions box.
   • Check on the Permission related to the Scopes created in step 3 session:scope:public
   • Click Add Permissions.

5. Collect additional information for Snowflake integration

   • Click on App Registrations
   • Click on the Snowflake OAuth Resource
   • Click on Endpoints in the Overview interface
   • Copy the OAuth 2.0 token endpoint (v2) which will be <AZURE_AD_OAUTH_TOKEN_ENDPOINT> value
   • Copy OpenID Connect metadata url from the endpoint overview and paste it on a new window
   • Locate the "jwks_uri" parameter and copy its value which will be <AZURE_AD_JWS_KEY_ENDPOINT> value (e.g., https://login.microsoftonline.com/6064c47c-80e4-4a2b-82ee-1fc5643b37a2/discovery/v2.0/keys)
   • Copy Federation metadata document, open the URL in a new window. Locate the "entityID" which will be our <AZURE_AD_ISSUER> value (e.g., https://sts.windows.net/6064c47c-80e4-4a2b-82ee-1fc5643b37a2/)

6. This results in the following:

   • Client ID and Client secret copied from Snowflake OAuth Resource
   • Issuer URL copied from step 5 <AZURE_AD_ISSUER> value
   • <AZURE_AD_JWS_KEY_ENDPOINT> value from Register an application for Snowflake Resource in Azure AD step 5
   • <SNOWFLAKE_APPLICATION_ID_URI> from Register an application for Snowflake Resource in Azure AD step 3

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 = ('api://8aa2572f-c9e6-4e91-9eb1-dcd84c856dd2')
   external_oauth_token_user_mapping_claim = 'upn'
   external_oauth_any_role_mode = 'ENABLE'
   external_oauth_snowflake_user_mapping_attribute = 'login_name';

Grant access on the integration to the public role:

grant USE_ANY_ROLE on integration external_oauth_azure_1 to PUBLIC;

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 Azure AD. 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>'