The Open Database Connectivity (ODBC) API is one of the oldest and most widely used ways for Windows users to access and modify data on any system, including IBM i data. Since the 1990s, Windows users have been using ODBC to import IBM i data into application programs such as Microsoft Excel, or into custom-written Windows programs.
Three steps to Windows ODBC access
If you’re not familiar with using ODBC for accessing IBM i data on Windows systems, you only need to perform three steps to import IBM i data into your Windows programs:
- Download a Windows ODBC driver
- Create an ODBC Data Source Name (DSN) configuration for accessing IBM i data on your Windows system
- Use your DSN to access IBM i data on a Windows program
Here’s how these pieces fit together for Windows ODBC access to IBM i data, and where you can run into problems using ODBC.
Step #1: Downloading Windows ODBC drivers
For IBM i ODBC access from a Windows machine, companies generally use these ODBC drivers:
Client Access ODBC Driver (32-bit)
iSeries Access ODBC Driver
IBM i Access driver
Some shops also use ODBC drivers from Microsoft and other companies to access IBM i data, but today let’s focus on IBM’s drivers.
The IBM-supplied ODBC driver you use will depend on your needs. The Client Access ODBC Driver and the iSeries Access ODBC Driver originally came with older IBM i client-server products (Client Access for Windows, Client Access Express for Windows, iSeries Access for Windows, etc.). The Client Access for Windows products was withdrawn years ago, and IBM is withdrawing IBM i Access for Windows 7.1 support as of April 30, 2019, ending that product line.
Windows ODBC drivers for IBM i access are now available with the more recent IBM i Access Client Solutions product, but you have to download these two products to get its ODBC drivers installed on your Windows device.
- IBM i Access Client Solutions – The base Access Client Solutions product
- IBM i Access Client Solutions – Windows Application Package, an optional package that contains ODBC, .Net, and OLE DB drivers for creating client applications for Windows operating systems.
Once the Windows Application Package is downloaded and installed, you’ll have access to the IBM ODBC drivers listed above. IBM i Access Client Solutions and IBM i Access Client Solutions – Windows Application Package can both be downloaded from IBM Entitled Systems Support.
Step #2: Creating a DSN on your Windows system
An ODBC Data Source Name (DSN) is a symbolic name that represents and describes all the parameters used to access an IBM i system using ODBC. ODBC DSNs are created using the ODBC Data Source Administrator (32-bit) or the ODBC Data Source Administrator (64-bit) on a Windows system.
DSNs can be created in three different flavors.
- System DSN – Created in the Windows registry, this DSN can be accessed by any user on the machine it resides on.
- User DSN – A DSN that’s created and used for one specific user on the Windows machine.
- File DSN – A DSN that’s stored on the system as a text file with a .DSN extension.
You generally only need to enter some specific IBM i-related data to create a DSN, including the IP address or hostname of your i system, sign-on information, and the default SQL schema, library, or library list that the DSN will make available to your applications.
Step #3: Using a DSN to access IBM i data in a Windows program
The final step is to use your ODBC DSN to connect to and access data on an IBM i system. DSNs allow users to have file and record level access to IBM i data on packages such as Microsoft Excel 2016, which uses the Microsoft Query Wizard to access IBM i data. DSNs can be used to access data in other packaged applications (including Microsoft Access), as well as in custom programming written in many different languages. Depending on their access rights, PC users can use DSNs to read, create, update, and delete records on an IBM i system, using off-the-shelf Windows applications and custom PC programming.
The problems with ODBC
Accessing IBM i data using ODBC comes with several security issues, including the following issues:
Excessive user authority: In order to protect application data in older applications running on 5250 green screens, many shops rely on production system menu security and limiting control line access. Users have read, write, update, and delete rights to the data, but the application’s menu protects them along with a lack of a command line from accessing or updating the data outside of the production system.
While menu security works in a 5250-command line environment, ODBC bypasses menu and command-line security by directly accessing IBM i data. If a user has update authority to files in a controlled application environment, they can update those files directly using an ODBC DSN on a Windows machine. Excessive user authority that is well-protected inside 5250 applications causes security issues when using ODBC (or any other direct access method, such as OLE DB, .NET, JDBC, or SQL, as well).
Default IBM i passwords: Another ODBC issue arises when shops allow user profiles to keep default passwords. Since ODBC access to IBM i data can be accomplished from any device on the same network as your IBM i system, it’s difficult to stop people with default passwords from accessing and possibly changing data. See our post on Common IBM i Password Security Mistakes for more information on dealing with default passwords.
IBM defined ODBC issues: IBM has identified several implementation issues surrounding ODBC on their Implementation issues of ODBC APIs Web site. On this site, IBM lists common ODBC problem issues, such as connection string issues, restrictions and unsupported functions, cursor behavior issues, long-running queries, and several other problems. It’s worth reviewing this page when you’re working with IBM’s ODBC drivers.
Protecting IBM i against unauthorized ODBC access
IBM offers many security-oriented exit points to guard against unauthorized IBM i access. An exit point is a pre-defined break in an IBM i process where a user-written program can be called to perform additional checking and validation. To secure ODBC, you can set up user-written validation routines that are attached to the QIBM_QZDA_INIT exit point to examine and approve/reject ODBC requests from specific users (a sample IBM program to do this can be found here).
You can create your exit point programs, or you can use a third-party IBM i Exit Point Monitoring & Intrusion Prevention program such as SEA’s iSecurity Firewall to handle ODBC exit point security. Third-party packages allow you to control outside access to IBM i data from ODBC and other access methods, create and test a security plan, analyze security events, and create management and auditor reports on your IBM i security performance. A third-party package is an excellent alternative to shoring up outside access to IBM i data, without having to create and institute additional DIY security measures from scratch.
