-
Title
How to configure the Stat Central Agent for TCPS connectivity. -
Description
The scope of this article is to enable TCPS communication between Stat and Oracle DB 12c and higher. The most important steps are described in the Oracle Support notes Doc ID 2280787.1 , Doc ID 2732618.1 , and Doc ID 2674842.1 which give the main steps to configure the wallet and certificates created by using the orapki utility, the listener, the tnsnames.ora and the sqlnet.ora for both sides database server and client, so that the communication happens from sqlplus.
-
Cause
The Stat Central Agent cannot connect through TCPS to any environment. -
Resolution
1. Create the wallet and certificates
Database Server side:
- Create a directory for your database server wallet.
C:\app\wallet
- Create an auto-login wallet
orapki wallet create -wallet "c:\app\wallet" -pwd Starwars14Stat -auto_login
- Create a self-signed certificate and load it into the wallet
orapki wallet add -wallet "c:\app\wallet" -pwd Starwars14Stat -dn "CN=%computername%" -keysize 2048 -self_signed -validity 3650
orapki wallet display -wallet "c:\app\wallet" -pwd Starwars14Stat
- Export the certificate so that you can load it into the client’s wallet later.
orapki wallet export -wallet "c:\app\wallet" -pwd Starwars14Stat -dn "CN=%computername%" -cert "c:\temp\%computername%".crt
Oracle Client side:
- Create a Client Wallet and Certificate. Follow the step 1,2,3 above.
orapki wallet create -wallet "c:\app\wallet" -pwd Starwars14Stat -auto_login
orapki wallet add -wallet "c:\app\wallet" -pwd Starwars14Stat -dn "CN=%computername%" -keysize 2048 -self_signed -validity 3650
orapki wallet display -wallet "c:\app\wallet" -pwd Starwars14Stat
orapki wallet export -wallet "c:\app\wallet" -pwd Starwars14Stat -dn "CN=%computername%" -cert "c:\temp\%computername%".crt
- Exchange Client and Server Certificates:
orapki wallet add -wallet "c:\app\wallet" -pwd Starwars14Stat -trusted_cert -cert "C:\temp\stat-dbsca.crt"
orapki wallet add -wallet "c:\app\wallet" -pwd Starwars14Stat -trusted_cert -cert "C:\temp\graiano-win2019.crt"
- orapki wallet display -wallet "c:\app\wallet" -pwd Starwars14Stat
Note: the *.crt files under Temp need to be moved under C:\app\wallet directories on both sides.
2. Now we have to configure the listener and the sqlnet.ora on the DB server, then restart the listener.
Listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.250.36.60)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = 10.250.36.60)(PORT = 1531))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
WALLET_LOCATION =(SOURCE =(METHOD = FILE)(METHOD_DATA = (DIRECTORY = C:\app\wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE
Sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES = (TCPS, NTS, BEQ)
NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME)
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\app\wallet)))
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
3. At the client side, we need to configure the sqlnet.ora and the tnsnames.ora.
Sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, EZCONNECT)
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\app\wallet) ) )
# SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
Tnsnames.ora:
ST63S = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS) (HOST=10.250.36.60)(PORT=1531))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ST63)))
4. At this point you should be able to connect to the database from sqlplus. If this doesn’t work, you must to fix it before going ahead with the others steps.
C:\sqlplus system/,password>@ST63S
5. Once your client/server is secure and works we can implement it by using an external application like a java program that uses a JDBC connection with TCPS.
Oracle offers some java program demo that you can test before configuring Stat Central Agent. Here is a simple java example:
import java.util.*;
import java.sql.*;
//import java.security.Security;
public class TestOracleConnection
{
public static void main(String args[])
{
try
{
String dbHost = null;
String dbPort = null;
String dbProtocol = null;
String dbName = null;
String dbUserId = null;
String dbUserPassword = null;
String dbConnectionString = null;
String strSQLStatement = null;
Scanner user_input = new Scanner( System.in );
System.out.print("Enter DB Host Name: ");
dbHost = user_input.next( );
System.out.print("Enter DB Port: ");
dbPort = user_input.next( );
System.out.print("Enter DB Protocol (TCP or TCPS): ");
dbProtocol = user_input.next( );
System.out.print("Enter DB Name: ");
dbName = user_input.next( );
System.out.print("Enter DB User Id: ");
dbUserId = user_input.next( );
//System.out.print("Enter DB User Password: ");
char[] pwd = System.console().readPassword("Enter DB User Password: ");
dbUserPassword = new String(pwd);
//System.out.print(dbUserPassword);
//step1 load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = null;
//step2 create the connection object
//dbConnectionString = "jdbc:oracle:thin:@" + dbHost + ":" + dbPort + ":" + dbName;
//dbConnectionString = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=" + dbHost + ")(PORT=" + dbPort + "))(CONNECT_DATA=(SERVICE_NAME=" + dbName + ")))";
dbConnectionString = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=" + dbProtocol + ")(HOST=" + dbHost + ")(PORT=" + dbPort + "))(CONNECT_DATA=(SERVICE_NAME=" + dbName + ")))";
System.out.println(dbConnectionString);
//"jdbc:oracle:thin:@psfprfha.fhlmc.com:1521:PSFPRFHA"
//connection = DriverManager.getConnection(dbConnectionString, dbUserId, dbUserPassword);
//Security.addProvider(new oracle.security.pki.OraclePKIProvider());
Properties props = new Properties();
props.setProperty("user", dbUserId);
props.setProperty("password", dbUserPassword);
props.setProperty("javax.net.ssl.trustStore", "C:/Oracle/wallet/cwallet.sso");
props.setProperty("javax.net.ssl.trustStoreType","SSO");
//props.setProperty("javax.net.ssl.trustStorePassword","changeit");
//props.setProperty("javax.net.ssl.trustStore", "C:/Oracle/keystore_save/nonprod_freddiemac.jks");
//props.setProperty("javax.net.ssl.trustStoreType","JKS");
//props.setProperty("javax.net.ssl.trustStorePassword","changeit");
//props.setProperty("javax.net.ssl.keyStore", "C:/Oracle/wallet/cwallet.sso");
//props.setProperty("javax.net.ssl.keyStoreType","SSO");
connection = DriverManager.getConnection(dbConnectionString, props);
//step3 create the statement object
Statement stmt=connection.createStatement();
//step4 execute query
strSQLStatement = "select * from PS.PSDBOWNER";
ResultSet rs=stmt.executeQuery(strSQLStatement);
ResultSetMetaData rsmd = rs.getMetaData();
while(rs.next())
{
System.out.println("");
System.out.println(rsmd.getColumnName(1) + " " + rsmd.getColumnName(2));
System.out.println("======= ========" );
System.out.println(rs.getString(1) + " " + rs.getString(2));
System.out.println("");
}
//step4 execute query
//rs=stmt.executeQuery("select USER As \"USER\", to_char(current_timestamp, 'DD-MON-YYYY HH24:MI:SS') AS \"DATETIME\", ORA_DATABASE_NAME AS \"DATABASE_NAME\", SYS_CONTEXT('USERENV','INSTANCE_NAME') AS \"INSTANCE_NAME\", SYS_CONTEXT('USERENV','SERVER_HOST') AS \"SERVER_HOST\" from dual");
strSQLStatement = "select"
+ " ORA_DATABASE_NAME AS DATABASE_NAME"
+ " ,USER AS USERNAME"
+ " ,to_char(current_timestamp, \'DD-MON-YYYY HH24:MI:SS\') AS \"DATE_TIME\""
+ " ,SYS_CONTEXT(\'USERENV\',\'INSTANCE_NAME\') AS INSTANCE_NAME"
+ " ,SYS_CONTEXT(\'USERENV\',\'SERVER_HOST\') AS HOST_NAME"
+ " ,SYS_CONTEXT(\'USERENV\',\'SERVICE_NAME\') AS SERVICE_NAME"
+ " ,SYS_CONTEXT(\'USERENV\',\'NETWORK_PROTOCOL\') AS PROTOCOL from dual";
rs=stmt.executeQuery(strSQLStatement);
while(rs.next())
{
System.out.println("");
System.out.println(rsmd.getColumnName(1) + " " + rsmd.getColumnName(2) + " " + rsmd.getColumnName(3) + " " + rsmd.getColumnName(4) + " " + rsmd.getColumnName(5) + " " + rsmd.getColumnName(6) + " " + rsmd.getColumnName(7));
System.out.println("============= =========== ==================== ============= =============== ============= ========" );
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5) + " " + rs.getString(6) + " " + rs.getString(7));
System.out.println("");
}
//step5 close the connection object
connection.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
6. If the above java code works, let’s configure Stat. On the SCA machine make the settings below and restart the Stat Central Agent.
- edit the standalone.bat adding the parameters below in the JAVA_OPTS:
rem Setup JBoss specific properties
set "JAVA_OPTS=-Dprogram.name=%PROGNAME% %JAVA_OPTS% -Doracle.net.tns_admin=C:\app\homes\OraDB21Home1\network\admin -Doracle.net.wallet_location=C:\app\wallet"
- Under SCA_HOME\app\module\oracle\jdbc\main copy oraclepki.jar, osdt_cert.jar, and osdt_core.jar. If using jdbc8 add also the related ojdbc8.jar
- Edit the module.xml like below:
<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="oracle.jdbc">
<resources>
<resource-root path="ojdbc8.jar"/>
<resource-root path="oraclepki.jar"/>
<resource-root path="osdt_cert.jar"/>
<resource-root path="osdt_core.jar"/>
</resources>
<dependencies>
<module name="javax.api"/>
<module name="javax.transaction.api"/>
</dependencies>
</module>
7. In the Stat Client add a new DB entry within PS, Oracle, or Generic applications. In this case, we use a Generic Application set like below:
8. Perform the Test Connection:
