Database connections with TLS

Kyle Aure on Jun 4, 2021

security , data-sources ,

Post available in languages:

This post is a primer to help you enable TLS database connections on Open Liberty. We discuss how TLS connections are established and provide some example configurations for the most commonly used database drivers: Db2, Oracle, Postgre, and SQLServer. We also cover how to debug some of the most common issues you might encounter when you enable TLS database connections. Most of the configuration in this post is for the database JDBC driver. You can easily configure driver settings on Open Liberty by enabling one of the JDBC-4.x features and configuring the dataSource element in your server.xml file.

This post is intended for users who are already familiar with the basics of database connections in Open Liberty. If you’re new to new Open Liberty, you can review our existing documentation on configuring database connections, Relational database connections with JDBC.

What are TLS database connections and why are they important?

Most enterprise-ready database providers allow users to create connections by using the Transport Layer Security (TLS) protocol.

TLS is a cryptographic security protocol that authenticates data transfer between systems, in this case between an Open Liberty server and a backend enterprise database. The Secure Socket Layer (SSL) protocol is also a cryptographic protocol that was replaced by TLS around 1999. However, due to the similarities between the protocols, SSL and TLS are terms that are sometimes used interchangeably.

At the time of writing this post the only two TLS protocol versions that are considered safe are TLSv1.2 and TLSv1.3.

How are TLS connections established?

The TLS handshake is a process by which a client and server set the terms for secure communications during a connection. The handshake occurs before any data is exchanged.

Through this process, the client and server agree on a version of the TLS protocol, select cryptographic algorithms that are called cipher suites, and authenticate one another by exchanging and verifying digital certificates. These digital certificates are data files that contain cryptographic keys and other identifying information about a client or server. TLS certificates are digitally signed by a certificate authority, which acts as a trusted third party that can verify the identity of the parties that are exchanging information.

The JDBC drivers use the underlying JDK to create and participate in the TLS handshake. Therefore, changes to protocol, cipher suites, and root certificates must be configured with the Transport Security feature. By default, Java 8 uses TLSv1.2.

For more information about Open Liberty support for TLS, see Secure communication with Transport Layer Security (TLS)

Common database examples

The JDBC API does not specify an interface for creating TLS connections. As a result, every JDBC driver provider that supports TLS connections has their own implementations and each JDBC driver must be configured differently. The following sections provide example configurations for the most popular JDBC drivers that Open Liberty supports.

Each driver must be configured with the following capabilities:

Enable TLS: Tell the driver to use a TLS connection instead of an insecure connection.
Establish trust: Tell the driver where it can find trusted certificates.

How you enable TLS for data source connections differs depending on your choice of database and JDBC driver. How you establish trust depends on your JDBC driver, the needs of your application, and the resources that are available.

Db2

The following server.xml example configures three Db2 data sources, each of which establishes trust in a different way:

<server>
  <featureManager>
    <feature>jdbc-4.2</feature>
    <feature>transportSecurity-1.0</feature> <!-- Only needed for Option 1 -->
  </featureManager>

  <!-- Option 1: Trust using jdk keystore -->
  <keyStore id="defaultKeyStore" location="security/keystore.p12" password="${KEYSTORE_PASS}" />

  <dataSource jndiName="jdbc/db2-tls-jdk" jdbcDriverRef="jdbcLib" containerAuthDataRef="dbData">
    <properties.db2.jcc
      databaseName="${DB2_DBNAME}" serverName="${DB2_HOSTNAME}" portNumber="${DB2_PORT_SECURE}"
      sslConnection="true"/>
  </dataSource>

  <!-- Option 2: Trust using driver configured truststore -->
  <dataSource jndiName="jdbc/db2-tls-native-trust" jdbcDriverRef="jdbcLib" containerAuthDataRef="dbData">
    <properties.db2.jcc
      databaseName="${DB2_DBNAME}" serverName="${DB2_HOSTNAME}" portNumber="${DB2_PORT_SECURE}"
      sslConnection="true"
      sslTrustStoreLocation="security/keystore.p12" sslTrustStorePassword="${KEYSTORE_PASS}" sslTrustStoreType="PKCS12"
/>

  <!-- Option 3: Trust using driver configured certificate -->
  <dataSource jndiName="jdbc/db2-tls-native-cert" jdbcDriverRef="jdbcLib" containerAuthDataRef="dbData">
    <properties.db2.jcc
      databaseName="${DB2_DBNAME}" serverName="${DB2_HOSTNAME}" portNumber="${DB2_PORT_SECURE}"
      sslConnection="true" sslCertLocation="server.crt" />
  </dataSource>
</server>

To enable TLS connections on the Db2 driver, add the sslConnection="true" attribute to the dataSource properties element.

