Transport Layer Security Secures Data in Transit between Network FairCom DB Clients and Servers

Note: FairCom DB File and User Security are available only when using the client/server operational model.

FairCom applications can now secure data in transit between c-tree network clients and FairCom servers. Transport Layer Security (TLS, also commonly referred to as its predecessor SSL, Secure Sockets Layer) is a cryptographic protocol designed for secure network communications using public key cryptography for authentication of the communicating party. Symmetric cryptography is used to encrypt transmitted data over the wire. FairCom DB TLS relies on OpenSSL toolkit support and implements TLS protocol V1.3. Earlier versions of TLS (and predecessor SSL) protocols contain known and exploited vulnerabilities. FairCom DB only supports TLS via TCP/IP communications protocols (IPv4 and IPv6). (For more about TLS, see https://en.wikipedia.org/wiki/Transport_Layer_Security.)

Two modes of TLS connections are available: basic and peer authenticated. Basic TLS connections are encrypted using only a server-side certificate; there is no local certificate requirement for a client. This makes deployment and management of secured connections easy.

TLS Certificates

It is the server administrator’s responsibility to ensure a correct and valid certificate pair, as well as proper configuration of allowed TLS connections.

Creation and management of TLS certificates, as well as use of a Certification Authority (CA), is beyond the scope of this document. Consult OpenSSL and other TLS supporting documentation and be sure you firmly grasp all details regarding use of TLS for network security before deploying.

Server certificates may be created and provided as two separate files: a certificate and a key. They can also be combined into a single file. There is no required file naming convention. Certificate files are usually created and/or provided as Base64 encoded X.509 certificate files and denoted with a .pem extension (Privacy Enhanced Mail). They can be identified with this set of surrounding identifiers within the file:

-----BEGIN CERTIFICATE-----

-----END CERTIFICATE-----

The private key is likewise identified:

-----BEGIN PRIVATE KEY-----

-----END PRIVATE KEY-----

The private key is often included in the same certificate file or as a separate .key file.

A certificate key can be optionally passphrase protected. However, this then requires the passphrase to be presented at server startup. FairCom key store files provide this ability.

Always securely maintain key files. Store key files only in permissions protected server areas and never distribute.

Server-Side Configuration

To enable TLS (SSL), see keywords for TLS.

Standard c-tree TCP/IP ports are used for connections regardless of TLS configuration. That is, a single ISAM port will handle both TLS encrypted and non-encrypted connections, and likewise for the SQL port. There is no need for separate port configurations.

For the HTTPD plugin you can verify ciphers in use using the system nmap command (Linux) against the HTTPD port when no ciphers are specified in cthttpd.json. This can be done with the linux command:

nmap --script ssl-enum-ciphers -Pn -p 8443

Client-Side Configuration

Both FairCom ISAM and SQL clients support TLS connections.

Peer Authentication - TLS connection with server certificate validation:

By default, a c-tree client requires a PEM file containing the server public certificate—ONLY the public certificate, not the server private key.

Important: The server private key should be securely maintained only at the FairCom Server location at all times.

By default, ISAM and SQL client libraries use the file ctsrvr.pem in the client process' working directory when connecting. An ISAM client can change the file name that the client library searches for by calling the ctSetCommProtocolOption() function with the option ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE:

ctSetCommProtocolOption(ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE, "myservercert.pem");

Basic - TLS connection without server certificate validation (ISAM only):

It is also possible to establish a TLS connection from an ISAM client without validating the FairCom certificate. Such a connection is encrypted but there is no guarantee of the server’s identity. To use this option, call the ctSetCommProtocolOption() function with the option ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE with an empty certificate name before connecting:

ctSetCommProtocolOption(ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE, "");

When the client does not use a server certificate, the connection is encrypted, but there is no guarantee that the client is connected to that specific server. This implies that a "man in the middle" attack could be possible.

If an error occurs when connecting using SSL, the connection attempt returns error 1104 (SSLCONN_ERR). To get more detailed information enable SSL logging by either:

• calling ctSetCommProtocolOption() with the ctCOMMOPT_FSSLTCP_DEBUG_LOG option:

ctSetCommProtocolOption(ctCOMMOPT_FSSLTCP_DEBUG_LOG, "ssldebug.log");

or

• setting the environment variable CTSSL_DEBUG_LOG to the name of the SSL debug log file.

To request a TLS-enabled ISAM connection, append ^fssltcp to the server name. For example:

ctadmn ADMIN ADMIN "" "FAIRCOMS@localhost^fssltcp"

Note: When you append ^fssltcp to the server name you must quote the entire connection string, for example:

"FAIRCOMS@localhost^f_tcpipv6"

Windows normally interprets the carat as an escape character or line continuation, so the entire server connection string must be quoted when it includes a caret.

Other examples showing how to specify a desired communication protocol when connecting:

To connect using shared memory (without falling back to TCP/IP if the shared memory connection fails):

FAIRCOMS@localhost^fsharemm 

To connect using TCP/IP v4 without SSL:

FAIRCOMS@localhost^f_tcpip

To connect using TCP/IP v6 without SSL:

FAIRCOMS@localhost^f_tcpipv6

To connect using TCP/IP v6 with SSL:

FAIRCOMS@localhost^fssltcpv6

To request a TLS-enabled connection for a SQL connection, prepend ssl: to the connection string. For example:

isql -u admin -p ADMIN ssl:6597@localhost:ctreesql

Standard FairCom DB SQL interfaces (JDBC, ADO.NET, ODBC, PHP, Python) each have independent standards for connection strings. JDBC and ADO.NET are specifically described later in this chapter.

ctadmn and FairCom DB Monitor show the communication protocol that is in use, including whether or not the connection is using TLS (SSL):

F_TCPIP     indicates an unencrypted ISAM TCP/IP connection.
FSSLTCP     indicates an SSL-enabled ISAM TCP/IP connection.
SQL_TCPIP   indicates an unencrypted SQL TCP/IP connection.
SQL_SSLTCP  indicates an SSL-enabled ISAM TCP/IP connection.

X.509 Support

Support has been added for X.509 client/server authentication. For now, only X.509 authentication when using TLS-encrypted TCP/IP for ctree is supported. Future revisions will extend this to SQL and shared memory protocols.

X.509 authentication is not supported in combination with LDAP authentication.

To use X.509 authentication, the client must provide a PEM formatted X.509 certificate with a complete certificate chain using the same root CA as the server certificate. If the client's certificate is successfully validated by the server, the subject field on the client X.509 will be parsed by the server to extract a username. This username is used to determine the database permissions to be granted. A complete certificate chain consists of a PEM formatted file with the certificate for the user, followed by the certificate for the issuer, followed by the certificate for that issuer, and so on, ending in the root CA certificate.

When X.509 authentication is enabled and a certificate is provided by the client, the username and password arguments passed to InitISAMX() are ignored.

New Security keywords have been added to enable X.509. See Client Communications Keywords for TLS.

The following functions have been enhanced for configuring X.509 authentication

• ctSetCommProtocolOption
• InitISAMXtd

Expected TLS Performance

The resource-intensive portion of a TLS connection is the initial creation. A secure communications channel is established via asymmetric encryption requiring a session key exchange. This initial key exchange and encryption process is the slowest computational epoch. Once connected, ongoing connection overhead is negligible. Further, most modern hardware contains dedicated CPU instructions for enhanced encryption performance. Thus, it is important to avoid repeated connections and maintain an established TLS connection when at all possible.

Compatibility

COMPATIBILITY TCPIP_CHECK_DEAD_CLIENTS

Advanced SSL Certificate Options

The ssl_certificate keyword in the config/cthttpd.json web server plug-in configuration supports the fccert.pem, which is a self-signed certificate we supplied. To use your own certificate, use the following keywords to config/cthttpd.json:

• ssl_key: SSL private key - This can be embedded in the same file provided the in ssl_certificate. For example, our default fccert.pem certificate file has both the certificate and the private key, so, ssl_key is not required.
• ssl_ca: SSL Certificate Authority - External authority that issues and validates the certificate.
• ssl_cipher_suites - Colon-delimited list of SSL cipher suites.

Troubleshooting

There is a client-side environment variable for debugging. To enable SSL logging for the client, set the CTSSL_DEBUG_LOG environment variable to the log file name.

For server side SSL debugging, add DEBUG_LOG <logfile> to the SSL subsection.

Testing with Default FairCom DB Certificates

For testing and evaluation purposes only, a self-signed X.509 certificate is included in your default FairCom DB package. OpenSSL was used to create this certificate.

Security Note: It is critical this included certificate is never used in a production setting.

In the example below, a certificate and private key are both included in the file ctree_ssl.pem. Unencrypted TCP/IP connections are allowed, and the specified ciphers are the ones that are allowed to be used in encrypting the SSL connection:

SUBSYSTEM COMM_PROTOCOL SSL {
 SERVER_CERTIFICATE_FILE ctree_ssl.pem
 SSL_CONNECTIONS_ONLY NO
 SSL_CIPHERS ALL:!aNULL:!eNULL:!SSLv2:!LOW:!EXP:!RC4:!MD5:@STRENGTH
}

To enable only TLS-encrypted communications for all connections, change SSL_CONNECTIONS_ONLY to YES, and, optionally, comment Shared Memory communications support to prevent local unencrypted shared memory connections. (FairCom DB Shared Memory connections are not supported for TLS encryption.)

;COMM_PROTOCOL  FSHAREMM

Finally, for peer authentication, an additional cross-check validation against the server certificate, copy ctsrvr.pem to your client working directory. FairCom DB management and administration tools already include this local client certificate file in their working folder:

<faircom>\server

All GUI tool connection dialogs contain specific parameter options for enabling TLS connections. support.faircom.com was specified as the Common Name in provided FairCom DB default certificates. Specify support.faircom.com when testing FairCom DB SQL Explorer for TLS authentication using ADO.NET to succeed using these included certs.

ADO.NET Support for TLS

In V11.5 and later, the FairCom DB ADO.NET provider supports TLS/SSL connections per Microsoft specifications.

The ADO.NET provider uses the local certificate store to locate certificates when using peerAuthentication. The server's CA (or a self-signed) certificate ctsrvr.pem must be added to the trusted root certificate store on the client machine for the .NET framework's certificate authentication to succeed:

CertMgr.exe /add ctsrvr.pem /c /s /r localMachine root

Note that the Common Name specified in the server certificate is the name that the application must specify in the ADO.NET connection string for the TLS option.

For this certificate, we used support.faircom.com as the Common Name, and so the ADO.NET connection string must specify sslcert=support.faircom.com for the TLS authentication to succeed.

If client certificate authentication is desired (V13.1 and later), a certificate chain and private key for the client must first be added to the current user's personal certificate store. This can be done using a PKCS #12 file containing the desired certificate chain and private key.

Connection String

The ADO.NET connection string is similar to the JDBC string. The connection string accepts a new property:

ssl=<value>

which can have two values:

• basic - Basic SSL setting, no peer certificate authentication, only communication encryption as requested by server certificate
• peerAuthentication - Server certificate authentication.

In the case of peerAuthentication the server certificate Common Name must be provided by the new property:

sslcert=<value>

If this property is not specified, the value of the Server setting is used to match the certificate.

If peerAuthentication is enabled, client authentication may also be attempted. If X509_AUTHENTICATION is enabled by the server then a client certificate can replace the normal password based authentication. Identify the Common Name for the client certificate to use:

ClientSSLCert=<value>

The current user's personal certificate store is searched first for this certificate. The localmachine store will also be searched if a matching certificate is not found in the current user's store and the process has permission to access the localmachine's personal store.

If X509_AUTHENTICATION is enabled by the server and the ClientSSLCert is accepted, any UID and PWD values included in the connection string are ignored.

Examples:

"UID=ADMIN;PWD=ADMIN;Database=CtreeSQL;Server=localhost;Service=6597;ssl=basic";
"UID=ADMIN;PWD=ADMIN;Database=CtreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com";
"Database=CtreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com;ClientSSLCert=JohnDoe";

ODBC Support for SSL

You can use SSL with an ODBC data source. To configure SSL, open the ODBC Data Source Administrator.

Once you invoke the ODBC Data Source Administrator:

1. In the dialog box for the type of data source you choose, choose the Add button. The Add Data Source dialog box appears.
2. Select FairCom DB SQL from the list of installed drivers and choose Finish. The FairCom DB ODBC Setup dialog box appears.
3. Fill in the dialog box fields as shown in the following figure and choose OK. The ODBC Data Source Administrator writes the values you supply to ODBC.INI or to the DSN file you indicated.

Data Source Name - A local name for the FairCom DB SQL data source for use in connect calls and by the ODBC Administrator.

Description - Optional descriptive text.

Host - Specify the machine name on which the FairCom DB SQL Server is running.

Database - The name of the database where the FairCom DB SQL data source resides.

User ID / Password - User name and password for connecting to the database. The driver uses those values if the application does not supply them in the call. You can leave these fields blank if you want the driver to use the defaults on the server. If no defaults are defined and you leave these fields blank, the user will be prompted when the application connects.

Service - The name of the Service FairCom DB SQL listens to. If empty, sqlnw is used.

Default Fetch Size - This value is the size (in bytes) used by the driver to fetch multiple rows from the server. It reduces network requests resulting in performance gains. If not set, the internal buffer size is 5000 bytes.

• In your connection string, set the attribute "FETCH_SIZE=[number of bytes]"
• In your ODBC.INI file, set the attribute "Default Fetch Size=[number of bytes]"

Connection string settings take precedence over DSN and ODBC.INI settings.

Default Query Timeout - It is possible to set the Default Query Timeout (in seconds) in the DSN and in the connection string. 

• In the connection string, the attribute is: QUERY_TIMEOUT=[number of seconds]
• In the ODBC.INI file, the attribute is: Default Query Timeout=[number of seconds]

Settings in the connection string take precedence over the setting in the DSN or ODBC.INI.

Preserve Cursor - This behavior is configurable from within the ODBC administrator. 

Scrollable Cursor - This allows you to enable support for ODBC applications that require a scrollable cursor. 

Client Character Set - Use the drop-down list to select the character set used by the client. 

SSL - Enter optional parameters to configure the SSL. The following entries can be used to configure the SSL connection: 

1. empty - Do not use SSL.
2. BASIC - Use SSL without certificate checking. 
3. <certificate file name> - Use SSL with certificate checking using the certificate file specified. If no path is entered, the file must be in the current working directory.

Options - Enter any optional parameters to be included in the connect string. 

4. The Data Source Dialog box reappears, and now includes the newly-added data source.

Configuring SSL in Your Connection String

In the ODBC connection string, it is possible to add "SSL=xyz" where "xyz" is one of the options 2 or 3 from the SSL parameters listed above in the FairCom DB ODBC Setup dialog box.

You can add one of the following to your existing ODBC connection strings to enable TLS/SSL:
"SSL=BASIC" - Encryption with default server certificate.
"SSL=ctsrvr.pem" - Use Peer authentication with explicitly named cert located in the local directory.
"SSL=C*\certs\ctsrvr.pem" - Use Peer authentication with an explicitly named cert with a full path.

JDBC Support for TLS

In FairCom DB V11.2 and later, FairCom DB SQL JDBC supports TLS connections per the JDBC standard. Enable TLS in a JDBC connection URL using the ssl=value parameter string.

TLS connections are enabled in the JDBC connection URL using the new format (it is not supported on the old URL format) and a new parameter ssl.

The new URL format is:

jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[&param=value]...]

