Spotfire Server: Configuring Spotfire to connect to a Snowflake Database using Information Services and OAuth

Keywords: Snowflake OAuth JDBC Information Services

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

The Spotfire Server has been configured with OpenID connect authentication

If your TIBCO 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 the OpenID configuration.

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

Next, create a data source template as below:

Note: ISSUER will be the URI entered when configuring the OpenID Connect provider. Enter the URI up to but not including /.well-known/openid-configuration or /.well-known/oauth-authorization-server. 

<jdbc-type-settings>
<type-name>snowflake_oauth2</type-name>
<driver>net.snowflake.client.jdbc.SnowflakeDriver</driver>
<connection-url-pattern>jdbc:snowflake://&lt;account_identifier&gt;.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>ISSUER</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.

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 as an OAuth2 client to acquire an access token which 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.

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).
With this configuration the role is specified inside the data source template so differs from the earlier example.

<jdbc-type-settings>
    <type-name>snowflake_oauth</type-name>
    <driver>net.snowflake.client.jdbc.SnowflakeDriver</driver>
    <connection-url-pattern>jdbc:snowflake://&lt;account_identifier&gt;.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/70148eb6-9725-4c6a-a4fc-227c2ea8a644/v2.0</value>
   </parameter>
   <parameter>
        <key>scope</key>
        <value>8fd536b3-4e2a-428e-984f-8306abae37c0/SESSION:ROLE-ANY</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.

Setting up a data source for Snowflake in the Information Designer

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