Client Access ODBC is a great way to connect to your AS/400 and retrieve data. ActiveX Data Objects (ADOs) an be used as an easy interface to the ODBC API; they allow you to make dynamic Web pages that retrieve or update data on your AS/400. However, if you want these pages to run on an unattended Microsoft Windows NT server without a user logged into the NT system, you must set up Client Access/400 to run as a service. This article shows you how.
Requirements
You must be running the Client Access/400 for Windows 95/NT client (V3R1M2 or greater with the appropriate service packs installed) and Windows NT Version 4.0 for this to work. I run this code on OS/400 V3R7M0, so it should work on all RISC-based AS/400s. It might even work on OS/400 V3R2, provided you have the most recent Windows 95/NT client. Also required are administrator-level privileges on your NT box and access to the Windows NT REGEDIT program. Finally, you need the Client Access CWBCFG utility, which should reside in your Client Access directory on the Windows NT server. This technique works only if you use TCP/IP to connect to your AS/400.
What Is a Service?
A service is a special kind of program that can run under the Windows NT environment. Service programs usually require no interaction with the desktop or user of the system. In fact, services can be set up to run once the computer is started, whether a user is logged into the system or not. You can even write your own service programs using tools such as Visual Basic to provide networked OLE automation.
Some well-known examples of Windows NT service programs are the following:
• TCP/IP
• Internet Information Server (IIS)
• FTP
• Print and media sharing
• Microsoft’s SQL Server If you want IIS to be able to access AS/400 data in a standalone manner, you must set up CA/400 so that it can be called by a service.
Setting Up the Server
First, you need to log into the Windows NT box as a user with administrator-level privileges. The account name is usually Administrator, but most shops rename this account during their initial system setup for security reasons.
Once you log in, you must decide if you want to run CA/400 from a user account or the system account. It makes no difference to CA/400; it just depends on how you want to manage your NT accounts. Some service programs, such as IIS, can only run under the system account. The program that you are running determines which of the following steps you will need to complete.
Setting Up to Run Under the System Account
Let’s assume that you need IIS to access the AS/400. IIS must be run under the system account. This requirement dictates that CA/400 must be enabled to use the system account. Since you cannot configure or log into the system account directly, IBM has provided the CWBCFG utility.
Log into your NT box with administrator- level privileges and initiate an MS-DOS command prompt session. From the command window, type the following:
CWBCFG /host
In this example,
Alternate Setup
Of course, you may have an in-house service program that you want to connect to your AS/400 with a specified Windows NT user account. Since this setup gets a little more complicated, I recommend that you use the system account method just described. But for you die-hard masochists, here goes!
From Windows NT, log into the AS/400 as the victim—er...intended user account—and use the AS/400 connection program to create a connection to the target AS/400 system. Make sure that you select Connect Only as the default view. Once you have a connection, you must use the REGEDIT utility to add a key and value to the Windows Registry. This new key and value is what will cause CA/400 to be run as a service under this account. Remember, editing the Windows Registry with the REGEDIT utility can be quite dangerous, not to mention downright disastrous! Be extremely careful and be sure to follow these directions exactly. It is wise to back up the Registry before you attempt any REGEDIT activity.
Press Start and select Run. In the prompt window, type REGEDIT and press OK. The wonderfully obscure registry window should appear. Move your mouse to the folder marked HKEY_Current_User and click. More folders should appear. Now select Software, then IBM, then Client Access, then CurrentVersion, then
Internal_Components, then Communication, then Environments, and finally Primary Environment. Whew, that was a lot of clicking! To make sure you are in the right place, compare your screen to Figure 1.
You should now see your AS/400 system name displayed under this comprehensive list of open folders. In Figure 1, my system is the folder named S100521M under the Primary Environment folder. Click to open your system folder; you should see a set of keys that are set up for your AS/400. (What you see should be similar to what is shown in Figure 2.) Click the Edit menu, select New, and then select the DWORD value. These selections create a new registry entry called New Value #1. With the mouse highlighting New Value #1, use the right mouse button to select the Rename menu option. You should now change the name of New Value #1 to ServiceConfig.
With the mouse pointer over the ServiceConfig option, press the right mouse button and select Modify from the pop-up menu. A screen similar to that shown in Figure 3 should appear. Place the number 1 into the Value data field and press OK.
With these entries, CA/400 can now run as a service under this NT user account. However, you must use the Control Panel/Services application to select the service and configure the target service to use the Windows NT account that you have just modified. Figure 4 contains a representation of a service being configured from the Services Control Panel application.
Configuring the ODBC Data Source
Now that you have set up CA/400 to run as a service, you must set up an ODBC data source for Client Access to use. Use the Control Panel and start the ODBC Administrator utility. When you click the System DSN tab and then click the Add button, a screen called Create a New Data Source appears. Select the Client Access 32-bit driver and press Finish. You should now be in the Client Access ODBC Setup screen.
On the General tab (shown in Figure 5), be sure to set the Data Source Name field with the name you will use to refer to this ODBC connection. If you want to, you can also set the default user ID along with your AS/400 system name. However, when initiating an ODBC connection from a service, the calling program must specify the user ID and password.
You can ignore most of the other setup tabs, as the default values usually supply what you need. I do suggest that you use the Server tab to set the library list that the data source should use. Doing so allows you to specify the order and libraries that this data source will see when it asks for available databases and tables. Once you have finished configuring your data source, press the OK button at the bottom of the ODBC configuration screen. This action will save the data source you have just created.
A discussion of the configuration would not be complete without a word about security and Active Server Pages (ASP). When creating an account that will access this data source, you need to heavily restrict the abilities of the account. I suggest that you grant and revoke AS/400 table privileges for the ASP account using SQL. In this way, you can grant only the privileges needed on the tables or columns required by your ASP script. ASP scripts and NT security leave open the possibility that a good hacker can download the ASP script and see your data source name, user ID, and password if the WWW server is not secured correctly. If you heavily restrict the account, it won’t matter if someone finds an account password; he won’t be able to hurt you anyway. You should also look into a secure firewall if your network is connected to the Internet. TCP/IP connectivity means that anyone can send you packets or connect to your AS/400 unless you take proactive steps to prevent it.
Testing for Connectivity
If you have IIS or Microsoft Personal Web Server, you can use the CONTEST.ASP script shown in Figure 6 to test for connectivity. Place the script into the root Web directory and make sure that the directory is configured with Enable Scripts to Execute authority. Edit the script and change the Datasource, Userid, and Password text fields to an appropriate data source, user ID, and password. For the data source, you can specify the ODBC data source that you configured in the previous section.
Once you’ve placed the script in the directory and made these changes to the text fields, attempt to open the page in a Web browser using the Uniform Resource Locator (URL) http://localhost/contest. asp. If you see a list of AS/400 libraries, the program is connecting to the AS/400.
To test connectivity as a server, log off your Windows NT session and go to another machine to attempt to open the page. The URL will be http://yourmachinename/ contest.asp. Again, if the page appears with a list of libraries, you have successfully connected. Now that you are connected, you may want to delete the CONTEST.ASP script. If someone gains access to the script, they can find your user ID and password. See the caveats at the end of this article for more security information.
Prepare to Explore
ODBC and Java Database Connectivity (JDBC) can be an effective way to create interactive Web pages that access data on your AS/400. By configuring CA/400 to run as a service, you open up NT server-based programming to your shop. Just keep in mind the following caveats:
• Only TCP/IP connections are supported. This scenario will not work under Microsoft SNA Server.
• Only ODBC can be run as a service; no other features of CA/400 (such as terminal emulation or printers) may be used.
• You must have the latest and greatest Client Access/400 for Windows 95/NT (at least V3R1M2 or above with the appropriate service packs) installed. To see if you are ready, check for the CWBCFG program in your Client Access directory.
• When configuring your Web server software, remember to configure your ASP scripts as execute-only. You want users to run the script, not download it and see your code, user ID, and passwords.
• Restrict the ODBC account heavily using the GRANT and REVOKE commands. This can protect sensitive data from abuse.
• Only give rights to the AS/400 tables and columns that your ASP application needs to see. Now, have fun exploring the exciting world of ASP and NT!
LATEST COMMENTS
MC Press Online