The valid param values are:

• characterEncoding - Replace encoding with a valid Java encoding name (e.g., US‑ASCII, ISO‑8859-1, UTF‑8, etc.).
• password
• user
• ssl - The valid values for ssl are:
  • basic
  • peerAuthentication
• trustStore - The name of a local trust store that includes the server's CA certificate.
• trustStorePassword - The password for the specified trust store.
• keyStore - The name of a local key store that has the user certificate and private key.
• keyStorePassword - The password for the specified keyStore.

NOTE: For backward compatibility, the older format ("jdbc:ctree:6597@localhost:ctreeSQL", "ADMIN", "ADMIN") is still supported but should be considered deprecated.

Basic TLS with JDBC clients

Traffic to the server is encrypted, but there is no assurance of the server's identity.

Basic SSL encryption on the client is enabled by the URL parameter ssl, for example:

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=basic");

Peer Authenticated TLS with JDBC clients using System properties

If the client wants to authenticate the server, then the client's trust store must contain the server's CA certificate (or a self-signed server certificate). A Java keystore must first be created that contains this CA certificate.

For example, the following adds the trusted CA certificate ctsrvr.pem to a keystore named server.store

keytool -importcert -file ctsrvr.pem -keystore server.store

keytool is part of the Java distribution, and will prompt you for a password used to encrypt the trust store. In this example we used mypassword as the password.