To establish trust, the driver must compare the certificate that is sent by the database with a trusted certificate.

To provide a trusted certificate, configure one of the following options:

Option 1: Configure a keyStore element that contains the trusted certificate.
Option 2: Configure sslTrustStoreLocation, sslTrustStorePassword, and sslTrustStoreType attributes on the dataSource properties element that has the trusted certificate.
Option 3: Configure sslCertLocation to point directly to the trusted certificate.

For more information, see Configuring connections under the IBM Data Server Driver for JDBC and SQLJ to use SSL.

PostgreSQL

The following server.xml example configures two PostgreSQL data sources, each of which establishes trust in a different way:

<server>
    <featureManager>
        <feature>jdbc-4.2</feature>
        <feature>transportSecurity-1.0</feature> <!-- Only needed for Option 1 -->
    </featureManager>

    <!-- Option 1: Trust using jdk keystore -->
    <keyStore id="defaultKeyStore" location="security/keystore.p12" password="${KEYSTORE_PASS}" />

    <dataSource jndiName="jdbc/postgres-tls-jdk" jdbcDriverRef="jdbcLib" containerAuthDataRef="dbData">
      <properties.postgresql
        serverName="${POSTGRES_HOST}" portNumber="${POSTGRES_PORT}" databaseName="${POSTGRES_DB}"
        ssl="true" sslMode="verify-ca"
        sslfactory="org.postgresql.ssl.DefaultJavaSSLFactory"/>
    </dataSource>

    <!-- Option 2: Trust using driver configured truststore -->
    <dataSource jndiName="jdbc/postgres-tls-native" jdbcDriverRef="jdbcLib" containerAuthDataRef="dbData">
      <properties.postgresql
        serverName="${POSTGRES_HOST}" portNumber="${POSTGRES_PORT}" databaseName="${POSTGRES_DB}"
        ssl="true" sslMode="verify-ca"
        sslKey="security/keystore.p12" sslPassword="${KEY_PASSWORD}" />
    </dataSource>
</server>

To enable TLS connections on the PostgreSQL driver, add the ssl="true" attribute to the dataSource properties element.

The sslMode attribute is optional, but can be used to fine-tune the amount of verification done by the driver. By default, the sslMode attribute is set to prefer, which means the driver prefers to use encryption, but still creates a connection even if it cannot. The recommended setting is at least veryify-ca, which requires encryption, and verifies the server certificate.

To establish trust, the PostgreSQL driver must compare the certificate that is sent by the database with a trusted certificate. To provide a trusted certificate, configure one of the following options:

Option 1: Configure a keyStore element that contains the trusted certificate.
To tell the PostgreSQL Driver to use the java SSLFactory, set sslfactory="org.postgresql.ssl.DefaultJavaSSLFactory"
Option 2: Configure sslKey and sslPassword attributes on the dataSource properties element that has the trusted certificate.

For more information, see Postgre SQL JDBC Driver: Initializing the driver

Oracle

The following server.xml example configures two Oracle data sources, each of which establishes trust in a different way:

<server>
  <featureManager>
    <feature>jdbc-4.2</feature>
  </featureManager>

  <!-- General TLS connection properties -->
  <variable name="oracle.tls.props" value="oracle.net.ssl_version=1.2;oracle.net.ssl_server_dn_match=false;oracle.net.authentication_services=TCPS;"/>

  <!-- Option 1: Oracle wallet using SSO Keystore -->
  <variable name="oracle.wallet.sso" value="oracle.net.wallet_location=security/;" />
  <variable name="oracle.conn.props.wallet.sso" value="${oracle.tls.props}${oracle.wallet.sso}" />

  <dataSource jndiName="jdbc/oracleWalletSSO" jdbcDriverRef="jdbcLib">
    <properties.oracle URL="${env.SSL_URL}" connectionProperties="${oracle.conn.props.wallet.sso}"/>
  </dataSource>

  <!-- Option 2: Oracle wallet using PKCS Keystore -->
  <variable name="oracle.wallet.p12" value="oracle.net.wallet_location=security/;oracle.net.wallet_password=${env.WALLET_PASS};" />
  <variable name="oracle.conn.props.wallet.p12" value="${oracle.tls.props}${oracle.wallet.p12}" />

  <dataSource jndiName="jdbc/oracleWalletP12" jdbcDriverRef="jdbcLib">
    <properties.oracle URL="${env.SSL_URL}" connectionProperties="${oracle.conn.props.wallet.p12}"/>
  </dataSource>

  <!-- Option 3: Oracle wallet using Java Keystore/Truststore config properties -->
  <variable name="oracle.keystore" value="javax.net.ssl.keyStore=security/keystore.jks;javax.net.ssl.keyStoreType=JKS;javax.net.ssl.keyStorePassword=${KEYSTORE_PASSWORD};"/>
  <variable name="oracle.truststore" value="javax.net.ssl.trustStore=security/truststore.jks;javax.net.ssl.trustStoreType=JKS;javax.net.ssl.trustStorePassword=${TRUSTSTORE_PASS};"/>
  <variable name="oracle.conn.props.store" value="${oracle.tls.props}${oracle.truststore}${oracle.keystore}" />

  <dataSource jndiName="jdbc/oracleWalletJKS" jdbcDriverRef="jdbcLib">
    <properties.oracle URL="${env.SSL_URL}" connectionProperties="${oracle.conn.props.store}" />
  </dataSource>

