Spotfire Server: Configuring Spotfire to connect to a Snowflake Database using Information Services and Azure (OAuth2)

This article describes how to configure Spotfire server to connect to a Snowflake JDBC with OAuth, using two different methods:


Prerequisites

 Please be sure to complete the activities in Entra and Snowflake  as described here. This includes creating a security integration in Snlowflake. 

https://docs.snowflake.com/en/user-guide/oauth-azure
 

Option 1: Spotfire Server is configured with OpenID connect authentication

If your Spotfire Server has been configured with OpenID authentication, it's possible to use the acquired Access Token to authorise use of a Snowflake database using OAuth.

When logging into Spotfire via OpenID a user receives an ID Token (used to log in to Spotfire) and additionally an Access Token. 

The Access Token remains dormant, and is not used to authenticate with the Spotfire server, but it can be passed on to a JDBC data source (Information Services) that supports OAuth. In this article, how to configure this for the Snowflake database is discussed.

• The Snowflake data source must use the same OpenID provider.
• The scope for accessing the Snowflake data source must be known.
   

First, add the  scopes required to access Snowflake via OAuth into the Spotfire OpenID Configuration (Using the UI tool "Configure TIBCO Spotfire Server")

This can be found in the Scopes section of "Expose an API" in the Snowflake OAuth Resource in Entra (see link above). This examples adds:

api://e257e36a-1490-43fe-a7c8-3ae5adf3c9d6/session:role:analyst

This is the fully qualified scope. 

Download a copy of the Snowflake JDBC driver and install into the <installation root>\custom-ext-informationservices directory.

Next, create a data source template as below:

Note: The ISSUER value will be the URL entered when configuring the OpenID Connect provider. It will have the form https://login.microsoftonline.com/<tenant id>/v2.0

<jdbc-type-settings>
    <type-name>snowflake_oauth</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.TokenCredentialsProvider</credentials-provider-class>
    <credentials-provider-init-params>
        <parameter>
            <key>issuer</key>
            <value>https://login.microsoftonline.com/[your-custom-value]/v2.0</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 server to make these changes effective.

Login to Spotfire with OpenID and create a Snowflake data source using this template. The authentication will show "Custom". The access token will be provided by the OpenID login.
 

Option 2: The Spotfire Server does not use OpenID connect authentication

If the Spotfire server hasn't been configured with OpenID connect, then it's possible to configure the Spotfire Server OAuth2 client role to acquire an access token, which then can be passed to the Snowflake data source. 

Important note: Unfortunately Spotfire server does not support the internal OAuth2 service that is provided by Snowflake. This section assumes that an external OAuth2 provider will be used instead.

Please see here for more details. Please upvote this idea to get this feature implemented.

To achieve this, an OAuth2 Client configuration must be added to Spotfire server:

1.) Start the configuration tool (uiconfig) and navigate to the OAuth2 Client section


2.) Enter the details of the external OAuth2 provider (e.g. Microsoft Entra)


 

3.) Add a data source template for Snowflake (OAuth2). 

This configuration is similar to the previous section, except the access token scope (which relates to a Snowflake role) is specified inside the data source template.

<jdbc-type-settings>
    <type-name>snowflake_oauth</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.TokenCredentialsProvider</credentials-provider-class>
    <credentials-provider-init-params>
        <parameter>
            <key>issuer</key>
            <value>https://login.microsoftonline.com/[your-custom-value]/v2.0</value>
        </parameter>
        <parameter>
            <key>scope</key>
            <value>api://e257e36a-1490-43fe-a7c8-3ae5adf3c9d6/session:role:analyst</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>

4.) Save the configuration and restart the Spotfire Server.
 

5.) The data source for Snowflake can now be added to Spotfire. Since the data source template uses OAuth2, the username and password fields should be greyed out. You may additional specify the Snowflake warehouse or schema if required. 

 

Please see here for more information on working with custom data sources.


Please refer to the Spotfire manual for more information on creating Data Sources and working with Information Services.

References and further information:

[1] Using OAuth2 with Information Services
[2] TIBCO Spotfire® JDBC Data Access Connectivity Details
[3] Snowflake JDBC parameter reference guide
[4] Configure Snowflake OAuth for Custom Clients