Client SSL with server authentication is enabled by the URL parameter ssl set to peerAuthentication.

You must set the system properties javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword to reference the trust store for the desired database server.

Example:

System.setProperty("javax.net.ssl.trustStore","server.store");
System.setProperty("javax.net.ssl.trustStorePassword","mypassword");
Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=peerAuthentication");

Full TLS authentication with JDBC clients

The FairCom database server may be configured to allow or require client TLS authentication in place of password based authentication. The client needs to both authenticate the server (with the same requirements as for peerAuthentication), and provide proof of identity to the server by providing a client certificate that is trusted by the server. This may be set in the connection string by specifying a trustStore for the server and keyStore for the user.

Example:

Create the trust store with the trusted CA certificate ctsrvr.pem to a keystore named server.store. Keytool is part of the Java distribution, and will prompt you for a password used to encrypt the trust store. In this example we use mypassword as the password.

keytool -importcert -file ctsrvr.pem -keystore server.store

The client must possess a keystore containing their certificate and private key. Here we assume a keystore exists named johndoe.pkcs12 protected with password secret.

NOTE: The default keystore format is controlled by the java.security configuration file under the property keystore.type. The PKCS12 keystore format is supported by all Java implementations, but prior to Java9 JKS was the default format. You may need to adjust this configuration for your keystore to be accessed by Java.

