KB485556: How to connect to Exasol using TLS certificate through MicroStrategy m2021 Update 6

Quality Engineer, Principal • MicroStrategy

How to connect to Exasol using TLS certificate through MicroStrategy m2021 Update 6

Pre-requisites:

Strategy Intelligence has been upgraded to 2021 Update 6, whose Exasol drivers have been upgraded to 7.1.7
Exasol db versions 6.2.15+, 7.0.10+ and 7.1.x

Symptom
After Exasol driver upgraded to 7.1.7 along with MSTR m2021 Update 6, TLS is the default cryptographic protocol for all Exasol connections.
Old connection used to work in driver before 7.1.0 will not work in the new driver.
When connecting to your database, you will get an error message similar to this:


java.io.IOException: TLS connection to host (192.168.56.101) failed: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. If you trust the server, connect to it using the fingerprint: 2747B64E34414C293091FF37F428CC8D795B64B7159E50EDD7EA507B58D4FAEA.

Cause
All connections created using Exasol-provided 7.1 drivers will try to establish TLS connections to Exasol per default.
As part of the TLS handshake, the drivers require SSL/TLS certificate used by Exasol to be verified. This is the standard practice that increases the security of connections by preventing man-in-the-middle attacks.
Note, as Exasol driver has higher memory usage for encrypted communication, it might have small impact on performance in some scenarios.
Action
There are five possible methods to re-establish connection to your database using the latest driver.

Full, TLS-standard verification (default)
Fingerprint-based verification
No verification
Legacy encryption
Turn off encryption

Option 3 and Option 5 are not recommended.

1. Standard Verification

1.1 The certificate and the client's corresponding CA certificates are properly configured
This is the standard certificate verification process used by default. The certificate is verified to be valid for the Exasol node (host or IP address check) from which it was received and that the certificate is properly signed by a recognized CA.
If the certificate and the client's corresponding CA certificates are properly configured, then no additional configuration is necessary.
This method provides the highest level of security.
The following table provides the examples for standard verification:

Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N
Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N
Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N

1.2 Custom Self-signed Certificate
A custom (i.e. non-default) certificate to be used by Exasol can be uploaded via EXAoperation.
Here is a example to create custom self-signed certificate:


  sudo openssl genrsa -des3 -out rootCA_key.pem 2048
  sudo openssl req -x509 -new -nodes -key rootCA_key.pem -sha512 -days 365 -out rootCA.pem
  sudo vi v3.ext
 
authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names
[alt_names]
DNS.1 = *.Strategy.com
DNS.2 = *.labs.Strategy.com
DNS.3 = *.internal.Strategy.com
 
  sudo openssl req -new -nodes -out server.csr -newkey rsa:2048 -keyout server_key.pem

 
  sudo openssl x509 -req -in server.csr -CA rootCA.pem -CAkey rootCA_key.pem -CAcreateserial -out server.pem -days 365 -sha512 -extfile v3.ext
  cat server.pem > cert_chain.pem; cat rootCA.pem >> cert_chain.pem

Upload the certificate chain and private key via EXAoperation

Edit /etc/hosts of IServer, add exasol db information into hosts, the hostname should match the wild card in extfile

1.2.1 For ODBC:

Upload certificate file to Iserver and specify it in DBConnection propertiy SSLCertificate. The connection string is similar to:


DRIVER={EXASolution Driver};EXAHOST:8563=exasol.labs.Strategy.com;SSLCertificate=/opt/cert/withext/server.pem;

1.2.2 For JDBC:

Add certificate into keystore firstly:


keytool -importcert -file server.pem -keystore keystore.jks -alias "ExasolKS"

Upload the keystore file into Iserver
Specify keystore, keystorepassword and keystoretype in JDBC connection string. The connection string is similar to:


