EPM - Editing 'tnsnames.ora' and 'sqlnet.ora' Files

This document describes how to edit tnsnames.ora and sqlnames.ora files in order to connect to EPM.

Background:

The tnsnames.ora file is where the Oracle Client stores database connection entries. These entries contain information that allows the ODBC driver to establish network connections to an Oracle Database, including hostname and port number. Users must create an entry in this file for each database they wish to access. The syntax for connection entries is strictly defined, so it's important to make sure they are formatted correctly.

The sqlnames.ora file is where the Oracle Client stores various network connection settings which apply to ODBC connections. Like tnsnames.ora, entries in the file follow a rigid format. In order to connect to EPM, users must add lines permitting an encrypted connection.

Copies of the tnsnames and sqlnet entries used to connect to the EPM database are included in the initial authorization email EPM users receive. This document describes in detail how to insert those entries into the corresponding files and how to determine whether they were inserted correctly.

Directions:

  1. Begin by clicking on Start > All Programs > Accessories

  2. Right-click on Notepad and select Run as administrator

    notepad_as_admin.png
  3. In Notepad, click on File > Open. Switch the dropdown menu in the lower right hand corner of the screen from Text Documents (*.txt) to All Files

  4. Locate the folder where you installed the Oracle Client (ORACLE_HOME). Then open the following folders: product > 11.2.0 > client_1 > network > admin

    open_tnsnames.png

  5. If files named tnsnames.ora or sqlnet.ora already exist in the folder, open them. Before making any changes to the files, click File > Save As... and save them as tnsnames.ora.old and sqlnet.ora.old (be sure to switch the Save as Type: dropdown box to read All Files before saving). In doing this, you are creating backups of the original files in case you need to revert to the older versions at some point. If neither of these files already exists, hit Cancel to return to the Notepad editor.

  6. Copy and paste the tnsnames connection entry you received in the 'Connecting to EPM Database via Remote Connections' email into the Notepad document. If you are editing an existing tnsnames.ora file, append the entry to the end of the file. Click File > Save As... and save the file as tnsnames.ora. If you are prompted to replace the existing file, click 'Yes'.

  7. Copy and paste the sqlnet connection entry you received in the 'Connecting to EPM Database via Remote Connections' email into the Notepad document. If you are editing an existing sqlnet.ora file, append the entry to the end of the file. Click File > Save As... and save the file as sqlnet.ora. If you are prompted to replace the existing file, click 'Yes'.

  8. To test your the ODBC connection, open a command prompt (Click Start, type 'cmd' in the search bar, and hit Enter). At the command prompt, enter the following command: 'tnsping connection_name'. For example, if you wish to test the connection to the EPQAS test database, type 'tnsping epqas'. You should see the following output:

    tns_ping.png

    If the output you see differs from the above, please see EPM - Common ODBC Errors for information about typical ODBC errors and solutions.