How to install TIBCO JasperReports® Server to a SSL Enabled SQL Server database

Issue:

When trying to run jsinstall to install TIBCO JasperReports® Server to an SSL Enabled SQL Server database, the following error was encountered :
Table of Contents 
 
[advanced-sql] Failed to connect*:
 [TibcoSoftware][SQLServer JDBC Driver]The SQL Server login requires an SSL connection.*
  [echo] Connection failed:
  [echo]  SQLState: 08001
  [echo]  Vendor specific error code: 0
  [echo]  Message: [[TibcoSoftware][SQLServer JDBC Driver]The SQL Server login requires an SSL connection.]

What are the steps involved in a successful installation ?

Solution:

  1. Download and install the native sql server jdbc driver and place the jar file in the following directory:

    ../buildomatic/conf_source/db/sqlserver/jdbc

    Note1: We used sqljdbc42.jar (compatible with SQL Server 2016)

    Note2: Move the TIsqlserver-5.14.1.jar (TIBCO provided sql server jdbc driver) to a backup location

  2. Modify the buildomatic/default_master.properties file as follows:

    # 1) Setup Standard SQLServer JDBC Driver
#
# Uncomment and modify the value to native
jdbcDriverMaker=native
#
# Uncomment and modify the value in order to change the default
# Driver will be found here: <path>/buildomatic/conf_source/db/sqlserver/native.jdbc
#
maven.jdbc.groupId=sqlserver
maven.jdbc.artifactId=sqljdbc
maven.jdbc.version=4.2

    Note: jsinstall will look for sqljdbc-4.2.jar which is non-existent so we renamed the jar file from sqljdbc42.jar to sqljdbc-4.2.jar to workaround this error.

  3. Open the file: ../buildomatic/conf_source/db/sqlserver/db.template.properties

    1. Comment out the existing 4 lines of connection string

      admin.jdbcUrl=jdbc:tibcosoftware:sqlserver://${dbHostOrInstance};sendTimestampEscapeAsString=false
js.jdbcUrl=jdbc:tibcosoftware:sqlserver://${dbHostOrInstance};databaseName=${js.dbName};sendTimestampEscapeAsString=false
sugarcrm.jdbcUrl=jdbc:tibcosoftware:sqlserver://${dbHostOrInstance};databaseName=${sugarcrm.dbName};sendTimestampEscapeAsString=false
foodmart.jdbcUrl=jdbc:tibcosoftware:sqlserver://${dbHostOrInstance};databaseName=${foodmart.dbName};sendTimestampEscapeAsString=false

    2. Copy the 4 lines and modify so that they include the connection string for native sql server together with the SSL connection parameters according to example below:

      From Microsoft, the connection string format is (see reference below):

      String connectionUrl =   
    "jdbc:sqlserver://localhost:1433;" +  
     "databaseName=AdventureWorks;integratedSecurity=true;" +  
     "encrypt=true; trustServerCertificate=false;" +  
     "trustStore=storeName;trustStorePassword=storePassword";

      After modification, it looks like this :

      admin.jdbcUrl   =jdbc:sqlserver://${dbHostOrInstance};
                 sendTimestampEscapeAsString=false;encrypt=true;
                 trustServerCertificate=false;
                 trustStore=storeName;
                 trustStorePassword=storePassword
 
js.jdbcUrl      =jdbc:sqlserver://${dbHostOrInstance};
                 databaseName=${js.dbName};
                 sendTimestampEscapeAsString=false;
                 encrypt=true;
                 trustServerCertificate=false;
                 trustStore=storeName;
                 trustStorePassword=storePassword
 
sugarcrm.jdbcUrl=jdbc:sqlserver://${dbHostOrInstance};
                 databaseName=${sugarcrm.dbName};
                 sendTimestampEscapeAsString=false;
                 encrypt=true;
                 trustServerCertificate=false;
                 trustStore=storeName;
                 trustStorePassword=storePassword
 
foodmart.jdbcUrl=jdbc:sqlserver://${dbHostOrInstance};
                 databaseName=${foodmart.dbName};
                 sendTimestampEscapeAsString=false;
                 encrypt=true;
                 trustServerCertificate=false;
                 trustStore=storeName;
                 trustStorePassword=storePassword

      Note1: 'trustStore' must contain the full path including the file with extension.
      Note2: A keystore can also be used as a truststore which means the values for 'trustStore' and 'trustStorePassword' parameters is that of the keystore.

  4. Rerun ./jsinstall.sh [minimal]

https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-2016

ref:01652273

Feedback