Configuring a client for SSL connections

Configure an ESQL/C, ODBC, DB-Access, dbexport, dbimport, dbschema, or dbload database client by adding connection information to the sqlhosts file, setting SSL configuration parameters, and configuring the keystore and the digital certificates it stores.

Before you begin

The client and the server must be enabled with a mutual TLS version. Set the TLS version on the server with the TLS_VERSION configuration parameter.

About this task

Note: Transport Layer Security (TLS) is the successor to SSL. In this documentation, the same information applies to TLS as to SSL.

Procedure

Update connection information in the sqlhosts file (UNIX™) or the SQLHOSTS registry (Windows™), by using the onsocssl protocol for SSL SQLI client connections.

The following table shows an example of an sqlhosts file configured for these client connections.

Table 1. Example of sqlhosts file configured for SSL SQLI client connections
Server Name	Protocol	Host Name	Server Name
sf_on	onsoctcp	sanfrancisco	sf_serv
oak_on	onsocssl	oakland	oak_serv

Using a text editor, create a conssl.cfg file in the $ONEDB_HOME/etc directory. The file must contain the following information:
- SSL_KEYSTORE_FILE information that specifies the fully qualified file name of the keystore that stores the CA certificates for authenticating all the servers to which the client connects
- SSL_KEYSTORE_STH information that specifies the fully qualified file name of the stash file containing the encrypted keystore password.
  This parameter is optional and is only needed if the stash file is at a different location or has a different file name than the keystore file itself.
The format of the conssl.cfg file is:
```
Parameter    Value   # Comment
```
For example, the conssl.cfg file might contain this information:
```
SSL_KEYSTORE_FILE  /work/keystores/ssl_client.p12  # Keystore file
```
Create the keystore.
The client keystore must contain all the certificates that are needed to authenticate the server during the SSL handshake.

Generally, these certificates are CA certificates. You get such CA certificates from the CA that signed the server’s certificate. However, if the server’s certificate is self-signed, then the client keystore must contain a copy of this self-signed certificate in its keystore. In this case, you must extract that certificate from the server keystore and add it to the client keystore.
When you create the keystore, be sure that:
- The name and location of the keystore and optional stash file are as specified in the conssl.cfg file.
- Permissions on the keystore and its stash file are set to 666, even though the permissions are not enforced.
For more information on the OpenSSL utility commands, see the OpenSSL documentation at http://www.openssl.org.
- If the certificate created for the server is self-signed:
  1. Log on to the database server machine and extract the certificate from the server keystore:
```
openssl  pkcs12 -in  $ONEDB_SERVER.p12 \ 
 -passin pass:SERVERPASSWD \
 -out SSL_KEYSTORE_LABEL.cert.pem -nokeys
```
  2. Transfer the output file SSL_KEYSTORE_LABEL.cert.pem from the server machine to the client machine.
  3. Create the client keystore using the exported certificate in the PEM file as input:
```
openssl pkcs12 -export -out client.p12 \
  -passout pass:CLIENTPASSWD -in SSL_KEYSTORE_LABEL.cert.pem \
  -caname LABEL1 -nokeys
```
    Note: You must specify the option "-caname ..." in order to give a “friendly name” to the certificate as identifier in the client keystore.
  4. Use the utility onkstash to create the stash file with the keystore password:
```
onkstash client.p12 CLIENTPASSWD
```
- If your client connects to several different database servers or your database server has a CA-signed certificate:
  1. Collect all the certificates needed for the authentication of each database server into a single PEM file:
    - For a database server that uses a single self-signed certificate, use a command as described above to extract that certificate from the server’s keystore.
    - For a database server that uses a certificate signed by a CA, get all needed CA certificates from the CA.
    - In case there are duplicates in the collected certificates, eleminate all duplicates. Duplicate certificates may be collected e.g. when the same CA signed the certificates of several different database servers.
    - Concatenate all unique certificates into a single PEM file.
  2. On the client machine, create the client keystore using the single PEM file with the unique certificates as input:
```
openssl pkcs12 -export -out client.p12 -passout pass:CLIENTPASSWD \
 -in SINGLE_PEM_FILE -nokeys -caname LABEL1 -caname LABEL2 … 
```
    Note: Make sure to specify the option “-caname ...” multiple times with an individual friendly name as unique identifier for each certificate in the input PEM file.
  3. Use the utility onkstash to create the stash file with the keystore password:
```
onkstash client.p12 CLIENTPASSWD
```