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 (I am using Fedora 22 for my testing). 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 certifiacte 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"> <authentication security-domain="teiid-security"/> <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"> <authentication security-domain="teiid-security"/> <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
for 2-WAY SSL add additional properties
-Djavax.net.ssl.keyStore=/path/to/teiid.keystore -Djavax.net.ssl.keyStorePassword=changeme
The start your client application normally, that should make sure the SSL certificates used for encryption.
Configuring ODBC client to use SSL (Windows)
- Install the Postgresql ODBC driver in your Windows machine. You can download the driver from http://www.postgresql.org/ftp/odbc/versions/
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.
That's it, if you have questions please be sure to leave comments or suggestions.
Enjoy.
Ramesh..
[1] Problem with SSL certificate setup
[2] Creating a Self-Signed SSL Certificate | Heroku Dev Center
Comments