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:

1. Follow SecureCRT configuration instructions.
2. Launch SecureCRT. The Connect window will open with a list of servers.
3. 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.
4. Click Advanced button, and select Port Forwarding tab.
5. In the Local port field, type 1521.
6. In the Remote hostname field, type warehouse.isc.upenn.edu.
7. Make sure that 1521 has appeared in the Remote port field.
8. Only 'Use single SSH connection' should be checked, unless you need to have multiple telnet windows open. (Leave 'Forward X11 packets' unchecked).
9. 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.

1. 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.
2. 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 below.]
3. 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
4. Click OK once settings are modified, and then Yes to confirm changes (or Back to make corrections).
5. Select "Exit SQL*Net Easy Configuration", and click OK, and OK again.

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.

1. Launch SecureCRT.
2. Select the session profile of your choice (FINPROD if you're a BEN Financials user, or your mail server, if available), and click OK.
3. If prompted, click Accept & Save to accept the key for your host.
4. Type your user ID in the Username field (for the server to which you're connecting, not your Warehouse ID) and click OK.
5. Type the password in the Password field and click OK.
6. 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 properly.