Version 4

    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)

     

    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

    [3] ODBC Using Windows Cert Store - PostgreSQL wiki