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. PFA screenshot.
- 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. PFA screenshot
- Validate the data source from the Information Designer.
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