The following connection string will attempt to connect using these certificate sets for TLS authentication.

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?trustStore=server.store&trustStorePassword=mypassword&keyStore=johndoe.pkcs12&keyStorePassword=secret");

See Also:

Java keytool documentation provides examples of how to generate a key pair, request a certificate from a CA, or generate certificates for a server.

Allow ISAM Client to Use SSL without Client Having Server Certificate

The c-tree ISAM client now supports establishing an SSL connection without requiring the client to have the server certificate in the file ctsrvr.pem. By default, the client requires the certificate file, but it can disable this requirement by calling the ctSetCommProtocolOption() function with the following parameters after registering a c-tree instance and before connecting to c-tree Server:

ctSetCommProtocolOption(ctCOMMOPT_FSSLTCP_SERVER_CERTIFICATE, "");

When the client does not use a server certificate, the connection is encrypted, but there is no guarantee that the client is connected to that specific server. This implies that a "man in the middle" attack could be possible.

OpenSSL Headers for Linking FairCom DB Client Applications

The OpenSSL headers have been included in this package for your convenience. These headers must be compiled and linked into your project. Examine your opensslv.h header in the ctree.drivers/include/openssl folder for the current version included in your build.

The OpenSSL include files are located under /ctree/include.

Libraries are provided for multiple build platforms and are located in /ctree.drivers/lib/License.Lib/openssl, where you'll find:

• libeay32.lib
• ssleay32.lib

Choose the appropriate build platform from the provided versions.

OpenSSL Now Provides Default Faster AES Encryption

FairCom now supports an updated AES algorithm based on the OpenSSL standard by default. Previous algorithm implementation can be restored by specifying in ctsrvr.cfg the keyword OPENSSL_ENCRYPTION NO.

This change is expected to provide security and, with internal testing, we have seen an up to 20% performance improvements.

This modification changes file passwords encrypted on the client-side for secure transmission to use OpenSSL.

TLS/SSL Tutorials

As an example of linking with OpenSSL libraries, you can view the Tutorial makefiles and IDE Projects in the SDK directories. For example:

/linux.x64.64bit/sdk/ctree.ctdb/tutorials/cmdline/Makefile