How to configure TCPS in JDBC thin client for oracle

certificateencryptionjavajdbcoracle

An Java Application running with JDBC thin wtih TCP connection, Now require is to convert in TCPS for encryption transmission from application to database.

String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername)(PORT=XXXX))(CONNECT_DATA=(SERVICE_NAME=XXXX)))");

with using JKS truststore for certificate;

Here we havn't using sqlnet.ora file for setup TCPS encryption. Can anyone help for same that what are changes require in JDBC,Ciper, certificate etc ?

Best Answer

To setup TCPS with JDBC, we have a few tasks.

First, create the wallet in both client and server:

orapki wallet create -wallet "wallet" -pwd XXXXXXXX -auto_login

Then, create self-signed certificates (or signed by a trusted CA)

orapki wallet add -wallet "wallet" -pwd XXXXXXXX -dn "CN=%COMPUTERNAME%" -keysize 1024 -self_signed -sign_alg sha256 -validity 3650

Exchange via SFTP/SCP and import each other certificate in the wallet

orapki wallet export -wallet "wallet" -pwd XXXXXXXX -dn "CN=%COMPUTERNAME%" -cert ./%COMPUTERNAME%.crt

orapki wallet add -wallet "wallet" -pwd XXXXXXXX -trusted_cert -cert %REMOTECOMPUTER%.crt

Now indicate in SQLNET.ORA the location of the wallet (in TNS_ADMIN folder, in both client and server).

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = D:\oracle\wallet)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

Now edit listener.ora in the server to add the secure endpoint (PROTOCOL = TCPS):

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mydbservername)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mydbservername)(PORT = 2484))
    )
  )

Restart the listener with lsnrctl stop, start and status to check that your line is showing up.

Update the TNSNAMES.ORA accordingly in both client and server.

MYDB_SSL=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCPS)
      (HOST=MYDBSERVERNAME)
      (PORT=2484)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=REP3)
    )
  )

The TNS_ADMIN folder in the client has to be the same used for the wallet, so place your sqlnet.ora and tnsnames.ora in that folder only.

If everything is good so far, tnsping MYDB_SSL will return OK.

D:\>tnsping MYDB_SSL

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 05-FEB-2021 13:10:59

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
D:\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCPS) (HOST=MYDBSERVERNAME) (PORT=2484)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB)))
OK (1100 msec)

Otherwise, check lsnrctl on the server side, find the log and troubleshoot.

On the java side, set the JDBC URL as per the example below (for Spring/JPA):

spring:
  jpa:
    database: oracle
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle12cDialect
  datasource:
    url: jdbc:oracle:thin:@MYDB_SSL?TNS_ADMIN=\\oracledb\\wallet

Load the certificates in the cacerts with the keytool command:

keytool -importcert -file %ORACLE_BASE%\%CN%.crt -keystore %CACERTS% -storepass changeit -alias %CN%

Run the app, you may specify the TNS_ADMIN in the command line too:

%JAVA% -Doracle.net.tns_admin=%ORACLE_BASE%\wallet -jar myapp.jar...

Hope this helps...

Related Question