JDBC/ODBC SSL connection using self-signed SSL certificates

When you are operating in a secure environment, you need to think about mutual authentication with the server you connecting to and also encrypt all the messages going back and forth between the client and server. In Teiid, both JDBC and ODBC protocols support SSL based connections. Typically for development purposes you will not have CA signed certificates, and you need to validate with self-signed certificates. In article, I will show the steps to generate a self-signed certificate and then configuring them in Teiid. Then configuring the JDBC and ODBC clients with the defined SSL certificates to communicate with the Teiid server.

Creating self-signed certificates

If you do not already have it, download the "openssl" libraries for your environment. Follow the below script for creating the certificate(s).

Create root CA Certificate

To begin with, you need to generate the root CA key (this is what signs all issued certs), make sure you give a strong pass phrase.

openssl genrsa -des3 -passout pass:changeme  -out rootCA.key 2048
openssl rsa -passin pass:changeme -in rootCA.key -out rootCA.key

Generate the self-signed (with the key previously generated) root CA certificate:

openssl req -new -key rootCA.key -out rootCA.csr
openssl req -x509 -in rootCA.csr -key rootCA.key -days 365 -out rootCA.crt

You can install this on Teiid Server machine that will be communicating with services using SSL certificates generated by this root certificate. Typically, you’ll want to install this on all of the servers on your internal network.

To work with Teiid server, you need to import this certificate into keystore. Follow the below steps

openssl pkcs12 -export -in rootCA.crt -inkey rootCA.key -out rootCA.p12 -noiter -nomaciter -name root
keytool -importkeystore -destkeystore rootCA.keystore -srckeystore rootCA.p12 -srcstoretype pkcs12 -alias root

Generating client side certificates

Once you have the root CA certificate generated, you can use that to generate additional SSL certificates for other JDBC or ODBC and for other services.

1-WAY SSL

For 1-WAY SSL, we would need to extract rootCA’s trust certificate (public key) and create a keystore with it.

openssl x509 -trustout -in rootCA.crt > rootCA_trust.crt
keytool -importcert -v -trustcacerts -alias rootCA -file rootCA_trust.crt -keystore teiid.keystore
openssl x509 -in rootCA_trust.crt -out rootCA_trust.cer -outform der

Here we created keystore (teiid.keystore) that can be used with java based applications like JDBC driver, and also created certificate (rootCA_trust.cer) that can be used in Windows platform.

2-WAY SSL

for 2-WAY SSL, you would need an another certificate on client side. To create an SSL certificate you can use for one of your services, the first step is to create a certificate signing request (CSR). To do that, you need a key (separate from the root CA key you generated earlier). Then generate a CSR

openssl genrsa -out teiid.key 2048
openssl rsa -passin pass:changeme -in teiid.key -out teiid.key

Generate the self-signed certificate, and generate signed certificate using the root CA certificate and key you generated previously. Make sure the Common Name (CN) is set to the FQDN, hostname or IP address of the machine you’re going to put this on.

openssl req -new -key teiid.key -out teiid.csr
openssl x509 -req -in teiid.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out teiid.crt -days 365

Now you have an SSL certificate (in PEM format) called teiid.crt This is the certificate you want your JDBC or ODBC to use. Import this certificate into a existing key store or create a new one using

openssl pkcs12 -export -in teiid.crt -inkey teiid.key -out teiid.p12 -noiter -nomaciter -name teiid
keytool -importkeystore -destkeystore teiid.keystore -srckeystore teiid.p12 -srcstoretype pkcs12 -alias teiid
keytool -importcert -file rootCA_trust.crt -keystore teiid.keystore

Also, import the client certificate’s public key into rootCA keystore

openssl x509 -trustout -in teiid.crt > teiid_trust.crt
keytool -importcert -file teiid_trust.crt -keystore rootCA.keystore

I also found a great reference here [1] & [2] for certificate generation. Note in above that, I had issues with recognizing the PKCS12 formatted keystore in Java VM, I had to convert into a JKS format.

