Hardening s-Server and Configuring SSL

For many purposes, running SQLstream s-Server single user with no password is fine when inside a firewalled environment.

If you need JDBC access over the internet or you want to run with multiple users with restricted privileges, some of the following steps should be taken to harden s-Server:

You can also use Sandboxing in s-Server to further limit what users can access.

Setting a Password for the "sa" Account

Set a password for the “sa” account. This is the default, and administrative, account for s-Server. To do so:

  • Run sqllineClient or SQLline and connect to s-Server.
  • Enter the following:

Configuring s-Server Clients to Use a Password

Once you set a password for this user, you will need to configure s-Server clients to use a password. To use sqllineClient, you will need to start it using

sqllineClient –PASSWORD


sqllineClient –PASSWORD="aPassword”.

You’ll also need to specify user and password in any SQLstream JDBC URLs, along the lines of: See the topic SQLstream JDBC Driver for more details.

When connecting to s-Server through sqlline, you can specify a username with -n and a password by passing -p For s-Studio, you can enter a user name and password in the Connection Properties dialog box. See Entering a JDBC URL for s-Studio.

To use StreamLab or s-Dashboard after adding user passwords, you will need to set a default user name and password through webAgent.

webAgent has command line arguments for setting the default user/password. See Command Line Arguments for webAgent in the Integrating with Other Systems*.

Creating Other Users and Granting Them Privileges

You can create other users and grant them privileges by following the instructions below. If you want a more controlled experience for your users, you may want to set up sandboxing.



This will create a new non-administrator user that by default will not have access to any system tables or functions (even in non-sandboxed environments). Only the creator of an object or schema (or the administrator) is allowed to drop or alter it.

Granting Users Permissions

Administrators can use the following SQL to grant access to foreign streams and tables, as well as system tables and streams or views based on system tables and streams:

GRANT <permission> { , <permission> }
TO <user>;

Administrators can use the following SQL to grant access to system functions:


List of Permissions

Permission Repository objects to which permission applies
Select Tables, streams, views
Insert Tables, streams
Update Tables
Delete Streams
Execute user-defined routines, user defined functions, user-defined procedures, or user-defined transforms)

Configuring s-Server to Use SSL

You can 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 section covers the following topics:

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:


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

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.custom.properties. aspen.sdp.tcpListenEnableSSL=true
  2. Start sqllineclient with the following parameters: /opt/sqlstream/<VERSION>/s-Server/bin/sqllineClient --tcpTrustAllSSL=true --tcpListenEnableSSL=true 9904 We recommend placing $SQLSTREAM_HOME/bin in your PATH. If you do so, you can run the command above as sqllineClient --tcpTrustAllSSL=true --tcpListenEnableSSL=true 9904 Once connected, all communication between SQLline client and s-Server will be encrypted via SSL.

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

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

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

  1. After changing aspen.sdp.tcpListenEnableSSL in aspen.custom.properties, restart 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
 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
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/ 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. 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:


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 aspen.properties:


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.

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:

  • Make sure that s-Server is SSL enabled.
  • Store your certificate under the alias com.sqlstream.client.jdbc
  • 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:

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

Convert it to from .der to .pem format.

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

Copy it to the certificate store

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

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.

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


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.


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.


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

# 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
# With client certificates enabled, trust all certificates regardless of verification.
# Primarily used during setup while testing certificates
# 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.

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