</server>

To enable TLS connections on the Oracle JDBC driver, add the connectionProperties="oracle.net.ssl_version=1.2;" attribute to the dataSource properties element.

The connectionProperties attribute takes a semicolon-delimited list of properties to be applied to the driver.

The following Oracle properties can also be set for TLS configuration:

oracle.net.ssl_server_dn_match: If set to true, the driver confirms that the distinguished name (dn) of the certificate matches the hostname that sent the certificate.
oracle.net.ssl_cipher_suites: A specified subset of supported cipher suites for the driver to use.
oracle.net.authentication_services: Setting TCPS tells the driver to expect the database to authenticate the connection to the database via the TLS handshake. That is, if the TLS handshake succeeds, no other authentication is needed to access the database. This property requires extra setup on the database. Notice that in this example, neither datasource has an containerAuthDataRef attribute. The container authorization is not needed because the oracle.net.authentication_services=TCPS property indicates that the TLS handshake is the only authorization that is required.

The Oracle database has a feature called Oracle Wallets. When this feature is set up correctly on the database side, the Database Administrator can provide two Wallet files, cwallet.sso and ewallet.p12, to the client by using the oraclepki tool provided with the Oracle database installation. The oraclepki tool can also be used to convert the Oracle wallet into a Java keystore/truststore if that format is preferred.

To establish trust, configure one of the following options:

Option 1: To establish trust by using Oracle Wallets and the SSO keystore, point the driver to the directory that contains your cwallet.sso file: connectionProperties="oracle.net.wallet_location=security/;"
Option 2: To establish trust by using Oracle Wallets and the PKCS keystore, point the driver to the directory that contains your ewallet.p12 file and provide the keystore password for that file: oracle.net.wallet_location=security/;oracle.net.wallet_password=${env.WALLET_PASS};
Option 3: To establish trust by using Oracle Wallets and Java keystore and truststore files, use the javax.net.ssl.* connection properties.

the ewallet.p12 file contains additional encrypted data that the Oracle driver needs to establish the TLS connection to the database. As such, the default Security Providers shipped with the Java JRE will fail to read this file. If using the ewallet.p12 keystore users will need to add the Oracle PKI driver to their JVM native liberty path, and add the oracle.security.pki.OraclePKIProvider.OraclePKIProvider class to position 1 of their security provides.

For more information, see SSL With Oracle JDBC Thin Driver.

SQLServer

The following server.xml example configures three SQLServer data sources, each of which establishes trust in a different way:

<server>
  <featureManager>
    <feature>jdbc-4.2</feature>
    <feature>transportSecurity-1.0</feature> <!-- Only needed for Option 1 -->
  </featureManager>

  <!-- Option 1: Always trust server -->
  <dataSource jndiName="jdbc/ss-tls-unsecure" jdbcDriverRef="jdbcLib" containerAuthDataRef="dbData">
    <jdbcDriver libraryRef="SQLServerLibAnon"/>
    <properties.microsoft.sqlserver
                databaseName="${SS_DB}" serverName="${SS_HOST}" portNumber="${SS_TLS_PORT}}"
                encrypt="true"
                trustServerCertificate="true"/>
    <containerAuthData />
  </dataSource>

  <!-- Option 2: Trust using jdk keystore -->
  <keyStore id="defaultKeyStore" location="security/keystore.p12" password="${KEYSTORE_PASS}" />

  <dataSource jndiName="jdbc/ss-tls-secure-jdk" jdbcDriverRef="jdbcLib" containerAuthDataRef="dbData">
    <properties.microsoft.sqlserver
                databaseName="${SS_DB}" serverName="${SS_HOST}" portNumber="${SS_TLS_PORT}}"
                encrypt="true" hostNameInCertificate="${SS_HOST}" />
  </dataSource>

  <!-- Option 3: Trust using driver configured truststore -->
  <dataSource jndiName="jdbc/ss-tls-secure-native" jdbcDriverRef="jdbcLib" containerAuthDataRef="dbData">
    <jdbcDriver libraryRef="SQLServerLibAnon"/>
    <properties.microsoft.sqlserver
                databaseName="${SS_DBNAME}" serverName="${SS_HOST}" portNumber="${SS_TLS_PORT}"
                encrypt="true" hostNameInCertificate="${SS_HOST}"
                trustStore="security/truststore.p12" trustStorePassword="${TRUSTSTORE_PASS}" />
  </dataSource>
