How to connect ASP to remote Oracle database?

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?
cwchan80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hans_vdCommented:
ASP code is executed in your webserver.  Can you connect to Oracle from the machine where your web server runs?
0
sujit_kumarCommented:
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
cwchan80Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cwchan80Author Commented:
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
hans_vdCommented:
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
cwchan80Author Commented:
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
sujit_kumarCommented:
Then use Oracle in OraHome92 ODBC connection (Oracle OLEDB Provider) rather than using Microsoft Driver for Oracle.
0
joaoalmeidaCommented:

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

What are the contents?

0
cwchan80Author Commented:
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
cwchan80Author Commented:
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
joaoalmeidaCommented:

comment the SQLNET.AUTHENTICATION_SERVICES= (NTS) line.

0
joaoalmeidaCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.