Remote Access to the Data Warehouse
The Data Warehouse is accessible to authorized users connecting via Penn's
on campus Ethernet network, or remotely either through Penn's modem services
or a commercial Internet Support Provider (ISP). Since the Data Warehouse
is protected by the Penn network firewall, individuals (who do not use
Business Objects) wishing to query the Warehouse via a commercial ISP
need to establish a secure connection. Windows users should follow the
steps described below to do this.
NOTE: Individuals using Business Objects Web Intelligence or InfoView do not need any additional local configuration.
· Obtain Access to an SSH Server
· Configure SQL*Net for Port Forwarding
· Connect to the Data Warehouse
· Web-based Queries and Reports
Obtain Access to an SSH Server
Data Warehouse queries and results are transmitted over SQL*Net, which
is Oracle Corporation's software that enables clients and servers to connect
to each other, send SQL statements and data, and disconnect when the session
is complete. Since the Warehouse will only recognize logins and queries
over SQL*Net from a secured server, remote users must employ a secure
shell (SSH) client and port forwarding through an SSH server. While other
SSH clients may provide the same features, Van Dyke's SecureCRT is the
Penn supported SSH client for the Windows platform.
The Penn installation of SecureCRT is preconfigured with session profiles
for SSH servers on campus. If you have access
to one of the preconfigured servers (such as an email account on one of
the mail servers) you can follow the steps below for setting up Secure
CRT for port forwarding. Individuals who do not have access to an SSH
server should contact Data Administration for assistance.
Setting up SecureCRT for port forwarding:
- Follow SecureCRT configuration instructions.
- Launch SecureCRT. The Connect window will open with a list of servers.
- Right-click on the appropriate profile. (If you do not have access
to any of the servers listed, contact Data Administration.) Select Properties
from the Right-Click menu.
- Click Advanced button, and select Port Forwarding tab.
- In the Local port field, type 1521.
- In the Remote hostname field, type warehouse.isc.upenn.edu.
- Make sure that 1521 has appeared in the Remote port
- Only 'Use single SSH connection' should be checked, unless you need
to have multiple telnet windows open. (Leave 'Forward X11 packets' unchecked).
- Click the Save button - you should now see those settings in
the window under the word Current.
Note: You can also follow the above steps to establish port forwarding
for other Penn secured Oracle databases. You will need to modify the appropriate
alias in the SQL*Net Easy Configuration application, as directed below.
Configure SQL*Net for Port Forwarding
These instructions assume that you are already authorized to access the
Data Warehouse, and have the necessary Oracle software installed on your
PC. For more information about the Data Warehouse, access forms and query
tools, consult the Data Warehouse web pages.
- Launch the SQL Net Easy Configuration application. This can be found
in your Oracle for Windows 95 (or Oracle for Windows NT) program group,
accessible from the Windows Start menu.
- Select "Modify Database Alias", and then select whse from the
list of aliases, and click OK. [If you have not yet created an
alias for the Warehouse, select "Add Database Alias" and enter the settings
- Settings should be adjusted as follows:
Database Alias (no change): whse
TCP/IP Host Name (was warehouse.isc.upenn.edu): 127.0.0.1
Database Instance (no change): whse
- Click OK once settings are modified, and then Yes to
confirm changes (or Back to make corrections).
- Select "Exit SQL*Net Easy Configuration", and click OK, and
Note: You can also follow the steps above to modify an alias for
other Penn secured Oracle databases. You will need to enter the appropriate
database address in the SecureCRT Port Forwarding tab, however.
Connect to the Data Warehouse using SecureCRT Port Forwarding
Once you have configured SecureCRT and SQL*Net for port fowarding, you
can initiate your remote connection.
- Launch SecureCRT.
- Select the session profile of your choice (FINPROD if you're a BEN
Financials user, or your mail server, if available), and click OK.
- If prompted, click Accept & Save to accept the key for
- Type your user ID in the Username field (for the server to which you're
connecting, not your Warehouse ID) and click OK.
- Type the password in the Password field and click OK.
- Once logged in to your session, you may minimize the window (while
staying logged in), and then launch your SQL*Net based application (such
as Business Objects, GQL, Access/ODBC, SQL*Plus) as usual, to connect
to the Data Warehouse. You may not disconnect from your SecureCRT session
until you have logged off from the Warehouse and the port forwarding
feature is no longer in use.
If you use the same PC on campus and remotely you will need to perform
these steps any time you query the Warehouse, whether on campus or off-campus.
You may still use your Ethernet connection instead of the modem, but will
need to have the SecureCRT connection established.
Web-based Queries and Reports
Web query and reporting applications, such as BEN Reports, SAS's IRQDB,
Wharton's InfoMart or Business Objects Web Intelligence and InfoView may be used
from any ISP with no SecureCRT or other configurations required. Since
the SQL*Net communications for these applications happens between the
web server and the Data Warehouse (not the client and the Warehouse),
port forwarding is unnecessary to use those services through an ISP. If
you do have a SecureCRT open concurrently, the connection will still work