Overview

In the updated version of the Microsoft JDBC Driver for SQL Server, the default values for the following connection properties have changed to:

encrypt=true
trustServerCertificate=false

With the new default settings, TLS encryption is enabled, and a valid, not self-signed, server certificate is required. If you have configured parts of your Spotfire environment using the included driver, and you have not specified values for the above properties (thus falling back to the driver default values), you might see encryption-related errors.

What do I need to do?

If you use the included Microsoft JDBC Driver for SQL Server, with the default settings, for any of the functionality listed above, you must make sure that you can still connect to the Microsoft SQL Server instance with the updated driver. If the Microsoft SQL Server instance is already configured for encryption with a valid server certificate, the connection should work as expected. If the Microsoft SQL Server instance is not configured with a valid server certificate, you must either configure the instance, or change the connection URL for the affected databases.

Configuring your Microsoft SQL Server

For any Microsoft SQL Server instances in your environment, in particular in production environments, you should generally use valid, not self-signed, server certificates. This enables you to use encryption, with validation of the server certificates, in your connections. For instructions, see the official documentation from Microsoft.

Changing the connection URL

Warning: Before you make any changes to connection URLs, make sure you understand the security implications of turning on the setting trustServerCertificate in connections with the Microsoft JDBC Driver for SQL Server.

If you choose not to change the configuration of your Microsoft SQL Server, another option is to update the connection URL/connection string to the database in Spotfire. If you add the setting trustServerCertificate=true to the connection URL, the connection will be encrypted, but self-signed server certificates will be accepted.

Sample connection URL with the setting trustServerCertificate=true added:

jdbc:sqlserver://localhost:1433;DatabaseName=dbname;trustServerCertificate=true

Spotfire database connection URL

The connection URL for the Spotfire database is stored in the bootstrap file, bootstrap.xml. To change settings in the bootstrap file, use the config tool, or the CLI command update-bootstrap.

You can also change the Spotfire database connection URL when you use the upgrade tool. If the connection to the Spotfire database fails when you start the upgrade, you can modify the Spotfire database connection URL before proceeding. This option is not available if you run the upgrade tool silently.

Action log database connection URL

To modify the action log database connection URL, you can use the configuration tool, or the CLI command config-action-log-database-logger.

Note: If the connection to the action log database fails, you will not be able to start Spotfire Server after the upgrade. You can change the connection URL with the configuration tool after the upgrade to resolve this issue.

Default join database connection URL

If you have configured a default join database, you can review it with the CLI command show-join-database and update it with create-join-db.

Information Services data sources

For more information about updating the connection URL of your Information Services data sources, see the section Data sources in Information Services.

Data sources in Information Services

You might have many Information Services data sources that are affected by the change in the driver. The Spotfire upgrade tool can help you identify, and, optionally, update the data sources with a change to the connection URL.

Which sources do I have to update?

Data sources that use the Microsoft JDBC Driver for SQL Server, and for which you have not configured the connection properties encrypt and trustServerCertificate in the Connection URL in Information Designer will be affected. Specifically, data sources for which the following is true likely need to be updated:

• The data source is based on a data source template using the Microsoft JDBC Driver for SQL Server:

  <driver>com.microsoft.sqlserver.jdbc.sqlserverdriver</driver>

  Note: The default data source template 'SQL Server (2005 or newer)’ uses the driver.
• In the Connection URL field in Information Designer, the properties encrypt or trustServerCertificate are not configured.

If you do not update the data sources, Spotfire will fall back to the driver defaults for these properties when connecting to the external database. If the database is not properly set up for SSL/TLS and it does not have a valid server certificate, the connection will not work.

How do I identify and update affected data sources?

Tip: You can also perform the scan described below after upgrading Spotfire, with the CLI command sqlserver-datasource-update-script.

To help you identify and make changes to any affected data sources, the Spotfire upgrade tool scans all your Microsoft SQL Server data sources and identifies the ones that are not configured with either of the following settings:

 encrypt=true
 trustServerCertificate=true

The result of the scan is a script file, SQLServerDatasourceUpgradeScript.txt, that you can run to add the setting trustServerCertificate=true to your data sources.

Tip: Additionally, if you select the check box Probe Microsoft SQL Server data sources in the upgrade tool, you can have the upgrade tool run a test towards your data sources during the upgrade. Any data sources that work, or that fail due to reasons unrelated to encryption, are excluded from the SQLServerDatasourceUpgradeScript.txt script file. After the upgrade tool is finished, data sources that fail for reasons unrelated to encryption are listed in the file SQLServerDatasourceUpgradeProbing.txt.

Be aware that if you have many data sources, this process can slow down the upgrade significantly.

Important: If you enable this setting, before you start the upgrade, make sure that for data sources that should use encryption, the corresponding server certificate is available on the Spotfire Server.

Important: If you have data sources that use Kerberos for authentication, it is recommended to not enable this setting. Data sources with Kerberos authentication, for which you do not have the server certificate on the Spotfire Server, might fail during the test with an error reported in the SQLServerDatasourceUpgradeProbing.txt file.

Updating data sources with the script file

1. When you run the Spotfire Server upgrade tool, if any data sources that require updating are identified, the file SQLServerDatasourceUpgradeScript.txt is created in the directory where you started the upgrade tool.
2. Review the contents of the SQLServerDatasourceUpgradeScript.txt file, to see which data sources the script will update when you run it.
3. To update all the data sources in the script file, open a command line as an administrator, and run the following command:
   Windows:

   <installation dir>/tomcat/spotfire-bin/config.bat run --fail-on-undefined-variable -Vtoolpassword=<config tool password> -VlibraryAdmin=<library admin user> -Vvalidate=true SQLServerDatasourceUpgradeScript.txt

   Linux:

   <installation dir>/tomcat/spotfire-bin/config.sh run --fail-on-undefined-variable -Vtoolpassword=<config tool password> -VlibraryAdmin=<library admin user> -Vvalidate=true SQLServerDatasourceUpgradeScript.txt

Note: The user specified by VlibraryAdmin must be in the default domain.