JDBC;DRIVER={com.exasol.jdbc.EXADriver};URL={jdbc:exa:exasol.labs.Strategy.com:8563;keystore=/opt/keystore.jks;keystorepassword=mstr123#;keystoretype=jks;schema=tpch;};

2. Fingerprint-based Verification

If another certificate has not been uploaded using EXAoperation, a self-signed certificate is used by Exasol per default. Since this certificate is self-signed, TLS connections to Exasol will fail by default.
If it is not possible or desired for the client to verify the SSL/TLS certificate using the standard verification process, an additional method using the certificate's fingerprint has been provided to check it. For example, if the certificate is self-signed, this method can still check that the certificate used by Exasol is the expected certificate even if the signatures cannot be verified.
This method still provides some level of security, as opposed to simply disabling certificate verification entirely.
The fingerprint looks like:

2747B64E34414C293091FF37F428CC8D795B64B7159E50EDD7EA507B58D4FAEA

, you can find it in the error message. You can also see this fingerprint in Exaoperation (if your DB is on version 7.1 or newer):

The following table provides examples of connection string using fingerprint:

Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N
Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N
Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N

3. No Verification(not recommended)
If it is not possible or desired to perform any verification of the certificate at all, then verification can be completely disabled. By disabling certificate verification, however, a man-in-the-middle attack is possible because it cannot be verified that the client actually connected to the specified server in the connection string. As such, this method is not recommended.
The following table provides examples of how you can configure connection strings to disable verification.

Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N
Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N
Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N

4. Legacy Encryption
7.1 driver will always try to establish a TLS connection first, regardless of the database version. This means using a 7.1 driver to connect to any database version requires you to take action to make a connection to the database (see options below). If the database does not fully support TLS (versions < 6.2.15 and < 7.0.10), then the driver will reconnect with ChaCha20.

Because a TLS connection is always created first, either the certificate must be valid, the check must be deactivated, or the fingerprint must be used. To avoid this and the additional TLS login, it is recommended to set the legacy encryption connection string parameter for older databases.

Also for the latest versions where TLS is the preferred method for encryption, ChaCha can still be used if necessary.
If for some reason the use of ChaCha20 is preferred to TLS, it can be manually enabled using the following settings.

Strategy Platform (Early Adopter)
Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563
ODBCEXAHOST=exadb1.example.com
SSLCertificate=SSL_VERIFY_SERVER
EXAHOST=exadb1.example.com
Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N

5. Turn off encryption(not recommended)
If none of the other options above are viable, the last option is to disable encryption altogether. With this option, data will be transferred between the client and the server in an insecure manner.

Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N
Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N
Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N	Strategy Platform (Early Adopter) Database ObjectType of Permissions RequiredTablesSelect, Insert, Update, Create, Drop, DeleteIndexesCreate, DropTriggersCreate, DropFunctionsCreate, ExecutePackagesCreateProceduresCreate, Execute Standard Verification (default - explicit)Standard Verification (default - implicit)JDBCjdbc:exa:exadb1.example.com:8563; validateservercertificate=1jdbc:exa:exadb1.example.com:8563 ODBCEXAHOST=exadb1.example.com SSLCertificate=SSL_VERIFY_SERVER EXAHOST=exadb1.example.com Connection string examples 1Connection string examples 2JDBCjdbc:exa:exadb1.example.com/<fingerprint>:8563 jdbc:exa:exadb1.example.com:8563; fingerprint=<fingerprint>;ODBCEXAHOST=exadb1.example.com/<fingerprint>:8563 EXAHOST=exadb1.example.com:8563;fingerprint=<fingerprint>; Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;validateservercertificate=0 ODBCSSLCertificate=SSL_VERIFY_NONE JDBCjdbc:exa:exadb1.example.com:8563;legacyencryption=1ODBCUseLegacyEncryption=Y Connection string examples 1JDBCjdbc:exa:exadb1.example.com:8563;encryption =0 ODBCencryption=N

Appendix
https://community.exasol.com/t5/product-news/exasol-7-1-connection-security-changes/ba-p/6831
https://www.exasol.com/support/browse/EXASOL-2936
https://docs.exasol.com/7.1/administration/on-premise/access_management/tls_certificate.htm
https://community.exasol.com/t5/connect-with-exasol/tls-connection-fails/ta-p/6712
Version:
11.3.6

Comment

0 comments

Details

Knowledge Article

Published:

June 22, 2022

Last Updated:

June 22, 2022

java.io.IOException: TLS connection to host (192.168.56.101) failed: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. If you trust the server, connect to it using the fingerprint: 2747B64E34414C293091FF37F428CC8D795B64B7159E50EDD7EA507B58D4FAEA.

sudo openssl genrsa -des3 -out rootCA_key.pem 2048 sudo openssl req -x509 -new -nodes -key rootCA_key.pem -sha512 -days 365 -out rootCA.pem sudo vi v3.ext authorityKeyIdentifier=keyid,issuer basicConstraints=CA:FALSE keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment subjectAltName = @alt_names [alt_names] DNS.1 = *.Strategy.com DNS.2 = *.labs.Strategy.com DNS.3 = *.internal.Strategy.com sudo openssl req -new -nodes -out server.csr -newkey rsa:2048 -keyout server_key.pem sudo openssl x509 -req -in server.csr -CA rootCA.pem -CAkey rootCA_key.pem -CAcreateserial -out server.pem -days 365 -sha512 -extfile v3.ext cat server.pem > cert_chain.pem; cat rootCA.pem >> cert_chain.pem