?
Solved

How to connect ASP to remote Oracle database?

Posted on 2005-02-24
15
Medium Priority
?
1,304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
15 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
Independent Software Vendors: 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 77

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

765 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