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

Keywords: Snowflake OAuth JDBC Information Services

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.

Restart the Spotfire server to make these changes effective.

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>

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

Restart the Spotfire server once more. Then it should be possible to create a new Information Services data source for Snowflake.

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