Setup of a secure JDBC Connector to Oracle

I've seen a few post on the TDI forum in regards to this subject, so here are my notes I've pulled together when setting up TDI with a secure Oracle jdbc connection.

Setup of the Oracle Wallet

To enable SSL in Oracle, a Certificate for the Oracle Server signed by a Certificate Authority(CA) is required.  In my environment, I created a Self Signed CA which will issue the certificate to the Oracle Server.

• If you already have a CA to process your Certificate Request, then ignore steps 1, 4, 5 & 6 steps

1.  Download and unpack the ssl helper scripts named ssl.ca-0.1.tar.gz from the OpenSSL > Contributions page.

2.  Open Oracle Wallet Manager and create a new wallet and certificate request.

3.  Export the certificate request to a file. Give it a .csr extension

 4. Move the certificate request to the directory containing the openSSL certificate authority scripts (e.g. /usr/src/crytpo/openssl/apps/ssl.ca-0.1) from Step 1.

5. Create a self-signed root certificate by running the new-root-ca.sh script. (You only need to run this command once). This will create a file called ca.crt

6. Use the CA to sign the certificate request... e.g. 

# sign-server-cert.sh <certificate-request-filename>. This will create a file called <certificate-request-filename>.crt

7. Import the ca.crt into the Oracle wallet as a trusted certificate. Import the <certificate-request-filename>.crt as a user certificate.

8. Enable auto-login and save the wallet. It is now ready for use.

Setting Up Oracle Server to use the SSL/Wallet.

I found it easier to use the Oracle GUI (item b), then modifying the files directly (item a).   

Both sets of instructions are provided, although I used the instructions in item b to setup my test environment.

a.  Modify Oracle ORA files to point to the Wallet.  (wp-oracle-jdbc-thin-ssl-130128.pdf)

b.  Using the Network Configuration Manger to setup SSL:

Configuring the LISTENER

ALSO review the following... 

1. Make sure the WALLET is mentioned in the sqlnet.ora & listener.ora files.
2. If the SSL_CLIENT_AUTHENTICATION parameter is not set, the default setting is TRUE and clients are required to present a certificate during the SSL handshake.  For my test environment I did not need client authentication, so I disabled it with the following parameter.
   
   Add the following line to the end of both files..$TNS_ADMIN/listener.ora and $TNS_ADMIN/sqlnet.ora
   
   SSL_CLIENT_AUTHENTICATION=FALSE

Verifying the SSL Connection outside of TDI, with the 'Oracle SQL Developer'

a.  The 'Oracle SQL Developer' GUI uses a JVM on the system.  For the JVM, it uses "C:\app\administrator\product\11.2.0\dbhome_1\jdk\bin\java.exe", so you'll need to add the CA Certificate to the jvm\jre\lib\security\cacerts jks file.  Other wise you'll receive a connection error regarding an untrusted certificate.  The default password for cacerts is 'changeit'

b.  Create the JDBC URL

jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST = <hostname>)(PORT = <sslport>))) (CONNECT_DATA = (SERVICE_NAME = <service_name>))(SECURITY=(SSL_SERVER_CERT_DN=\"CN=<.......>\")))

Verifying the JDBC URL

I had issues getting the correct url...the 'Service Name' gave me problems when trying to test the connection via the 'Oracle SQL Developer'.  Here's a few screen shots of the 'Net Manager' which I reviewed to help me correct my URL.

Compare these 3 files on the Oracle Server if you are having problems with the connection.  

C:\app\administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN

• Successfully connect to the database using the secure jdbc url in the Oracle SQL Developer client before proceeding to setup the TDI JDBC Connector.

TDI Server Side Configuration

In this environment, I used the Oracle Thin JDBC client.

• For TDIv6.1.1(JRE 1.5) use the Oracle Thin Client (ojdbc5.jar)
• For TCIv7.0 or 7.1(JRE 1.6) use the Oracle Thin Client (ojdbc6.jar)
• No additional Oracle files are on the TDI machine

TDI SSL Configuration

We must update the jks file used by the TDI Server to include the CA which signed the certificate for the Oracle Server.

1. Obtain the public certificate of the CA.  Contact the CA for this process.
2. Use the ikeyman tool (<TDI_HOME>/jre/bin/ikeyman) to import the CA public certificate into the jks file defined in your solution.properties file.
   To determine which JKS file you need to update, you can find the name of the file by reviewing the following section in the solution.properties file.
   
   ## server authentication
   javax.net.ssl.trustStore=serverapi/testadmin.jks
   javax.net.ssl.trustStorePassword=
   javax.net.ssl.trustStoreType=jks
   ## client authentication
   javax.net.ssl.keyStore=serverapi/testadmin.jks
   javax.net.ssl.keyStorePassword=
   javax.net.ssl.keyStoreType=jks
3. After you have updated the jks with the CA public certificate, restart TDI so the updated jks is used by the JVM.

TDI JDBC Connector Setup:

• First, setup the connector to use a non-secure connection to make sure you can connect to the database.
  
  JDBC URL
  jdbc:oracle:thin:@<HOST>:<PORT>:<service_name>
• Second, use the following url skeleton to build your url:
  SECURE JDBC URL
  URL : jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST = <hostname>)(PORT = <sslport>))) (CONNECT_DATA = (SERVICE_NAME = <service_name>))(SECURITY=(SSL_SERVER_CERT_DN=\"CN=<.......>\")))
  
  (SECURITY=(SSL_SERVER_CERT_DN=\"CN=<.....>\")) is optional and is used only for server DN matching.
  
  I found this link discussing Oracle Thin connection to be informative.
  http://docs.oracle.com/cd/E12839_01/web.1111/e13737/third_party_drivers.htm#i1050126 

Example:

jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST = hostname)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = orcldb)))

