Configuring s-Server to Use SSL

<< Click to Display Table of Contents >>

Navigation:  Administering SQLstream Blaze > Configuring s-Server >

Configuring s-Server to Use SSL

Previous pageReturn to chapter overviewNext page

This document describes how to enable s-Server to use the Secure Sockets Layer (SSL) protocol for secure communication between itself, sqlline client, and other clients that access s-Server through JDBC. SSL lets you use server authentication, client authentication, and data encryption. It also contains steps for generating sample certificates and sample test procedures to verify SSL communication status. Most users will likely want to use server certification only.

s-Server uses openSSL. We recommend having some familiarity with openSSL before using this feature. Some knowledge of SSL-related concepts such as certificates, trust chains, certificate authority concepts, and so on would also be helpful. Information about these can be found here:

https://www.digitalocean.com/community/tutorials/openssl-essentials-working-with-ssl-certificates-private-keys-and-csrs

To use client certificates, you need some familiarity with keytools and Java key storage concepts. Information about these can be found here:

https://www.digitalocean.com/community/tutorials/java-keytool-essentials-working-with-java-keystores

This document covers the following main topics:

Enabling SSL in s-Server.  (This enables encrypted data transfer.)
Generating and Installing Self-Signed Certificates  (This enables encrypted data transfer using certificates known to an enterprise.)
Modifying Your Client to Work with a Secure s-Server. (This enables the client to work with a secure s-Server.)
Using Client Certificates with s-Server (This adds client authentication in addition to encrypted data transfer.)

Overview of s-Server Technologies and Certificate Storage

s-Server is a streaming database that is written in both C++ and Java.

The database runtime and the networking layer is written in C++. It uses boost+openssl for SSL/TLS and cryptography. These use the openSSL keystore to store certificates.

The JDBC client and client side network layer is written in Java and uses apache-mina and java security classes. These use the java keystore to store certificates.

s-Server is capable of using more recent encryption methods like ECDHE (Elliptic curve Diffie–Hellman with ephemeral keys) for encryption. ECDHE has shorter keys, faster performance and supports forward secrecy. For more information about ECDHE, see the following links.

https://en.wikipedia.org/wiki/Elliptic_curve_Diffie%E2%80%93Hellman

https://blog.cloudflare.com/staying-on-top-of-tls-attacks/

Enabling SSL in s-Server

The steps below describe how to enable encrypted data transfer using a server certificate and key (but not a client certificate or key). s-Server requires both a certificate and a key to use SSL. Sample self-signed certificates are installed with s-Server. These let you get started quickly. Once you are familiar with s-Server's implementation of SSL, you should replace these with your own.

When SSL is enabled, s-Server will conduct a basic TLS handshake as described here:

https://en.wikipedia.org/wiki/Transport_Layer_Security#Basic_TLS_handshake

Before beginning, make sure that your certificate and key are in $SQLSTREAM_HOME.

Note: $SQLSTREAM_HOME refers to the installation directory for s-Server, such as /opt/sqlstream/5.0.XXX/s-Server.

These are called cert.pem and key.pem. They need to be pem formatted.

Note: When you create your own certificate and key, these must also be named cert.pem and key.pem.

You can also keep them in a separate location and create symbolic links with the names above.

To enable SSL:

1)  Add or change the following line in aspen.properties:

aspen.sdp.tcpListenEnableSSL=true

 

2)  Start sqllineclient with the following parameters

./sqllineClient --tcpTrustAllSSL=true --tcpListenEnableSSL=true 9904

 

Once connected, all communication between sqlline client and s-Server will be encrypted via SSL.

Note: The tcpTrustAllSSL parameter is optional and is primarily used with self signed certificates.

Note: You can require a private key password for SSL. See Passing a Private Key Password to s-Server below.

To test that s-Sever is communicating via SSL, take the following steps:

1)  After changing aspen.sdp.tcpListenEnableSSL in aspen.properties, start s-Server.

2)  Run the following openSSL command that acts as a client

openssl s_client -connect localhost:9904

 

It will connect and display connection information.

You should see something like the following:

.....

subject=/C=US/ST=CA/L=San Francisco/O=sqlstream.com/OU=development/CN=development.sqlstream.com

