We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Host string to connect to remote Oracle 9i server from limited user account

smidgie82
smidgie82 asked
on
Medium Priority
5,737 Views
Last Modified: 2011-08-18
I have an Oracle 9i server setup on a private network.  My users have to VPN into this client network to get to my server.  That part is working fine.  However, because this is for a department at a University, all of my users will be connecting from lab machines operated by our Computer Services department, which has file permissions locked down.  Specifically, neither I nor my users have permissions to edit the tnsnames.ora file, which does not include any references to my oracle instance.

Can I setup my listener.ora file on the server so that users can connect using username/password@192.168.0.60 and be connected to the "OracleDB" database instance?

Here is my current listener.ora file:

# LISTENER.ORA Network Configuration File: c:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = blade10)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = OracleDB)
      (ORACLE_HOME = c:\oracle\ora92)
      (SID_NAME = OracleDB)
    )
  )
Comment
Watch Question

Database Administrator
CERTIFIED EXPERT
Commented:
No, you need to modify one (or more) tnsnames.ora file(s) to allow a client to connect to the "OracleDB" alias in the listener.ora file.  Depending on how the client machines are set up, this could be just one common, shared tnsnames.ora file, or each of the lab machines could have their own tnsnames.ora file.

It is probably easiest, simplest and most-secure to use just one, shared tnsnames.ora file from a network location that the lab machines have read (but not write) access to.  Either you or someone must have write permissions on this networked tnsnames.ora file to add an entry pointing to your Oracle database.  Then you just add a registry entry to each of the lab machines pointing to this file.  The registry entry must be named: "TNS_ADMIN" and be located in: "Hkey-Local-Machine, Software, Oracle".  It must contain the full path and filename of the networked tnsnames.ora file.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Unfortunately, I don't have write access to that registry key, and Computer Services isn't willing to either A) give me write access or B) create that value and push it out on a domain-wide basis.  So, that solution doesn't work.  However, while searching on your TNS_ADMIN reg key, I came across another solution: the TNS_ADMIN environment variable.  Just set the user's TNS_ADMIN environment variable to point to a folder holding custom tnsnames.ora and sqlnet.ora files for my server, and everything works great.

Thanks for the pointer!
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
Yes, if you can set an environment variable, that ought to work fine.  I'm glad I was able to help get you going in the direction you needed.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.