Extra Provider Parameters

oracle.net.ssl_version:3.0

//The following two parameters were optional when connecting to my Oracle Server.  These two plus additional parameters maybe required depending on your Oracle Server configuration.

oracle.net.ssl_cipher_suites:SSL_RSA_WITH_RC4_128_MD5,SSL_RSA_WITH_RC4_128_SHA,SSL_RSA_WITH_AES_128_CBC_SHA

oracle.net.ssl_server_dn_match:FALSE

Experience with the TDI CDE

I've ran into problems when attempting to use the CDE, so I wanted to post incase it helps anyone else.

TDI Component Development Environment (CDE) is a tool that enables an effective, efficient and consistent way of creating TDI components. It abstracts and automates functions, tasks and complexities of developing TDI components.  TDI CDE can be used by a customer to quickly develop new custom components with minimal effort. This helps build better TDI solutions enabling creation of custom TDI components by the customer for easy use. -- Source url:  http://www.ibm.com/software/brandcatalog/ismlibrary/details?catalog.label=1TW10DI0R#tab-pinfo

Video:

Discussion on connector development: http://www.youtube.com/watch?v=4aiqj7lhbrg

Known Issues I've seen with CDE:

• Start Eclipse with the 'Run as Administrator' option, else when you use the 'File / Export / IBM Tivoli Directory Integrator / Deployable Connector' function in Eclipse to move the resource to TDI, the export windows will complete..although the resource will not be found in the TDI/jars directory.  The Export windows seems to flash to quick to see if any error is given during the export.
• In Eclipse, I found the 'Java' not 'Java EE' perspective should be used.  If the 'Java EE' perspective is used, then the error 'The selected resource is not a valid IBM Tivoli Directory Integrator project' is seen when attempting to export the developed resource.
• I could not find it documented in the CDE documentation on which JDK is required.
  Unexpected behavior was seen when using a non-IBM JDK. 
• • The 'Finish' button had to be pressed multiple times to confirm completion.
  • The Project structure was random and incomplete.

Google Voice (SMS) with TDI

Recently I've been working on an Assemblyline line which needed to send sms notification.  As most know, TDI already provides a sendMail() method in its UserFunction class to send email.  Using this method allows messages to be sent to phone numbers which have a service provider email address (e.g. 5552224444@txt.att.net).

However some providers like Google Voice(GV), do not provide this number@email option.
So with this limitation it took a little searching to find another option.

I found by using the google-voice-java API (api docs) we can send sms messages via TDI directly to a phone number, instead of relying on a phonenumber@email address.

JARs are available at:
http://code.google.com/p/google-voice-java/downloads/list

At the time of this posting, there were 5 jars (google-voice-java-1.14-java6.jar, dom4j-1.6.1.jar,
jaxen-1.1.3.jar, json.jar, jtidy.jar).
Simply add the jars to the <TDI>\jars\3rdparty\others\GoogleVoice directory

You can then use the following function from your Resource/Script library to to send sms from TDI by the way of Google Voice.



/*
 * sendGVsms uses the api made available at
 * http://code.google.com/p/google-voice-java/w/list
 */
function sendGVsms(dst,txt){
    try{
        destinationNumber = dst;
        txt = txt;
        username = system.getTDIProperty("gvuserName")
        pass = system.getTDIProperty("gvpass")
        var gvoice = Packages.com.techventus.server.voice.Voice(username,pass);
        gvoice.sendSMS(destinationNumber,txt);
        } catch (e) {
            task.logmsg(">>sms error " +e);
    }
}


NOTE:  You will need to add Google's  public certificate to the TDI truststore, otherwise you'll receive a 'java.lang.reflect.InvocationTargetException' when calling the class.

I used the following information to obtain and add the certificate to the TDI truststore.

1. Obtaining the Google certificate
a. 


b. 


c.

d.  Choose a 'Save as type' of DER when exporting the certificate.

2. Adding the certificate into the default TDI truststore.
By default, a TDI 7.x installation uses the serverapi/truststore.jks as its truststore.
This file location may vary depending on if you have a specific Solution Directory, or if the TDI Install directory is the Solution directory.  You can refer to the contents of the <TDI_Install>/bin/defaultSolDir.bat to determine the default Solution directory.

The TDI Component as a Client section in the TDI Infocenter discusses the importation of a certificate.
Here's an example of the command to import the google certificate into the testadmin.jks file.

<TDI>/jvm/jre/bin/keytool -import -trustcacerts -file google.der -keystore testadmin.jks -storepass administrator -alias googleCert