issuer=/C=US/ST=CA/L=San Francisco/O=sqlstream.com/OU=development/CN=development.sqlstream.com

---

No client certificate CA names sent

---

SSL handshake has read 1176 bytes and written 421 bytes

---

New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES256-GCM-SHA384

Server public key is 1024 bit

Secure Renegotiation IS supported

Compression: NONE

Expansion: NONE

SSL-Session:

   Protocol  : TLSv1.2

   Cipher    : ECDHE-RSA-AES256-GCM-SHA384

 

To test that sqlline Client Connects via SSL, take the following steps:

Run the following openSSL command that acts as a server.

openssl s_server  -accept 9904 -key ./key.pem -cert ./cert.pem  -debug -msg [ -CApath /etc/ssl/certs ]

 

Run the sqllineClient command below

./sqllineClient --tcpTrustAllSSL=true --tcpListenEnableSSL=true 9904

 

OpenSSL will display connection information along the following lines:

<<< TLS 1.2 Handshake [length 0010], Finished

.......

CIPHER is ECDHE-RSA-AES128-SHA256

Secure Renegotiation IS supported

......

 

Checking for Client Handshake Errors

To see errors, use the self signed certificate and start the server, and try to connect sqlline without the tcpTrustAllSSL parameter.

The connection will fail. Look at trace/ClientTrace.log around the following errors:

VirtualBox/127.0.1.1:9904) WARNING [15 2016-04-13 15:08:19.184]: com.sqlstream.aspen.sdp2.StreamingDataProtocolImpl exceptionCaught

SSL handshake failed.                                                                               WARNING [15 2016-04-13 15:08:19.184]: com.sqlstream.aspen.sdp2.StreamingDataProtocolImpl exceptionCaught SSL handshake failed

 

Generating and Installing Self-Signed Certificates

The following commands are used to generate self-signed sample certificates, using elliptic curve secp256r1.

You can use the same steps to generate your own certificate with your own organizational name and other information.

openssl genpkey  -algorithm RSA  rsa_keygen_bits:3072 ec_paramgen_curve:secp256r1 -out server.key

openssl req -new -key server.key -out server.csr

openssl x509 -req -days 3650 -in server.csr -signkey server.key -out server.crt

 

Once you have generated self-signed certificates, place them in $SQLSTREAM_HOME.

Note: $SQLSTREAM_HOME refers to the installation directory for s-Server, such as /opt/sqlstream/5.0.XXX/s-Server.

These should be called cert.pem and key.pem. They need to be pem formatted.

Modifying Your Client to Work with a Secure s-Server.

This enables your Java client communicating over JDBC to work with a secure s-Server.

To write Java code for the JDBC Client that Uses SSL, you need to pass the same arguments that the sqllineClient accepts in your Java code either through connection properties or as part of the url.

Below is an excerpt of the actual sqllineClient code that in turn establishes the JDBC connection.

          final Properties info = new Properties();

           info.put("user", username);

           info.put("password", password);

           info.put("tcpListenEnableSSL", Boolean.toString(opts.gettcpListenEnableSSL()));

           info.put("tcpTrustAllSSL", Boolean.toString(opts.gettcpTrustAllSSL()));

 

           connection = theDriver.connect(url, info);

           meta = connection.getMetaData();

 

Alternatively, you can pass the same arguments as part of the URL string:

jdbc:vjdbc:sdp://localhost:9904;tcpTrustAllSSL=true;tcpListenEnableSSL=true

 

To try it, again use sqllineClient and escape the special chars at command line.

./sqllineClient -n sa -p test -u jdbc:vjdbc:sdp:'/'/localhost:9904';tcpTrustAllSSL=true;'tcpListenEnableSSL=true

 

Using Client Certificates with s-Server

You can also set s-Server to accept only JDBC clients that provide client certificates.

When enabled, s-Server will authenticate with certificates exchanged between server and client, as described here:

https://en.wikipedia.org/wiki/Transport_Layer_Security#Client-authenticated_TLS_handshake

You do so by enabling the following setting in aspen.properties:

sdp.ssl.client.verify_fail_if_no_peer_cert

 

