There are two methods for connecting to ATP using a JDBC thin client. One is using Oracle wallet and the other is using java keystore. This post gives a summary of setting up JDBC thin connections using these methods. It is assume that the client credentials zip file is dowloaded from the ATP DB console.
Using Java KeyStore for JDBC Thin Connections
Using java keystore (JKS) is the simplest method for connecting a JDBC thin client to ATP. It requires no new libraries to be added to the classpath and if existing class conforms to using TNS entries then require no code changes either.
1. To use this method to connect to ATP DB following files are needed which are included in the client credentials zip file downloaded from the ATP DB console.
- truststore.jks
- keystore.jks
- ojdbc.properties
- tnsnames.ora
2. Modify the ojdbc.properties file to include JKS related entries (The original file in the client credentail zip will have additonal entries). This include specifying the locations of the two JKS files and their passwords. Password is the same password given when client credentials zip was downloaded from ATP DB console. Below is an example of ojdbc.properties file used in this method.
javax.net.ssl.trustStore=C:\\Asanga\\java\\atpjdbc\\Wallet_ATPFree\\truststore.jks
javax.net.ssl.trustStorePassword=<wallet password here>
javax.net.ssl.keyStore=C:\\Asanga\\java\\atpjdbc\\Wallet_ATPFree\\keystore.jks
javax.net.ssl.keyStorePassword=<wallet password here>
3. Next the location of the tnsnames.ora and ojdbc.properties files must be specified to the java app. This could be done by specifying the oracle.net.tns_admin system property, which could be passed on through the JVM options.
-Doracle.net.tns_admin=./Wallet_ATPFree
This would require no code changes. Other methods of specifying this location includes using the TNS_ADMIN in the JDBC URL (requies driver 18.3 or above) or using connection property OracleConnection.CONNECTION_PROPERTY_TNS_ADMIN. In this example location "./Wallet_ATPFree" containes tnsnames.ora and ojdbc.properties files.
4. With these files and enties in place create a JDBC thin connection using UCP as below. In this case the tpurgent service is used in the URL.
PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
ds.setConnectionPoolName("ATP_Pool");
ds.setURL("jdbc:oracle:thin:@atpfree_tpurgent");
ds.setUser("asanga");
ds.setPassword("pwd_here");
Using Oracle Wallet for JDBC Thin Connections
1. Using this method requires following files and library jars.
- ewallet.p12 and cwallet.sso
- ojdbc.properties
- tnsnames.ora
- oraclepki.jar, osdt_cert.jar and osdt_core.jar (not included in client credential zip file)
2. The wallet file is doesn't contain any passwords.
mkstore -wrl . -listCredential
Oracle Secret Store Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
However, it is possible to add a password to the wallet and use it for
passwordless login. What is contains is the certificate which allows to make TCPS connections. These certificates could be listed with following (redacted output shown).
orapki wallet display -wallet . -complete
Oracle PKI Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject: CN=............................,DNQ=V1
Issuer: C=US,ST=California,L=Redwood Shores,O=Oracle Corporation Autonomous Data Warehouse Cloud Self-signed CA,CN=Autonomous Data Warehouse Cloud CA
Serial Number: 00
Key Length 2048
MD5 digest: ....
SHA digest: ....
Trusted Certificates:
Subject: CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Issuer: CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Serial Number: ....
Key Length 2048
MD5 digest: ...
SHA digest: ...
Subject: C=US,ST=California,L=Redwood Shores,O=Oracle Corporation Autonomous Data Warehouse Cloud Self-signed CA,CN=Autonomous Data Warehouse Cloud CA
Issuer: C=US,ST=California,L=Redwood Shores,O=Oracle Corporation Autonomous Data Warehouse Cloud Self-signed CA,CN=Autonomous Data Warehouse Cloud CA
Serial Number: ...
Key Length 2048
MD5 digest: ...
SHA digest: ...
Subject: CN=DigiCert SHA2 Secure Server CA,O=DigiCert Inc,C=US
Issuer: CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Serial Number: ...
Key Length 2048
MD5 digest: ...
SHA digest: ...
As by default no password contains in the wallet, it must be specified in the JDBC connection. Secondly the auto login wallte included in the downloaded wallet zip file is not a auto login
local wallet. For added security a new auto login local wallet could be created. Password of the wallet is the same password given when client credentials zip was downloaded from ATP DB console
3. Modify the ojdbc.properties file to contain the following entry which specify the wallet file location.
oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\\Asanga\\java\\atpjdbc\\Wallet_ATPFree))))
4. Similar to JKS method, specify the location of the tnsnames.ora and ojdbc.properties files. This could be done by specifying the oracle.net.tns_admin system property, which could be passed on through the JVM options.
-Doracle.net.tns_admin=./Wallet_ATPFree
This would require no code changes. Other methods of specifying this location includes using the TNS_ADMIN in the JDBC URL (requies driver 18.3 or above) or using connection property OracleConnection.CONNECTION_PROPERTY_TNS_ADMIN. In this example location "./Wallet_ATPFree" containes tnsnames.ora and ojdbc.properties files.
5. Include the oraclepki.jar, osdt_cert.jar and osdt_core.jar files in the classpath of the java application.
6. With these files and entries in place same JDBC Connection code as shown above in JKS method could be used to make JDBC thin connections to the ATP DB. Example below shows password explicity being specified in the java code rathe than rely on password stoed in wallet.
PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
ds.setConnectionPoolName("ATP_Pool");
ds.setURL("jdbc:oracle:thin:@atpfree_tpurgent");
ds.setUser("asanga");
ds.setPassword("pwd_here");
