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

[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]

Related Articles:

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

ref:01652273