Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


iSeries, (IBM i) remote cummunication to a web server

Posted on 2011-09-13
Medium Priority
Last Modified: 2012-05-12
I have an IBM i5 running V6R1 running at our offices and a web server running Linux CentOS 5 (but may be changing that to ubuntu 11.04) at a data center.  I am exploring the available options to allow the web server to access data files and possibly execute commands/programs the i5 securely so that it could .  I the i5 is behind a Cisco firewall.  The web site I believe is mostly or all PHP.  I am for the most part and RPG programmer and not at all a web developer and the web developer I am working with barley know how to sign on to the i5.

The current setup (which was done by someone else and not available) to transfer data between the two is another Linux box at the office (on the same local network as the i5) and it monitors directories (incoming and outgoing), relays them to the i5 or web server and then runs a script/command.  I believe it uses SFTP.

Please let me know if you need more information.


Question by:DCS12
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
LVL 27

Expert Comment

ID: 36533514
...and the web developer I am working with barley know how to sign on to the i5.

Would your web developer be able to access a Windows server with ODBC (JDBC)? If so, then pretty much the same code could be used to access your i5. Use the i5 JDBC driver and an appropriate user/password.

What does the web developer need to know? Is it a question of "How do I access a remote database?"


Author Comment

ID: 36535962
Sorry let me try to clarify.  He (the web developer) said he is familiar with ODBC and I have have used it several years ago years ago with MS Access and also to drive a ColdFusion website.  Have never used the JDBC before.  It looks like there is an ODBC driver for Linux that can be used.

So my main question is what would be the best or simplest way to establish the connection securely?  Would I need a VPN connection from the web server to our office?          
LVL 35

Expert Comment

by:Gary Patterson
ID: 36536150
Do I have this right?  

You have a system on an insecure network (meaning that it is located on a public network or DMZ network that is exposed to a public network), and you want to allow it relatively unfettered access to connect in through the firewall to your (secure) AS/400 and execute commands and directly access the database?  This also means that the insecure system will need a mechanism to authenticate to the secure host, and credentials that allow it to access the secure database and execute commands.

Do you see the security problem that this presents?

When designing publicly-accessible systems, we always want to keep in mind the possibility that the system that is exposed directly to the internet may be compromised.  We want to make sure, from a design standpoint, that if that happens, we haven't created easy paths from the insecure system/network into the secure system/network.

The best design from a security perspective is to allow the secure system to PUSH data to the insecure system, and to allow absolutely no traffic to originate from the insecure system to the secure system.  One solution is to occasionally upload data, if that meets the needs of your application.  Another is to partially replicate the data in near-time or real-time using database replication tools.  Another is to design relatively secure interfaces between the systems that allow the insecure system to initiate only specified types of transactions - this approach can be implemented, for example, using SQL stored procedures (or may other interfaces, of course).

By the way, these considerations probably explain the existing design of the application: PUSH from secure to insecure.

Now, the rules are different when we are running wholly on secure networks, but that doesn't sound like the case here.

- Gary Patterson
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.


Assisted Solution

mkc451 earned 400 total points
ID: 36536462
ODBC/JDBC queries against the iSeries directly is a viable solution and you can use ODBC to launch programs on the iSeries to do things like refresh/rebuild data files for access by the system, all kinds of stuff. It's not my preferred way of doing things but it is very common. Security can be handled a number of ways, but we use Exit programs to verify the origination of the commands and limit what can & cannot be executed by ODBC securing it to specific programs, physicals and commands.

You can replicate the data if you wish (as Gary recommended) to an SQL based server box. This is pretty straight forward. I have several customers doing this to MS SQL and DB2 databases.  

Another options is to look at ZendServer (which comes free with your V6R1 operating system along with a year of support from ZEND) ... It allows you to write/execute PHP code against the iSeries and run it on the iSeries. It also comes with MySQL interface to the iSeries databases and gives you a set of commands using EasyCom I5 product that you can work directly against iSeries databases.  Where IBM & Zend are going with this product is not entirely clear -- but I have it running at over a dozen customers  and it works well.

Another option is that you can use the ZendServer(purchased software) line to run the web server portion on a Linux box and use the I5 command structure to access the iSeries also.

