DOCUMENT:Q193225 01-MAR-2002 [iis] TITLE :How to Configure IIS to Connect to Oracle from ASP and ADO PRODUCT :Internet Information Server PROD/VER::2.0,2.1 SP2,4.0,6.0,Build 2.573.2927,Build 2.573.3513 OPER/SYS: KEYWORDS:kbDatabase kbOracle kbGrpDSASP kbGrpDSMDAC kbDSupport kbADO210sp2 ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Internet Information Server version 4.0 - Microsoft Visual Studio 6.0 - Microsoft ODBC for Oracle version 2.5, versions Build 2.573.2927, Build 2.573.3513 - ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2 ------------------------------------------------------------------------------- SUMMARY ======= The following article includes the recommended Oracle client software versions and ODBC driver versions recommended for use with Microsoft Internet Information Server (IIS) with Active Server Pages (ASP). Ensuring that the correct drivers are used and that the appropriate patches from Oracle are installed will help create a stable platform for your application. This article also includes a sample step-by-step guide on how to successfully set up an Internet Information Server computer for ASP or ADO connectivity to an Oracle Server. It is important to note that the sample setup guide is provided as an example and will not apply to every installation of Oracle client software. For more detailed assistance, please contact Oracle support. Recommended Software Versions ----------------------------- The recommended software versions for the clients are as follows: Microsoft Data Access Components 2.5 with Microsoft ODBC for Oracle driver Msorcl32.dll) version 2.573.4403.0 (or later) -or- The Microsoft OLEDB Provider for Oracle (msdaora.dll) version 2.50.4403.0 (or later) The following Oracle software versions (or later) are recommended: Oracle Server 7.3.3.0.0 7.3.4.0.0 8.0.3.0.0 8.0.4.0.0 ------------- --------- --------- --------- --------- RSF 7.3.3.5.3 7.3.4.2.0 8.0.3.2.3 8.0.4.3.2 SQL*Net 2.3.3.0.5 2.3.4.0.4 N/A N/A Net8 N/A N/A 8.0.3.0.4 8.0.4.0.3 Oracle updates and patches may be obtained from Oracle Support. The Microsoft Data Access Components (MDAC) 2.5 installation can be obtained at http://www.microsoft.com/data/mdac2.htm. IMPORTANT: Oracle client software (SQL*Net, Net8) is manufactured by Oracle, a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability. MORE INFORMATION ================ The following is a sample step-by-step setup guide to install the necessary software for client access to an Oracle database. This sample sets up SQL*Net version 2.3.3.0.0 with the required support files version 7.3.3.0.0 on a computer running Windows NT 4.0 Server. NOTE: For optimum results, it is recommended that you perform the following configuration on a fresh installation of Windows NT 4.0 (SP3) with Internet Information Server also installed. Installing the Microsoft Data Access Components 2.5 --------------------------------------------------- If you select Custom installation, make sure that you select the following: - ODBC Components - OLE DB Components - ODBC Driver for Oracle Databases - OLE DB Provider for Oracle Databases - Microsoft ActiveX Data Objects NOTE: The Microsoft Data Access Components 2.5 installation is available at http://www.microsoft.com/data/mdac2.htm. Installing the Oracle required support files and SQL*Net -------------------------------------------------------- Active Server Pages uses ActiveX Data Objects (ADO) to communicate to Oracle through Open Database Connectivity (ODBC). To enable this connectivity, install Oracle's client software on the Web server. 1. From your Oracle software compact disc, run Setup from the root directory. NOTE: If you are installing Oracle software from a network drive, you must map a network drive to install the Oracle software. 2. Select Language. 3. Select Installation Directory. You will be prompted that the install directory path information needs to be added to the registry. The Oracle installer will automatically do this for you. At this point, Setup will exit and you will have to restart again. Repeat the above steps. 1. Select Oracle7 Client Products. 2. Select Application User. 3. When you are prompted to install the Oracle documentation either on your hard disk drive or to read it from the compact disc, choose the appropriate location. The Oracle client Setup will now copy the files and configure the client software. To connect to an Oracle database using ODBC, you must supply a valid SQL*Net connect string. SQL Net Easy Configuration allows you to create an alias that you can reference in an ODBC Data Source Name (DSN) to connect to your Oracle Server. Adding a Database Alias ----------------------- 1. Run SQL*Net Easy Configuration. (Click Start, point to Programs, select Oracle for Windows NT, and then choose SQL Net Easy Configuration.) 2. Select Add Database Alias. 3. Type in a name for the alias (for example, the server name). 4. Select the protocol (most commonly, it will be TCP/IP). 5. Specify the host machicomputerne and the database instance. This is the name of the Oracle Server and the database on that server. If you do not know these values, get them from your Oracle Database Administrator (DBA). 6. Confirm the settings. 7. Select Exit SQL*Net Easy Configuration. Creating a System DSN --------------------- 1. On the Web server, from the Control Panel use the 32-Bit ODBC Administrator to add a System DSN. 2. Select Microsoft ODBC for Oracle version 2.573.2927. 3. In the Microsoft ODBC for Oracle dialogue, supply the required information. The user name will be provided by your Oracle DBA and the "Server" is the alias created with SQL Net Easy Configuration. Data Source Name : OracleDSN Description : Oracle Server Test on Server Name (optional) User Name : Server : Leave the options set to their default values. Connecting to the Oracle Server from ASP ---------------------------------------- You can create an Active Server Page with the following code to test your Oracle connection. Please ensure that the connect string has a valid user ID and password and that the SQL statement references a valid table. <%@ Language=VBScript %> Oracle Test
<% Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "dsn=OracleDSN;uid=userid;pwd=password;" Set objRs = objConn.Execute("SELECT * FROM DEMO.EMPLOYEE") Response.Write "" Response.Write "" For I = 0 To objRS.Fields.Count - 1 Response.Write "" Next Response.Write "" Do While Not objRS.EOF Response.Write "" For I = 0 To objRS.Fields.Count - 1 Response.Write "" Next Response.Write "" objRS.MoveNext Loop Response.Write "
" & objRS(I).Name & "
" & objRS(I) & "
" objRs.Close objConn.Close %>
For more information regarding Oracle and/or IIS, please see the following Microsoft Knowledge Base articles: Q189410 : HOWTO: Enable or Disable Connection Pooling in IIS 4.0 Q178391 : PRB: Oracle TNS: Authentication Service Initialization Error REFERENCES ========== Q60781 Hardware and Software Third-Party Vendor List, L-P Additional query words: kbDSupport ====================================================================== Keywords : kbDatabase kbOracle kbGrpDSASP kbGrpDSMDAC kbDSupport kbADO210sp2 Technology : kbVSsearch kbiisSearch kbAudDeveloper kbADOsearch kbADO200 kbADO210sp2 kbiis400 kbODBCSearch kbODBCOracle25732927 kbODBCOracle25733513 kbVS600 kbVS600Search kbODBCOracle250Search Version : :2.0,2.1 SP2,4.0,6.0,Build 2.573.2927,Build 2.573.3513 Issue type : kbhowto ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2002.