Switching Data Sources to the Microsoft JDBC driver for SQL Server post upgrading to Spotfire 14.0.x LTS version

Switching Data Sources to the Microsoft JDBC driver for SQL Server post upgrading to Spotfire 14.0.x LTS version

book

Article ID: KB0070591

calendar_today

Updated On:

Products Versions
Spotfire Server 14.0.0, 14.0.1, 14.0.1

Description

Switching to the Microsoft JDBC driver for SQL Server after upgrading to Spotfire 14.0.x LTS version when the database is not encrypted with certificates.

Here is the old SQL Server (Data Direct) data source template

 tibcosoftwareinc.jdbc.sqlserver.SQLServerDriver 
New Microsoft SQL Server (2005 or newer) data source template.
 com.microsoft.sqlserver.jdbc.SQLServerDriver

Issue/Introduction

This article outlines the steps to switch to the Microsoft JDBC driver for SQL Server after upgrading to Spotfire 14.0.x LTS version if the database is not encrypted with certificates.

Resolution

Please follow the below recommendations and steps to accomplish this:

Note: Connection to the SQL server database will require encryption, and if the database is not encrypted then you have to manually add the connection property " trustServerCertificate=true" in the sqlserver2005 data source template and save. Restart the Spotfire server to take effect. You can read the section  Switching to the Microsoft JDBC driver for SQL Server within the documentation.
  1. Edit SQL Server (2005 or newer) Data Source Template from Spotfire Configuration file: 
  2. Open Spotfire server Configuration Tool -->Configuration -->Data Source Templates -->SQL Server (2005 or newer)
  3. Manually add the connection property "trustServerCertificate=true" in the sqlserver2005 data source template and save
     jdbc:sqlserver://<host>:<port>;DatabaseName=<database>;trustServerCertificate=true
  4. Restart the Spotfire server to take effect.
Note: This "com.microsoft.sqlserver.jdbc.SQLServerDriver"  driver is included within Spotfire along with a data source template (SQL Server (2005 or newer)) for Information Services.

Once you make these changes please proceed with updating the data source templates by executing the commands in the below order.
  1.  config create-datadirect-datasources-update-script --source-type <Template type name> --target-type <Target template type name> --validate false
    Example: 
     config create-datadirect-datasources-update-script --source-type sqlserver_datadirect --target-type sqlserver2005 --validate false
  2.  config run --fail-on-undefined-variable -Vtoolpassword=<config_tool_password> -VlibraryAdmin=<library_admin_user> -Vvalidate=true/false UpdateScript_sqlserver_datadirect_sqlserver2005.txt
    Example: 
     config run --fail-on-undefined-variable -Vtoolpassword=password -VlibraryAdmin=admin -Vvalidate=true UpdateScript_sqlserver_datadirect_sqlserver2005.txt
  3.  config sqlserver-datasource-update-script -p true/false
    Example: 
     config sqlserver-datasource-update-script -p true
  4.  config run --fail-on-undefined-variable -Vtoolpassword=<config_tool_password> -VlibraryAdmin=<library_admin_user> -Vvalidate=true/false SQLServerDatasourceUpgradeScript.txt
    Example: 
     config run --fail-on-undefined-variable -Vtoolpassword=password -VlibraryAdmin=admin -Vvalidate=true SQLServerDatasourceUpgradeScript.txt
  • In Step 1, you will have to pass the additional argument "--validation false" which will turn off the data source validation and allow the command to update the data source template when the Step 2 command is executed. PFA screenshot.
  • User-added image
  • Step 2 will update the data sources in the Spotfire without the";trustServerCertificate=true;" parameter. PFA screenshot.
  • User-added image
  • Step 3 will probe and generate the new script "SQLServerDatasourceUpgradeScript.txt" to update the connection URL with the";trustServerCertificate=true;" parameter. PFA screenshot.
  • User-added image
  • Step 4 will update all the data sources using the sqlserver2005 template connection URL with the";trustServerCertificate=true;" parameter in the Spotfire. PFA screenshot
  • User-added image
  • Validate the data source from the Information Designer.
  • User-added image
PFA UpdateReport_sqlserver_datadirect_sqlserver2005.html, UpdateReport_sqlserver_datadirect_sqlserver2005.txt, and SQLServerDatasourceUpgradeScript.txt files and screenshots in the zipped 14.0.1 folder.

The below files will be generated at the path: <installation directory>tomcat\spotfire-bin
  • UpdateReport_sqlserver_datadirect_sqlserver2005.html
  • UpdateReport_sqlserver_datadirect_sqlserver2005.txt
  • SQLServerDatasourceUpgradeScript.txt

Additional Information

Doc: Switching to the Microsoft JDBC driver for SQL Server Doc: Migrating from TIBCO-branded DataDirect JDBC drivers Doc: Updating Information Services data sources (create-datadirect-datasources-update-script) Doc: A scan of all Information Services data sources (sqlserver-datasource-update-script)

Attachments

Switching Data Sources to the Microsoft JDBC driver for SQL Server post upgrading to Spotfire 14.0.x LTS version get_app