This is not a fall of the rock solution .. it can be a little tricky to get it running perfectly but when it runs it's fast and solid when run on the iSeries.

Michael Cody

Author Comment

ID: 36536582
Yes the web server is on an insecure network and the AS/400 is on a secure network.  The AS/400 is at our store front and the web servers are host with another company.  I believe it is Rack Space.

The web server is currently sends invoices (text files) to the Linux box on our secure network, the Linux box pushes to file to the AS/400 and runs a command that turns the text file into an invoice in our software system.  So in our software the web invoices contain 2 invoice #'s.  One from the website database and one from our software.  I'm not sure if that makes since or not.  We were wondering if the web server maybe pull the next invoice from a control file, increment and then update.    

The web server does run on an SQL database.  How would you go about pushing data from the AS/400 to the web server?
LVL 35

Expert Comment

by:Gary Patterson
ID: 36536858
I can understand why you want to eradicate the dual invoice numbers.

One suggestion: create a stored procedure on the AS/400 that obtains and increments the invoice number, and create a dedicated AS/400 user profile that only has rights to execute that one stored procedure (no logon, limited capabilities, etc.).  Unfortunately, many AS/400's are not properly secured, and make use of *PUBLIC authority on many objects in the system, which makes it hard to secure a dedicated profile like this.  Another option would be to create a web service running on the AS/400 that does not require AS/400 authentication, but makes use of an alternate security mechanism, and that is firewall-restricted to only accept requests from a specific external system

Then your application can request invoice numbers from the AS/400 without creating too much risk to the backend system.

Bear in mind that an attacker that compromises the front-end system COULD now launch a denial of service attack against your back-end system by repeatedly calling the stored procedure, and potentially running your system out of invoice numbers, so you have to weigh this (relatively small) risk versus the benefit of having one numbering scheme.

Pushing data from AS/400 DB2 to SQL Server (or any other database) can be accomplished in several different ways.  

First of all, there are packages that will allow you to do this (example: http://www.hitsw.com/products_services/dbmoto/dbmoto_dsheet.html).  Second, if you are trying to replicate just a few tables, you could write database trigger programs (or a journal monitor if you are journaling the tables in question) on the AS/400 that captures the change and forwards them to the target system.  One way to do this is to have the trigger program queue the records to an AS/400 data queue, and then write a Java program that monitors the data queue, and performs the updates to the SQL Server via JDBC.  I've also written VB and C# programs that use the Client Access API to monitor an AS/400 data queue and apply changes to a Windows-based DBMS.  

There are numerous other approaches, too.

Just remember that whenever you allow any communication flow that initiates on an insecure system and terminates on a secure system, you are probably creating a vulnerability that could be exploited by an attacker if the system is compromised.  Make sure you have a good understanding of the security implications of your design, and the relative degree of risk that you are introducing when designing these types of applications.

I've seen some disastrous results from applications designed by well-meaning developers that didn't understand the security implications of their implementations.

- Gary Patterson

Author Comment

ID: 36537174
Pardon my ignorance, but lets say we go a stored procedure.  What would be the method for the web server to get through the firewall to call the stored procedure?
LVL 27

Assisted Solution

tliotta earned 600 total points
ID: 36544670
You call a stored proc with the SQL CALL statement via ODBC (or JDBC). Firewall issues are mostly the same as for any ODBC/JDBC access.

Instead of a direct SELECT, you would use CALL. The stored proc then determines what to SELECT and/or what to return based on what parms it receives. Various additional controls might be placed on connections to limit them to certain IP addresses or users or whatever. Controls might be firewall rules or server exit programs or whatever.

A stored proc encapsulates data and acts as an interface. Underlying data structures or business rules may then be changed without remote clients needing to be changed also.

LVL 35

Accepted Solution

Gary Patterson earned 1000 total points
ID: 36544770
You'd want to configure the AS/400 Database server for SSL (TCP Port 9471), and open that port in your firewall for packets originating from your hosted web server only with a destination of the public IP address of your AS/400.

- Gary Patterson

Author Closing Comment

ID: 36563084
Thanks guys.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question