Introduction
Federated identity simplifies the login process by establishing a trust relationship between services.
In this case, Astrato acts as an identity provider, allowing users to connect to Snowflake using their access token.
With OAuth, Astrato can securely delegate access to Snowflake while verifying the user's identity and sharing relevant attributes.
Instead of logging in directly to Snowflake, users only need to authenticate with Astrato. Astrato generates an access token that contains user attributes and sends it to Snowflake. The token is verified to ensure it originated from a trusted relationship with Astrato, making the authentication process more streamlined and secure.
Pre Setup Requirements
Configuration Requirements:
Snowflake ACCOUNTADMIN role
Astrato Administrator or Owner role
Technical requirements:
Astrato uses email as a user's unique identifier.
Each Astrato user that wants to use identity federation needs to exist on a Snowflake side with the same email address.
Astrato utilizes OAuth session:role-any, and it needs users to have a default Snowflake role defined.
Astrato requires users to have a default warehouse defined in Snowflake.
Identity Federation Setup
To establish an identity federation between Snowflake and Astrato, it is necessary to configure both services accordingly. To do so, as an Astrato Administrator, navigate to Administration ➝ Enterprise Connections ➝ Identity federation, then click New Provider and select Snowflake.
On the Configuration tab, you can find the default settings that must be used to enable external security integration in Snowflake.
To create a Security integration in Snowflake, you will use the values found in Astrato's Configuration tab.
external_oauth_issuer - value from Astrato Issuer above
external_oauth_audience_list - snowflake account url that would respect the tokens
external_oauth_jws_keys_url - value from JSON Web Key Sets above
external_oauth_scope_mapping_attribute - value from Scope mapping attribute above
external_oauth_token_user_mapping_claim - value from User mapping claim above
external_oauth_any_role_mode - Astrato uses session:role-any by default
external_oauth_snowflake_user_mapping_attribute - Astrato requires user's email
(for example in this case we'll use the Login Name as mapping attribute
Log in to Snowflake with a user who has an ACCOUNTADMIN role privilege or any other privileged user to create security integration.
Execute the SQL statement below.
Note: For more security-related options like allowed Snowflake roles and others, see Snowflake docs
CREATE OR REPLACE security integration EXTERNAL_OAUTH_FOR_ASTRATO_ROLE_ANY type = external_oauth enabled = true external_oauth_type = custom external_oauth_issuer = 'https://app.astrato.io/tenant/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' external_oauth_audience_list = ('https://xxxxxx.europe-west2.gcp.snowflakecomputing.com') external_oauth_jws_keys_url='https://app.astrato.io/auth/proxy/tenant/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/fed/jwks.json' external_oauth_scope_mapping_attribute = 'scp' external_oauth_token_user_mapping_claim = 'sub' external_oauth_any_role_mode = 'ENABLE' external_oauth_snowflake_user_mapping_attribute = 'login_name';Once it's created in Snowflake, return to Astrato Federated Identity and navigate to the Setup tab.
Display Name - type in here any name that will let you recognize your configuration
Service account URL - type in here your Snowflake account url like:
https://xxxxxx.europe-west2.gcp.snowflakecomputing.com
Press Create
Once the Security integration is created, you can obtain a testing access token by clicking the "Get Testing Access Token" button.
Verify the token by copying its content and running this code in Snowflake.
select SYSTEM$VERIFY_EXTERNAL_OAUTH_TOKEN('your token')
If the token is valid you should see this result:
For debugging token errors, see documentation: Snowflake External OAuth Errors
After completing the setup process, you can freely utilize your Federated Identity connection in Astrato. Navigate into Data ➝ Data Connection and click New Connection.
From the menu, select Snowflake.
Switch to Oauth and select the integration display name from the dropdown menu.
You can see on a list Federated: Snowflake GK, which means that it comes from your identity federation setup named Snowflake GK.
Select it and click Test connection.
Select a Database to use from the dropdown menu.