Okta SCIM Integration with Snowflake

This guide provides the steps required to configure Provisioning (in Okta) for Snowflake, and includes the following sections:

Features

User and Role Administration is supported for the Snowflake application.

This enables Okta to:

  • Manage the user lifecycle (create, update, and delete) in Snowflake.

  • Manage the role lifecycle (create, update, and delete) in Snowflake.

  • Manage user to role assignments in Snowflake.

The following provisioning features are supported:

Push New Users

New users created through OKTA will also be created in Snowflake.

Push Profile Updates

Updates made to the user’s profile through OKTA will be pushed to Snowflake.

Push User Deactivation

Deactivating the user or disabling the user’s access to Snowflake through OKTA will deactivate the user in Snowflake.

Note

For Snowflake, deactivating a user means setting the isActive attribute for the user to false.

Reactivate Users

User accounts can be reactivated Snowflake.

Sync Password

User password can be pushed from Okta into Snowflake, if required.

Tip

The default setting is to create a random password for users giving the user an attribute setting of has_Password=true. Without a password, users must access Snowflake through Okta SSO. To prevent a password being generated for users, turn this setting off before provisioning users as follows:

  1. Click Edit.

  2. Under Sync Password, uncheck the setting Generate a new random password whenever the user’s Okta password changes.

  3. Save the change.

Enabling this setting in Okta creates a password for the user to access Snowflake. This could result in a pathway for users to access Snowflake without SSO.

Push Groups

Groups and their members can be pushed to Snowflake as roles in Snowflake.

The following user attributes are supported:

Attribute Type

SCIM User Attribute

Snowflake User Attribute

Base attributes

userName

name and login_name

givenName

first_name

familyName

last_name

email

email

Custom attributes

defaultRole

default_role

defaultWarehouse

default_warehouse

Not Supported

  • Okta’s Enhanced Group Push feature.

    Note

    The defaultRole and defaultWarehouse attributes are unmapped as they are optional. If there’s a need, you can map them using Okta profile or expression or set the same value for all users.

  • If you are using AWS PrivateLink to access Snowflake, ensure that you are not entering the PrivateLink URL in the integration settings. Enter the public endpoint (i.e., without .privatelink), and ensure that the network policy allows access from the Okta IP address listed here, otherwise you cannot use this integration.

Known Issues

  • Snowflake account names that include one or more underscores (i.e. _) cannot be used at this time.

  • Existing Snowflake roles cannot be brought under Okta’s management through transfer of ownership. Only new roles can be created through Okta.

    • Existing Snowflake users can be brough under Okta’s management through transfer of ownership. For more information, see Troubleshooting Tips (in this topic).

Prerequisites

  1. Ensure that the network policy in Snowflake allows access from Okta’s IP addresses documented here.

  2. Before you configure provisioning for Snowflake, make sure you have configured the General Settings and any Sign-On Options for the Snowflake app.

Once the above steps are complete, click Next in Okta to take you back to the Provisioning tab.

Configuration Steps

Configure your Okta Provisioning settings for Snowflake by executing the following SQL statements in either the Snowflake web interface or in SnowSQL.

use role accountadmin;
create or replace role okta_provisioner;
grant create user on account to role okta_provisioner;
grant create role on account to role okta_provisioner;
grant role okta_provisioner to role accountadmin;
create or replace security integration okta_provisioning
    type=scim
    scim_client='okta'
    run_as_role='OKTA_PROVISIONER';
select system$generate_scim_access_token('OKTA_PROVISIONING');