After you enable this setting, s-Server will require client certificates from JDBC clients. Connections without such certificates will fail. The JDBC client should send the certificate to the server under the alias com.sqlstream.client.jdbc.

Note: The server also has a setting called aspen.sdp.ssl.client.CApath for adding a certificate verification path.

Enabling SSL for JDBC Clients

Client side certificates are in addition to the SSL on the server. Once enabled, s-Server will authenticate the client by determining that it has a client side certificate.

To enable SSL for JDBC:

1.Make sure that s-Server is SSL enabled.
2.Store your certificate under the alias com.sqlstream.client.jdbc
3.Acquire a certificate from a certification authority or use the command below for a self signed certificate for testing purposes.

sudo keytool -genkey -alias com.sqlstream.client.jdbc -keyalg RSA -validity 3650 -keystore /usr/lib/jvm/java-8-oracle/jre/lib/security/cacerts

 

It will ask you for the keystore password. The default password is “changeit”.

The client code honors standard Java keystore system settings and passwords, as described in the following link.

http://docs.oracle.com/javase/7/docs/technotes/guides/security/jsse/JSSERefGuide.html#Customization

 

If no keystore and password are given, then the client uses the default keystore and password.

Using Self-Signed Certificates with JDBC

If you use a self-signed certificate then you need to import it to the server certificate store, so that it can be trusted and verified.

To do so:

1.Export the self-signed certificate.

keytool -exportcert -alias com.sqlstream.client.jdbc -file com.sqlstream.client.jdbc.der  -keystore /usr/lib/jvm/java-8-oracle/jre/lib/security/cacerts

 

2.Convert it to from .der to .pem format.

openssl x509 -inform der -in com.sqlstream.client.jdbc.der -out com.sqlstream.client.jdbc.pem

 

3.Copy it to the certificate store

sudo cp com.sqlstream.client.jdbc.pem /etc/ssl/certs

 

4.Create a hashed link in the certs directory

cd /etc/ssl/certs

sudo ln -s com.sqlstream.client.jdbc.pem `openssl x509 -hash -noout -in com.sqlstream.client.jdbc.pem`.0

 

For more information on openSSL and certificates refer to the following link:

http://gagravarr.org/writing/openssl-certs/others.shtml#selfsigned-openssl

Check if your certificate can now be verified.

openssl verify [-CApath <ssl-base-dir>certs] com.sqlstream.client.jdbc.pem

 

If openSSL asks you to set -CApath set during verification, you likely need to set the verify_fail_if_no_peer_cert option in aspen.properties. Otherwise, the server may not locate the verification path and fail to verify the certificates.

Next, run the openSSL server  that requires the client to send certificates:

openssl s_server  -accept 9904 -key ./key.pem -cert ./cert.pem  -debug -msg -Verify 10 [ -CApath /etc/ssl/certs ]

 

The -Verify argument makes the client send the certificate and the openSSL server reject the connection otherwise.

Verify that the server requires the client to send certificates.

In a separate terminal run the openSSL client, using the real/verifable server certificates.

openssl s_client -connect localhost:9904 -cert cert.pem  -debug -msg -CApath /etc/ssl/certs

 

It should connect and display client certificate information towards the very end. Excerpts are below.

subject=/C=US/ST=CA/L=San Francisco/O=sqlstream.com/OU=development/CN=client.jdbc

issuer=/C=US/ST=CA/L=San Francisco/O=sqlstream.com/OU=development/CN=client.jdbc

 

Try the same command without appending -cert. It should fail to connect.

Connect via sqllineClient

./sqllineClient --tcpListenEnableSSL=true 9904

 

It should connect and use SSL with client certificates.

Start your server and verify that it requires client side certificates

Set sdp.ssl.client.verify_fail_if_no_peer_cert before starting the server.

Connect to your server using openssl s_client with and without -cert flags.

This will verify that your server requires client certificates.

Now, connect with sqllineClient. If it can connect, it means that the client certificates are exchanged and verified.

Note: When client side certificates are enabled you can control client access based on the SHA1 fingerprint of the certificate. See Accepting or Rejecting Clients Based on Certificate SHA1 Fingerprint below.

Importing Your Client side Certificate and Key into the Java Keystore