</server>

To enable TLS connections on the SQLServer driver, add the encrypt="true" attribute to the dataSource properties element.

To establish trust, the driver must compare the certificate that is sent by the database with a trusted certificate. To provide a trusted certificate, configure one of the following options:

Option 1: Set the trustServerCertificate attribute to true. This attribute tells the driver to always trust any certificate that is sent by the database. This option is helpful when debugging your configuration, but should not be used in a production environment.
Option 2: Configure a keyStore element that has the trusted certificate and set the hostNameInCertificate attribute on the datasSource properties element to tell the driver what hostname to expect in the certificate.
Option 3: Configure trustStore and trustStorePassword attributes on the dataSource properties element that has the trusted certificate.

For more information, see SQL Docs: Setting the connection properties.

Debugging TLS issues

To enable TLS debug trace, add a jvm.options file to your server configuration directory and set one of the following properties:

# All debug can be verbose
-Djavax.net.debug=all

# Debug handshake, keystore, truststore, and general TLS messages
-Djavax.net.debug=ssl:handshake:keymanager:trustmanager

The following sections have snippets of debug provided by the Java Virutal Machine (JVM), in this case OpenJDK + OpenJ9. Each section helps point out the debug that can show potential issues with your configuration and suggestions on how to fix them. Since this debug is provided by the JVM, you will see the same output no matter what JDBC Driver you are using. The JDBC Driver may also give a meaningful message.

Protocol and Cipher Suites

Ensure protocol’s match, and both client and server have common cipher suites. Typically, the database, or server, chooses the cipher suite.

In the following example, both the client and the server use the TLSv1.2 TLS version and the TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256(0xC02F) cypher suite:

javax.net.ssl|DEBUG|48|Default Executor-thread-16|ClientHello.java:653|Produced ClientHello handshake message (
"ClientHello": {
  "client version"      : "TLSv1.2"
  "cipher suites"       : "[TLS_AES_128_GCM_SHA256(0x1301), TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256(0xC02F), ...]",

javax.net.ssl|DEBUG|48|Default Executor-thread-16|ServerHello.java:870|Consuming ServerHello handshake message (
"ServerHello": {
  "server version"      : "TLSv1.2"
  "cipher suite"        : "TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256(0xC02F)"

Verify Certificate (client side)

An exception similar to the following example likely means that the database (server) sent a certificate in its ServerHello message that the driver could not find a trusted certificate to verify against:

PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

Search for the following trace to determine what trusted certificates were added:

javax.net.ssl|DEBUG|35|Default Executor-thread-3|X509TrustManagerImpl.java:79|adding as trusted certificates (
  "certificate" : {
    "version"            : "v3",
    "serial number"      : "1C 3D 0F 3E",
    "signature algorithm": "SHA256withRSA",

Check your server configuration and ensure that the truststore that is configured on the JDBC driver or the Transport Security feature exists and contains the certificate you expect the database to send.

Verify Certificate (server side)

If you configured your driver to send a certificate back to your database to verify, as in the previous Oracle example, then that verification might also fail.

In the following example, the driver could not find a certificate to send:

javax.net.ssl|DEBUG|41|Default Executor-thread-9|CertificateMessage.java:290|No X.509 certificate for client authentication, use empty Certificate message instead
javax.net.ssl|DEBUG|41|Default Executor-thread-9|CertificateMessage.java:321|Produced client Certificate handshake message (
"Certificates": <empty list>
)

Check your server configuration and ensure that the keystore that is configured on the JDBC driver or the Transport Security feature exists and contains the certificate you expect the driver to send.

In other cases, the driver sends a certificate, but database cannot not verify it. This issue is difficult to debug because the issue is on the database side. The driver might throw one of many different exceptions to indicate that it could not finish the handshake.

The following sample exception shows an error message when the driver tries to connect to an SQLServer database that the database cannot verify:

java.sql.SQLException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server did not return a response. The connection has been closed. DSRA0010E: SQL State = 08S01, Error Code = 0

Check with your database administrator to see if logs were produced by the database that can indicate the reason the database did not verify the certificate. Similar to the client side, this error could occur because a truststore location was mis-configured, or did not contain the correct certificate.

See all blog posts