Using Oracle Thin Driver with Client/Server Authentication SSL

Oracle Database server supports SSL on the wire encryption plus client and server authentication. This can be a bit tricky to set up and after much exhaustive searching I've never found a complete description of the steps to set up the client-side configuration (or at least, using a Tomcat Resource).

The following instructions describe how I set up SSL authentication (/encryption) from a Tomcat WebApp to Oracle Database server.

You must use a recent OJDBC6.JAR. Older versions (I can't work out which) have a bug relating to parsing passwords from connection properties. Download the latest OJDBC6 or OJDBC7 from Oracle. Place ojdbc6.jar into tomcat/lib.

You will need the "keytool" from the Java JDK or JRE.

Create a new keystore with self-signed certificate:

keytool -genkey -alias %computername% -keystore keystore.jks -storepass changeme -validity 3650

When prompted you probably want to use your machine name for the "What is your first and last name" (the CN= bit of the addressing).

Export the self-signed certificate:

keytool -export -keystore keystore.jks -storepass changeme -alias %computername% -file %computername%.cer

Provide this to your Oracle DBA who will import the certificate into the database trust store (wallet). The DBA should provide you a certificate chain for the server. Import these into your Java keystore:

keytool -importcert -noprompt -keystore keystore.jks -storepass changeme -file SERVER.CRT

In your Tomcat server.xml create a new Resource entry under GlobalNamingResources:

	<GlobalNamingResource>
	<Resource name="jdbc/mydb"
	auth="Container"
	type="oracle.jdbc.pool.OracleDataSource"
	factory="oracle.jdbc.pool.OracleDataSourceFactory"
	driverClassName="oracle.jdbc.pool.OracleDataSource"
	url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=servername)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=sid)))"
	user="username"
	password="password"
	maxActive="25"
	maxIdle="4"
	maxWait="10000"
	removeAbandoned="true"
	removeAbandonedTimeout="60"
	logAbandoned="true"
	connectionProperties="javax.net.ssl.trustServerCertificate=false;javax.net.ssl.trustStore=C:\\path\\to\\keystore;javax.net.ssl.trustStoreType=JKS;javax.net.ssl.trustStorePassword=changeme;javax.net.ssl.keyStore=C:\\path\\to\\keystore;javax.net.ssl.keyStoreType=JKS;javax.net.ssl.keyStorePassword=changeme;"
	/>

view raw server.xml hosted with ❤ by GitHub

In your WebApp's context.xml create an appropriate mapping:

	<ResourceLink
	name="jdbc/mydb"
	global="jdbc/mydb"
	type="javax.sql.DataSource"
	/>

view raw context.xml hosted with ❤ by GitHub

You can then connect to and use your new database connection using:

	Context initCtx = new InitialContext();
	DataSource ds = (DataSource) initCtx.lookup("java:comp/env/jdbc/mydb");
	conn = ds.getConnection();

view raw index.jsp hosted with ❤ by GitHub

Errors

It is generally easier to debug SSL configuration problems using the sqlplus client tool. You will need an Oracle wallet (orapki tool) to do this, which I won't cover in this blog post. The following may help diagnose problems with your client configuration though.

• Format error - check connectionProperties doesn't contain spaces/newlines
• IO Error: The Network Adapter could not establish the connection - 1) check your have the correct passwords for trustStorePassword and keyStorePassword 2) try a newer version of ojdbc6.jar / confirm you are using the verison you expect 3) this is a genuine network/hostname problem 4) Ensure oracle.net.wallet_location isn't specified in catalina.properties or elsewhere (seems to override connectionProperties)
• IO Error: NL Exception was generated - check server.xml's resource url attribute is formatted correctly