How To Troubleshoot an ASP-to-Oracle Connectivity Problem

This article outlines some of the common steps to take when you troubleshoot a problem with a connection to an Oracle Database from an Active Server Pages (ASP) application. Some of the more common error messages are:

The following is a list of general steps to take when you investigate a connectivity problem between ASP and an Oracle database.

1. First verify that you can connect to Oracle and retrieve data using Oracle's SQL Plus utility (a command line-based query utility). If you cannot connect from this tool, then you either have a bad installation or configuration of the Oracle client components or you did not correctly use the SQL Net Easy Config or Oracle Net8 Easy Config utility to create an alias for the Oracle server. You must work with your Oracle database administrator to make sure that the necessary Oracle components have been installed and are configured correctly.
2. If the Oracle client was recently installed on the Internet Information Server (IIS) computer, make sure that the computer has been rebooted at least once. In some cases after installation, the Oracle component SQL Plus works fine, but connectivity from IIS does not work until you reboot the computer.
3. Look for multiple copies of the OCIW32.dll file on the IIS server. There should only be one copy of this .dll file on the computer, and it should be in the \Bin subfolder of the Oracle home folder. If you find other copies, rename these with a .bak extension and then retest connectivity. You may want to restart the IIS services after renaming any additional copies of the .dll file that you found.
4. Make sure that you are running the recommended versions of the Oracle components.
5. For more information on how to obtain and install these patches, work with your database administrator or contact Oracle Technical Support. (Microsoft Technical Support does not support the configuration of these Oracle components.
6. Check to make sure that the Oracle client components are installed locally on the IIS server. While it is possible to get other applications to work without the Oracle components installed locally, this causes problems when you work with ASP applications. Microsoft recommends that these components be installed on the IIS server; other custom configurations are not supported.
7. Look at the system environment variables on the IIS computer and check the PATH variable. If there are any remote share names or mapped drives that point to locations that contain Oracle components, remove them. You must reboot the computer in order for any changes that you make to take effect.
8. Look again at the system environment variables on the IIS computer and check the PATH variable. Make sure that the \Bin folder in the Oracle home directory is included as part of the PATH environment variable. With a default installation, this is the Orant\Bin folder. Again, you must reboot the computer for the changes that you make to take effect.
9. Try to connect with both the Microsoft ODBC for Oracle driver and the Microsoft OLEDB Provider for Oracle. Here are sample connection string syntax for each of these:

   'Microsoft OLEDB Provider for Oracle
   "Provider=MSDAORA.1;Data Source=Your_TNSNames_Alias;User ID=User;Password=Password"
   
   'Microsoft ODBC for Oracle DSN-Less 
   "Provider=MSDASQL;DRIVER={Microsoft ODBC for ORACLE};UID=User;PWD=Password;Server=Your_TNSNames_Alias"
   
   'Microsoft ODBC for Oracle using a DSN
   "DSN=Your_DSN_Name;UID=User;PWD=Password"
   					

10. Disable SQL*Net Authentication. To do this, edit the SQLNET.ora file. This configuration file is usually stored in the Network\Admin subfolder of the Oracle home folder. Add the following lines to this file:

    SQLNET.AUTHENTICATION_SERVICES = (none)
    SQLNET.AUTHENTICATION = (none)
    						

    Restart the IIS service after you make these changes.
11. Add the IUSR_machine name account and the IWAM_machine name account to the Oracle home folder. Give both accounts full control.
    
    To do this, right-click on the Oracle home folder in Windows Explorer. Click Properties, and then click the Security tab. Add the appropriate accounts and then give them full control. Restart your computer.

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.