Unable to save TIBCO Spotfire Server bootstrap file with "unable to find valid certification path to requested target" error

Unable to save TIBCO Spotfire Server bootstrap file with "unable to find valid certification path to requested target" error

The following error is seen while trying to save the TIBCO Spotfire Server bootstrap file while using the TIBCO DataDirect driver:

"Could not connect to the Spotfire Server database
java.sql.SQLNonTransientConnectionException: [tibcosoftwareinc][SQLServer JDBC Driver]SSL handshake failed: sun.security.validator.ValidatorException: 
PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target"

  There can be of two reasons for this issue to happen when you have SSL enabled:

1. If you intend to use the TIBCO DataDirect drivers, ensure that you have imported the SQLServer Certificate into the default java keystore ([Spotfire Server 7.x installation folder]\jdk\jre\lib\security\cacerts) on Spotfire server. (if the SQLServer Certificate is not signed by a trusted external certificate authority)

2. Ensure that you have added "ValidateServerCertificate=true" at the end of connection URL. For example:

jdbc:tibcosoftwareinc:sqlserver://THESERVER:1433;databaseName=THEDB;EncryptionMethod=requestSSL;ValidateServerCertificate=true

  Please refer to the following information on when to set EncryptionMethod and ValidateServerCertificate:

EncryptionMethod

Purpose:
Determines whether data is encrypted and decrypted when it iss transmitted over the network between the driver and database server

Valid values:
noEncryption | SSL | requestSSL |loginSSL

Behavior:
If set to noEncryption, data is encrypted or decrypted
If set to SSL, data is encrypted using SSL. IF the database server does not support SSL the connection fails and the driver throws an exception
If set to requestSSL, the login request and data is encrypted using SSL. If the database server does not support SSL, the driver establishes an unencrypted connection
If set to loginSSL, the login request is encrypted using SSL. Data is encrypted using if the database servers cornfigured to requires SSL. If the database server does not require SSL data is not encrypted and only the login request is encrypted.

Notes:

• Connection hangs can occur when the driver is configured for SSL and the database server does not support SSL. You may want to set a login timeout using the LoginTimeout properly to avoid problems when connecting to a server that does not support SSL.
• If SSL is enabled, the driver communicates with database protocol packets that are set by the server's default packet size. Any value set by the PacketSize property is ignored.
• If SSL is enabled, the following properties also apply:
  • HostNameInCertificate
  • TrustStore
  • TrustStorePassword
  • Validate Server Certificate

Default:
noEncryption

Data Type
String
 

ValidateServerCertificate

Purpose:
Determines whether the driver validates the certificate that is sent by the database server when SSL encryption is enabled (EncryptionMethod=SSl). When using SSL server authentication, any certificate that is sent by the server must be issued by a trusted Certificate Authority (CA).Allowing the driver to trust any certificate that is returned from the server even if the issuer is not a trusted CA is useful in test environments because it eliminates the need to specify truststore information on each client in the test environment.

Valid value:
true|false

Behavior:
If set to true, the driver validates the certificate that is sent by the database server.Any certificate from the server must be issues by a trusted CA in the truststore file. If the HostNameInCertificate property is specified,the driver also validates the certificate using a hostname. The HostNameInCertificate property is optional and provides addition security against man-in-the-middle attacks by ensuring that the server the driver is connecting to is the server that was requested.

If set to false, the driver does not validate the certificate that is sent by the database server.The driver ignores any truststore information that is specified by the TrustStore and TrustStorePassword properties or Java system properties.

Notes:
TrustStore information is specified using the TrustStore and TrustStorePassword properties or by using Java system properties.

Default:
true

Data Type:
boolean