Each of the following statements are explained below.

  1. Login to Snowflake as an administrator and execute the following from either the Snowflake worksheet interface or SnowSQL.

  2. Verify the ACCOUNTADMIN role.

    use role accountadmin;
    
  3. Create a user and role in Snowflake and assign that user and role to the OKTA_PROVISIONER role. All users and roles in Snowflake created by Okta will be owned by the scoped down OKTA_PROVISIONER role.

    create or replace role okta_provisioner;
    grant create user on account to role okta_provisioner;
    grant create role on account to role okta_provisioner;
    
  4. Grant the OKTA_PROVISIONER role to the ACCOUNTADMIN role in Snowflake. The ACCOUNTADMIN role is necessary to create an integration.

    grant role okta_provisioner to role accountadmin;
    
  5. Let the ACCOUNTADMIN role create the security integration using the OKTA_PROVISIONER role.

    create or replace security integration okta_provisioning
        type=scim
        scim_client='okta'
        run_as_role='OKTA_PROVISIONER';
    
  6. Create and copy the authorization token to the clipboard. Use this token for each SCIM REST API request and place in the request header. The access token expires after six months and a new access token can be generated with this statement.

    select system$generate_scim_access_token('OKTA_PROVISIONING');
    

    Important

    All users and roles in Snowflake created by Okta will be owned by the scoped down okta_provisioner role.

    If you want to manage existing Snowflake users and roles through Okta, complete the following steps:

    1. Transfer ownership of existing users and roles to okta_provisioner role.

      use role accountadmin;
      grant ownership on user <username> to role okta_provisioner;
      grant ownership on role <rolename> to role okta_provisioner;
      
    2. Ensure login_name property is set for existing users which should already be set if these existing Snowflake users are using Okta SSO.

    3. Be advised that the name for existing users brought under Okta’s management will be updated to match with Okta’s username. Inform your users about this change as they may be using the name to connect to Snowflake from other integration (i.e. Tableau).

  7. In Settings, select Integration from the left hand menu and then check the Enable API Integration box.

  8. For API Token, enter the value generated above from the clipboard. Click Test API Credentials button, and, if successful, save the configuration.

  9. Select To App from the left hand menu.

  10. Select the Provisioning Features you want to enable.

  11. Verify the Attribute Mappings. The defaultRole and defaultWarehouse attributes are unmapped as they are optional. If there’s a need, you can map them using Okta profile or expression or set the same value for all users.

You can now assign users to the Snowflake application (if needed) and finish the application setup.

Note

Okta supports an attribute called snowflakeUserName which maps to the name field of the Snowflake user.

If you want the name and login_name fields for the Snowflake user to have different values, follow this procedure.

  1. Contact Snowflake support to enable separate mapping for your account.

  2. In Okta, access the Snowflake application and navigate to Provisioning > Attribute Mappings > Edit Mappings.

  3. Search for the attribute snowflakeUserName.

  4. If the attribute is not found, the Snowflake application was created prior to this attribute being available. Recreate the Snowflake application with the mappings shown below or add the attribute manually as follows:

    • Click Add Attribute.

    • Set the following values for each of the listed fields in the table.

    Field

    Value

    Data type

    string

    Display name

    Snowflake Username

    Variable name

    snowflakeUserName

    External name

    snowflakeUserName

    External namespace

    urn:ietf:params:scim:schemas:extension:enterprise:2.0:User

    Description

    Maps to the name field of the user in Snowflake.

    Scope

    User personal

  5. Click Save.

Troubleshooting Tips

  • Transferring Ownership. If the user update fails, check the ownership of the user in Snowflake. If it’s not owned by the okta_provisioner role (or the role set in the run_as_role parameter when creating the security integration in Snowflake), then the update will fail. Transfer the ownership by running the following SQL statement in Snowflake and try again.

    grant ownership on user <username> to role OKTA_PROVISIONER;
    
  • Ensure login_name property is set for existing users which should already be set if these existing Snowflake users are using Okta SSO.

  • To verify that Okta is sending updates to Snowflake, check the log events in Okta for the Snowflake application and the SCIM audit logs in Snowflake to ensure Snowflake is receiving updates from Okta. Use the following to query the Snowflake SCIM audit logs.

    use role accountadmin;
    use database demo_db;
    use schema information_schema;
    select * from table(rest_event_history('scim'));
    select *
        from table(rest_event_history(
            'scim',
            dateadd('minutes',-5,current_timestamp()),
            current_timestamp(),
            200))
        order by event_timestamp;
    
  • It is possible that an authentication error may occur during the provisioning process. One possible error message is as follows:

    Error authenticating: Forbidden. Errors reported by remote server: Invalid JSON: Unexpected character ('<' (code 60)): expected a valid value (number, String, array, object, 'true', 'false' or 'null') at [Source: java.io.StringReader@4c76ba04; line: 1, column: 2]
    

    If this error message or other authentication error messages occur, try this troubleshooting procedure:

    1. In Okta, remove the current Snowflake application and create a new Snowflake application.

    2. In Snowflake, create a new SCIM security integration and generate a new access token.

    3. Copy the new token by clicking Copy.

    4. In Okta, paste and verify the new access token as described in how to configure Okta as a SCIM identity provider.

    5. Provision users and roles from Okta to Snowflake using the new Snowflake application in Okta.