Configuring the Teiid Server with Certificates

  • Install Teiid server if you do not already have one.

  • Edit the standalone-teiid.xml file, and find "teiid" subsystem and inside find JDBC and ODBC transports and add as following.

<transport name="jdbc" socket-binding="teiid-jdbc" protocol="teiid">
    <ssl mode="enabled" authentication-mode="1-way">
        <keystore name="/path/to/rootCA.keystore" password="changeme" type="JKS"/>
      <!-- uncomment and configure for 2-way authentication
        <truststore name="/path/to/rootCA.keystore" password="changeme"/>
      -->
    </ssl>
</transport>
<transport name="odbc" socket-binding="teiid-odbc" protocol="pg">
    <ssl mode="enabled" authentication-mode="1-way">
        <keystore name="/path/to/rootCA.keystore" password="changeme" type="JKS"/>
      <!-- uncomment and configure for 2-way authentication
        <truststore name="/path/to/rootCA.keystore" password="changeme"/>
      -->
    </ssl>
</transport>

Then restart the server to start accepting the connections using SSL. Now server set up is complete.

Configuring JDBC client to use SSL

When using a JDBC client to use the SSL, copy the server.truststore file to the target machine. One of the main change is difference in JDBC connection URL you need to use. For example if your JDBC connection string is

jdbc:teiid:<vdb>:mm://<host>:31000

then change it to

jdbc:teiid:<vdb>:mms://<host>:31000

note "mm[s]" to represent [s] for secure. You also need to add the following system properties to your client for

1-WAY SSL

-Djavax.net.ssl.trustStore=/path/to/teiid.keystore
-Djavax.net.ssl.trustStorePassword=changeme
-Djavax.net.ssl.keyStoreType=JKS

2-WAY SSL

-Djavax.net.ssl.keyStore=/path/to/teiid.keystore
-Djavax.net.ssl.keyStorePassword=changeme
-Djavax.net.ssl.trustStore=/path/to/teiid.keystore
-Djavax.net.ssl.trustStorePassword=changeme
-Djavax.net.ssl.keyStoreType=JKS

The start your client application normally, that should make sure the SSL certificates used for encryption.

Configuring ODBC client to use SSL (Windows)

1-WAY SSL

  • Copy the "rootCA.crt" and "rootCA_trust.cer" files into your Windows machine into directory c:\Users\<yourname>\AppData\Roaming\postgresql. Note this directory may be hidden or non existent, if non-existent create a new folder. Note that if you are dealing with CA signed certificate, you do not have to share your private certificate "rootCA.crt". However since we are using self signed this will become the root certificate.

  • Rename "rootCA.crt" to "root.crt"

  • Rename "rootCA_trust.cer" to "postgresql.cer"

  • Now open the "ODBC Data Manager" application, create DSN for the connection you are ready to make using previously installed Postgres ODBC driver. Provide the correct host name and port (35432), and use VDB name as Database name, and select the "ssl-model" property to "verify-ca" or "verify-full" and save the configuration.

2-WAY SSL

  • Copy the "rootCA.crt", "teiid.crt", "teiid.key" files into your Windows machine into directory c:\Users\<yourname>\AppData\Roaming\postgresql. Note this directory may be hidden or non existent, if non-existent create a new folder. Note that if you are dealing with CA signed certificate, you do not have to share your private certificate "rootCA.crt". However since we are using self signed this will become the root certificate.

  • Rename "rootCA.crt" to "root.crt"

  • Rename "teiid.crt" to "postgresql.crt"

  • Rename "teiid.key" to "postgresql.key"

  • Now open the "ODBC Data Manager" application, create DSN for the connection you are ready to make using previously installed Postgres ODBC driver. Provide the correct host name and port (35432), and use VDB name as Database name, and select the "ssl-model" property to "verify-ca" or "verify-full" and save the configuration.

  • Now use any ODBC client application/tool like (QTODBC) and make ODBC connection using the DSN created and start issuing the SQL queries.

results matching ""

    No results matching ""