Skip to main content

[Oracle] Steps to validate that the Oracle Driver is installed correctly on the web application server

Rosemary Schatz
  • Updated

Applies to DSP all versions and CranSoft all versions.

1.  Verify that the Oracle Driver is installed by looking at the environment variable PATH.

A)  On the web application server, run a Command Prompt as administrator.

B)  Type the command path in the Command Prompt.

C)  In this example, the Oracle Path is highlighted by the red box.

D)  The Oracle directory should be a part of the path, normally at the beginning of the path.  If there is more than one Oracle Path, please review the Multiple Oracle Drivers Installed.

2. Verify that the web app server has been rebooted since the Oracle Driver install.

A) On the web application server, run a Command Prompt as administrator.

B) Type the command systeminfo in the Command Prompt screen.

C) You will see a variety of Loading statements which will write over themselves and then the screen will scroll with the System Information.

D) Compare the System boot time to the creation date on the Oracle Folder from the PATH command above.

3. Verify IUSER and IIS_IUSRS have READ and EXECUTE permissions on the Oracle folder and that the sub-folders have inherited these permissions.

A) On the web application server, using Windows Explorer go to the Oracle Driver Folder.  (The following examples is using a MicroSoft SQL database instance.  If it's an Oracle instance, please go to the folder with the version number that you are installing.)

B) Right click on the folder.

C) On the Security tab, click the Advanced button near the bottom right.

D) Validate the the IUSR and IIS_IUSRS groups have Read & Execute rights.

E) Click the Edit button on the Advanced Security Settings.

F) Verify that the above check box outlined in redis checked.  If it hasn't been checked but the IUSR and IIS_USRS groups have Read & Execute on the original folder, please click the check box and click the Apply button.

4. Verify that the files TNSNAMES.ORA and SQLNET.ORA exist.

A)  Edit the TNSNAMES.ORA to see the settings for the Oracle Database.

Example of an entry:

TEST =(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)

(HOST = THEHOST.COM)(PORT = 1521)))

(CONNECT_DATA =(SERVICE_NAME = TEST) ) )

ORALegacy.OLDCOMPANY.COM =(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)

(HOST = THEHost.com)(PORT = 1521)))

(CONNECT_DATA =(SERVICE_NAME = TEST) ) )

B)  Edit the SQLNET.ORA:

    1. Verify that NAMES.DIRECTORY_PATH includes TNSNAMES or LDAP.  CranSoft/DSP cannot connect via Onames or EZConnect.
    2. For Windows 2008 and 2003, validate that the value on the right side of the following statement is (NTS).
      SQLSQLNET.AUTHENTICATION_SERVICES=(NTS)
    3. For Windows 2012, validate that the value on the right side of the statement is (NONE).
      SQLSQLNET.AUTHENTICATION_SERVICES=(NONE)

5. Validate that the web app server has the 32-bit Oracle Admin Driver installed and can communicate to the Oracle Database server.

A) On the web application server, run a Command Prompt as administrator. 

B) Type the following in the Command Prompt screen:

tnsping Oracle Server Alias Name or Oracle Server IP Address

C) In this example, the Oracle Server Alias Name is BOAORA1:

D)  The following information can be obtained by the TNSPING command:

 

    • Oracle Driver Software type:  32-bit or 64-bit.  Both CranSoft and dsp require the 32 bit driver.
    • Oracle Version:  Version 11.1.0.7.0 (for example).  This is important to know.  Please go to this link to learn about the different Oracle Drivers and which one would work best for your Oracle database and CranSoft/dsp needs: Oracle Driver Versions Information
    • Result of the TNSPING.:  
      1. If TNSPING is not a known command, then that means the Oracle Thin Client has been installed.  We require the Administrator version of the Oracle Driver.  Uninstall the Oracle Thin Client and install the Administrator version.  Be sure that the server is rebooted after the Oracle Administrator version is installed.
      2. If you do not receive a reply back from the Oracle database server, please go to this link for steps on how to resolve that issue. Steps to Follow if Oracle TNSPING fails

6. Connect with SQLPlus:

A) Connect to the Oracle Database using the UserID and Password that will be/was entered in CranSoft/dsp:

    1. In a  command prompt, enter the following command: SQLPLUS userid/Password@TNSNAME (NOTE:  Replace TNSNAME with the name to the left of the = sign in the TNSName.ORA file)
    2. If the login fails, this means that it's an incorrect UserId or Password. Validate with the DBA, the UserID/Password.

B) If the login succeeds, run the following command to verify that the userid/password has access to a table:

SELECT COUNT(*) FROM “SCHEMAOWNER”.”TABLENAME”

7. Add the DataSource into dsp/CranSoft using this document for guidance Steps for adding an Oracle Database into dsp/CranSoft

 

Related articles