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

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

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.

• Step 2 will update the data sources in the Spotfire without the";trustServerCertificate=true;" parameter.

• Step 3 will probe and generate the new script "SQLServerDatasourceUpgradeScript.txt" to update the connection URL with the";trustServerCertificate=true;" parameter.