This article goes through the steps to switch 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
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.
- Edit SQL Server (2005 or newer) Data Source Template from Spotfire Configuration file:
- Open Spotfire server Configuration Tool -->Configuration -->Data Source Templates -->SQL Server (2005 or newer)
- Manually add the connection property "trustServerCertificate=true" in the sqlserver2005 data source template and save
jdbc:sqlserver://<host>:<port>;DatabaseName=<database>;trustServerCertificate=true
- 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.
-
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
-
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
-
config sqlserver-datasource-update-script -p true/false
Example:config sqlserver-datasource-update-script -p true
-
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. PFA screenshot.
- Step 3 will probe and generate the new script "SQLServerDatasourceUpgradeScript.txt" to update the connection URL with the";trustServerCertificate=true;" parameter. PFA screenshot.
- Step 4 will update all the data sources using the sqlserver2005 template connection URL with the";trustServerCertificate=true;" parameter in the Spotfire:
- Validate the data source from the Information Designer.
The following files are attached to this article as a zip file: 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
Documentation links
Migrating from TIBCO-branded DataDirect JDBC drivers
Updating Information Services data sources (create-datadirect-datasources-update-script)
A scan of all Information Services data sources (sqlserver-datasource-update-script)
Comments
0 comments
Article is closed for comments.