14
Thu, Nov
5 New Articles

Running Client Access ODBC as an NT Service

APIs
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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 /s

In this example, should be replaced with the name of your AS/400 system. If you do not have a proper Domain Name System (DNS) running or are not using the HOSTS files on your NT server, you may add the option /IPADDR <400IP>, replacing <400IP> with the IP address of your AS/400. The CWBCFG utility adds necessary registry keys and values to the default registry. CA/400 needs these values to log in under the Windows system account and run as a service. You may now close the MS-DOS window by typing exit and then pressing the Enter key. Since all mainstream services use the Windows NT system account, you are now set up to run Client Access as a service. The next step is to set up an ODBC system data source for the service to use. (Setting up a system data source is covered later in this article.) Once the system data source is set up, you are ready to run ODBC as a service.

 

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!


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: