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:
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center