Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to connect ASP to remote Oracle database?

Posted on 2005-02-24
15
Medium Priority
?
1,310 Views
Last Modified: 2008-01-09
Good day. I am having difficulties in accessing remote Oracle database from a ASP page. I installed Oracle9i Client - Administrator option from the CD in the web server but the problem still remained. When I try to connect to the database, Internet Explorer took very long time to load and eventually it came out "The page could not be displayed" error without any specific detail.

Here's the ASP code to connect to the database:

Function GetDBConnection()
  Dim objConnection

  Set objConnection=Server.CreateObject("ADODB.Connection")
  With objConnection
    .ConnectionString=      "Provider=MSDAORA.1; " & _
                  "data source=mydb; " & _
                  "User ID=username;" & _
                  "Password=password;"
    .open
  End With
  Set GetDBConnection=objConnection
End Function


Surprisingly, when I use a desktop program to access another remote Oracle db, the program can be run properly. Just that the web application on the same machine did not function well when accessing remote Oracle database. I checked tnsnames.ora and the parameter is correct.

Any idea?
0
Comment
Question by:cwchan80
  • 5
  • 3
  • 2
  • +2
13 Comments
 
LVL 6

Expert Comment

by:hans_vd
ID: 13390640
ASP code is executed in your webserver.  Can you connect to Oracle from the machine where your web server runs?
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13390696
See that your Data Source (mydb) is connecting to properly to the server or not. Otherwise use "Oracle in OraHome92". For using this you will have to create a TNS entry for your server (Using Net Configuration Assistant from your start menu). Test the Service before using it.
0
 

Author Comment

by:cwchan80
ID: 13390728
Hi hans_vd, that's the problem. I cannot connect to remote Oracle db from the web server (WS1) machine. Wondering what went wrong.

I use the same piece of source code and run it on another ASP server with Oracle database (not client) installed and it's working. So, I suspect that I misconfigured or forgot to install something in WS1 to allow web application to run properly.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cwchan80
ID: 13390813
Hi sujit kumar, I used Net Configuration Assistant and I tested the service at the end of the setup wizard and it's successful.

This is my tnsnames.ora:

MES2PNG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.26.35)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mes2png.domainname.com)
    )
  )

0
 
LVL 6

Expert Comment

by:hans_vd
ID: 13391001
If you can set up the machine your web server is running on in the same way as the machine you can start the other desktop programs that connect to Oracle from, your problem might be solved.
0
 

Author Comment

by:cwchan80
ID: 13391228
I try not to install entire Oracle database into the problem web server due to licensing issue. Is it due to Oracle Net configuration?
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13391269
Then use Oracle in OraHome92 ODBC connection (Oracle OLEDB Provider) rather than using Microsoft Driver for Oracle.
0
 
LVL 9

Expert Comment

by:joaoalmeida
ID: 13391367

Do you have a sqlnet.ora file on the some dir that tnsnames.ora?

What are the contents?

0
 

Author Comment

by:cwchan80
ID: 13391387
Hi Sujit Kumar, how to use Oracle in OraHome92 ODBC connection?

I tried another provider:

Dim OraSession
Function GetDBConnection()
      Set OraSession = CreateObject("OracleInProcServer.XOraSession")
      Set GetDBConnection = Orasession.OpenDatabase("mydb", "username/password", CInt(0))
End Function

Is it the driver provided by Oracle? I tried but no luck as well. The page took forever to load.


0
 

Author Comment

by:cwchan80
ID: 13391435
This is my sqlnet.ora file:

==========================================================
# SQLNET.ORA Network Configuration File: D:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

#NAMES.DEFAULT_DOMAIN = domain1.com

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)
==========================================================

This is my tnsnames.ora file:

==========================================================
# TNSNAMES.ORA Network Configuration File: D:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

mydb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.26.35)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mydb.domain2.com)
    )
  )
==========================================================

Note that the remote database server is at different domain versus the web server. Does it have something to do with the problem?


0
 
LVL 9

Expert Comment

by:joaoalmeida
ID: 13391449

comment the SQLNET.AUTHENTICATION_SERVICES= (NTS) line.

0
 
LVL 9

Accepted Solution

by:
joaoalmeida earned 140 total points
ID: 13391467

If stil dont work try in tnsnames.ora

mydb.domain2.com =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.26.35)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mydb)
    )
  )

And in the ASP


With objConnection
    .ConnectionString=     "Provider=MSDAORA.1; " & _
               "data source=mydb.domain2.com; " & _
               "User ID=username;" & _
               "Password=password;"


0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 140 total points
ID: 13392518
Let's eliminate the ASP code for the moment.

From a command prompt on the web server try:
tnspint <dbalias>
where <dbalias is the alias speciified in the tnsnames.ora file on the web server.

also, is there a firewall between the web server in question and the DB server?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question