Spotfire Server: Using the OAuth2 Client Credentials flow for a Snowflake Data Source

Introduction
 

A connection to a Snowflake data source via OAuth2 can be configured to use the client credentials flow instead of the authorisation code flow.
 
This allows machine to machine communication, where the Spotfire server acquires an access token without requiring any user intervention. 

This can be useful in cases where a dashboard needs to have an associated Scheduled Update or be used with Automation Services.
 

Procedure

Please see this related article

As with using the Authorisation Code Flow (where user intervention is needed) a Snowflake Client app needs to be registered in Azure. 

Please see the following article on how to register a client application in Entra so it can used with Snowflake. 

The difference when using the Client Credentials flow is that only the token endpoint is called on to acquire an access token. The client ID and secret are used as credentials. This then allows non interactive workflows.

With this method it is not necessary  to register an OAuth2 Client in Spotfire. The credentials are sourced from the data source connection in the Information Designer. 

1. Register an OAuth2 resource in Entra. 

This must be done by registering a new Enterprise Application in Entra. 

2. Add an app role to the resource

Under Enterprise Apps -> (Snowflake App) -> Single Sign-On -> Go to Application -> App Roles add a new app role. This will set the correct scope inside the access token. The example below sets session:role:analyst. This must be set as appropriate for your Snowflake configuration.

Make a note of the Object ID of the Snowflake Client application. This can be found in Entra when listing all Enterprise Applications. 
 

3. Create a data source template in Spotfire

The data source template for Snowflake uses the com.spotfire.ws.im.ds.sql.ClientCredentialsCredentialsProvider class to initiate a client credentials flow. 

An example template is as below

<?xml version="1.0"?>
<jdbc-type-settings>
    <type-name>snowflake_oauth_clientcreds</type-name>
    <driver>net.snowflake.client.jdbc.SnowflakeDriver</driver>
    <connection-url-pattern>jdbc:snowflake://<account_identifier>.snowflakecomputing.com</connection-url-pattern>
    <connection-properties>
        <connection-property>
            <key>authenticator</key>
            <value>oauth</value>
        </connection-property>
    </connection-properties>
    <credentials-provider-class>com.spotfire.ws.im.ds.sql.ClientCredentialsCredentialsProvider</credentials-provider-class>
    <credentials-provider-init-params>
        <parameter>
            <key>metadata_uri</key>
            <value>https://login.microsoftonline.com/611f0ac3-b38c-4e56-a62e-5dbdcfed9071/v2.0/.well-known/openid-configuration</value>
        </parameter>
        <parameter>
            <key>access_token_property_name</key>
            <value>token</value>
        </parameter>
    </credentials-provider-init-params>
    <supports-catalogs>true</supports-catalogs>
    <supports-procedures>false</supports-procedures>
    <supports-schemas>true</supports-schemas>
    <fetch-size>10000</fetch-size>
    <batch-size>100</batch-size>
    <use-ansii-style-outer-join>true</use-ansii-style-outer-join>
</jdbc-type-settings>

 
Restart the Spotfire Service once this is added.
 

4. Create a new Data Source in the Information Designer

Create a new data source based off the template created. Note that the username comes from the Object ID of the Snowflake Client App which was noted earlier. The password data will not be used, enter any dummy value. 
As explained later, this username is also seen as the sub claim in the access token.


 

5. Create a Security Integration in Snowflake

A security integration needs to be created in Snowflake. The following requirements must be adhered to.

[a] The OAuth2 access token has a claim called sub and this is what identifies the user seen by snowflake. The sub claim is the same user name as the data source uses in Spotfire, which is also the Object ID from Entra

As a result the security integration must set EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM to be sub. This tells snowflake to use sub from the access token to map a user.

[b] A Snowflake user must be created or select for this session.

The LOGIN_NAME must be set to the same value as the Data Source username (which is also the value of sub in the access token)

Once these steps have been completed it should be then possible to access the Snowflake data.