You need to import them via a pkcs12 keystore because the java keytool is not capable of importing both cert and key.  Please refer to the relevant sections of this document:

https://docs.oracle.com/cd/E19509-01/820-3503/6nf1il6er/index.html

Concatenate your cert and key into a single file referred as cert_key.pem below.

Then issue the following commands to import via a .pkcs12 store

cat mykey.pem mycertificate.pem >cert_key.pem

openssl pkcs12 -export -in cert_key.pem -out cert_key.pkcs12 -name com.sqlstrem.client.jdbc -noiter -nomaciter

keytool -importkeystore -srckeystore cert_key.pkcs12 -srcstoretype PKCS12 -destalias com.sqlstream.client.jdbc -destkeystore /usr/lib/jvm/java-8-oracle/jre/lib/security/cacert

 

Troubleshooting SSL and Certificate Issues

You can use the following system settings to generate debugging information

-Djava.security.debug=all

-Djavax.net.debug=all

 

Also, the server logs will contain traces using SdpVerifyCallBack tag.

Similarly, the client logs will contain traces with sun.security.ssl.

Passing a Private Key Password to s-Server

You can, if you choose, require a private key password for communication with s-Server.

To do so, use the following commands to generate a password protected private key.

openssl genpkey  -algorithm RSA  rsa_keygen_bits:3072 ec_paramgen_curve:secp256r1 -out server.key -passout -pass:"changeit" > key.pem

openssl rsa -des3 -in key.pem -out key.new.pem

cp key.new.pem key.pem

openssl req -new -key key.pem -out cert.csr -subj "/CN=testpassword2"

openssl x509 -req -days 3650 -in cert.csr -signkey key.pem -out cert.pem

 

Verify that you can't open it unless you have the password.

openssl rsa -noout -text -in server.key

 

Next, start the s-Server with the parameters above. If your server is started with incorrect password, all the SSL and network communication will be unusable.

Accepting or Rejecting Clients Based on Certificate SHA1 Fingerprint

When client side certificates are enabled you can control client access based on the SHA1 fingerprint of the certificate. s-Server will verify the certificate and if the fingerprint is in the list then the connection will be accepted. s-Server has the following setting for this purpose.

aspen.sdp.ssl.client.acceptFPs

 

Here, you can designate a comma separated list of SHA1 finger prints. It is a 160 bit(20 byte) number.

It needs to be encoded in hexadecimal with upper case letters., two letters/digit per byte.

aspen.sdp.ssl.client.acceptFPs=D9A5A4C4448C6FEA9865E2827A583F3ADE47F0A9

 

If the acceptFPs list is empty, then no fingerprint filtering will be done.

Other Settings for Client Communication with SSL

If you are using a version of openSSL other than the one installed on your system, or need to add some other certificate path, you may need to set it manually.

Pasted below are all the aspen.properties settings related to clients

# With SSL on, require the client certificate, reject the connection if it fails to verify

aspen.sdp.ssl.client.verify_fail_if_no_peer_cert=true

# The Certificate path to add to openssl in addition to the default path. Usually /etc/ssl/certs

# You may need to set this if you have a different openssl installation

# that doesn't use the default on the system

aspen.sdp.ssl.client.CApath=/etc/ssl/certs

# With client certificates enabled, trust all certificates regardless of verification.

# Primarily used during setup while testing certificates

aspen.sdp.ssl.client.tcpTrustAllSSL=true

# With client certificates enabled, accept only the client certificates with matching finger prints.

# A comma separated string of SHA1 fingerprints of the certificates, hex encoded with uppercase letters

# Eg: D9A5A4C4448C6FEA9865E2827A583F3ADE47F0A9

# Can be generated using: openssl x509 -noout -in cert.pem -fingerprint -sha1

# aspen.sdp.ssl.client.acceptFPs=

 

openSSL and Integrable Versions of s-Server

The integrable version of the product comes with openSSL 1.0.1. We recommend instead using the openSSL that comes on your target system. That way, you can update it using package tools like apt-get and yum.  Our product works with openSSL 0.9 through 1.0.2. The installable version of the product uses whatever openSSL is available on the system.

Note: This only applies in very limited situations; generally, you should know if your organization has integrated s-Server into their product.