Troubleshoot Enforce connection to Oracle

book

Article ID: 192817

calendar_today

Updated On:

Products

Data Loss Prevention Enforce Data Loss Prevention Data Loss Prevention Enterprise Suite Data Loss Prevention Plus Suite Data Loss Prevention Oracle Standard Edition 2 Data Loss Prevention Core Package

Issue/Introduction

When looking at the connection between the Enforce Server and the Oracle Server there are 3 main files you will want to work with.

Oracle:

  • tnsnames.ora
    • Path: C:\oracle\product\12.2.0.1\db_1\network\admin\tnsnames.ora
    • This is the original source for the tnsnames.ora file. 

 

Enforce:

  • tnsnames.ora
    • C:\Oracle\client\Administrator\product\12.2.0\client_1\network\admin\tnsnames.ora
    • This is primarily used for Upgrades and testing.
  • jdbc.properties
    • C:\Program Files\Symantec\DataLossPrevention\EnforceServer\15.7\Protect\config\Jdbc.properties
    • This is the primary source of information for the day to day connections for Oracle.

Resolution

  •  

TNSPING:

To start our troubleshooting we should start with a basic "tnsping" from the Enforce Server. This command runs completely outside of DLP, so this is a good way to test your connection to the Oracle Sever while completely removing DLP from the equation. It is important to note that this command runs through the Oracle Client (which should be installed on the Enforce Server if the Oracle Server is not on the same machine). The "tnsping" command pulls its connection data from the "tnsnames.ora" file that is on the Enforce Server. If this command does not work for any reason then the first step should be to copy the "tnsnames.ora" file from the Oracle Server and paste it into the path specified above for the tnsnames.ora file on the Enforce Server.

Syntax: tnsping <service_name>

What we are looking for is that last line that says "OK (30 msec)", this tells us that the ping was successful, and that it took 30 milliseconds.

 

SQLPlus:

The next step would be to try logging into the Oracle Server through SQLPlus, this is included with the Oracle installation, or with the Oracle Client installation.

1. Start by trying to log into Oracle from the Oracle box.

Syntax: conn [email protected]_identifier

  • use the command "sqlplus /nolog" to log into sqlplus without a user.
  • use "conn [email protected]"
  • This should show "Connected" after you enter your password if it is successful.
  • If it is not successful then we now know the issue is on the database side and not related to DLP as we are on the local Oracle Server.

2. The next step is to login to Oracle from the Enforce Server.

Syntax: conn [email protected]_identifier

  • use the command "sqlplus /nolog" to log into sqlplus without a user.
  • use "conn [email protected]"
  • This should show "Connected" after you enter your password if it is successful.
  • If it is not successful then we now know the issue is with the communication between the Enforce Server and the Oracle Server.
    • You should see an error that should provide you with more specific details about the failure.
  • This is the exact same process as Step#1, the only difference is that we are attempting to connect from the Enforce Server instead of the Oracle Server.

3. Connect using the jdbc.properties connection string.

Syntax: conn [email protected]_string

  • You will notice that this process is very similar to the previous steps, we have simply replaced the "connection_identifier" with the "connection_string" found in the jdbc.properties file on the Enforce Server.
  • Open the jdbc.properties file on the Enforce Server and scroll to the very bottom.
    • You will see an entry similar to the following...
    • jdbc.dbalias.oracle-thin=@(description=(address=(host=10.255.1.53)(protocol=tcp)(port=1521))(connect_data=(service_name=protect)))
    • The "connection_string" in this case would be defined as everything including and after the @ symbol.
    • connection_string = @(description=(address=(host=10.255.1.53)(protocol=tcp)(port=1521))(connect_data=(service_name=protect)))
  • use the command "sqlplus /nolog" to log into sqlplus without a user.
  • use "conn [email protected](description=(address=(host=10.255.1.53)(protocol=tcp)(port=1521))(connect_data=(service_name=protect)))"
  • This should show "Connected" after you enter your password if it is successful.
  • If logging in with the connection string fails, then you will need to review the files(tnsnames.ora, jdbc.properties) and the connection string to determine what is wrong with your connection string and correct any problems.
  • If you cannot connect with the connection string, then Enforce will not be able to communicate with the database and you would be unable to login.

Attachments