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
randomness