QMF for Workstation and Multi-Factor Authentication

Tom Glaser; July 2025

QMF (Query Management Facility) is IBM/Rocket’s flag ship product for querying Db2.  While there is a TSO version that has been around as long as Db2 has been around, there is an interface using WebSphere and a desktop (QMF for Workstation).  With more and more companies using Multiple Factor Authentication, a few companies are struggling on how set this up.  This article hopes to address these concerns and provides some recommendations on how to set up QMF for Workstation to work with MFA.

What is Multi-Factor Authentication?

Early authentication required user to enter user ID and password pair, which was compared against saved credentials. In course of time, passwords were made stronger by insisting on longer passwords, use of special characters. We have now reached to stage where we don't rely only on password verification, but verification of more information which is short-lived and dynamic in nature; and via different communication channels.

Multi-factor authentication is about:

1. Verification of credentials, this is usually done by company-wide authentication framework such as LDAP, RACF etc.
2. Creating a dynamic information which is valid for short duration, this can be creating a random number or creating a link with parameters that will the identify user. 
3. Send this dynamic information (called MFA token/PIN) via different mechanism to user.
4. Allow the user to re-enter the short-life extra information.
5. Verify the information within the valid time duration.
6. Complete the authentication process.  

How It Works:

-            Something You Know (Password or PIN)

-            Something You Have (Text code or app)

-            Something You Are (Biometrics like fingerprint or face scan)

Providing additional verification in addition to password verification is not easy.  There are commercial products that provides this functionality; Okta is most common in organizations.  QMF already supports MFA for JDBC-based connections, provided the database is Db2.

QMF for Workstation Connections

When QMF for Workstation makes a connection to retrieve information, this connection is not permanent.  In fact, there might be several connections to Db2 made; it pulls back the information it needs and drops this connection.  This can be viewed by:

Connection Displays:

As you monitor the connections, they will disappear. This is where the issue lies. When you attempt to perform another task, such as displaying all the tables, QMF needs to make another connection.  However, if you are using MFA tool such as the RSA SecurID, the password or token can only be used within a given time slot to create a connection. This connection comes from underlying pool and once its purpose is over it is placed back in the pool which appears as ‘Idle’ and can eventually timeout. Under the covers, QMF tries to make another connection using the old token which may have timed out (if the timeout setting is aggressive) and it fails. One option to correct this issue, we must inform Db2 to authenticate on the first connection from the QMF user and allow this connection from the same user to continue by setting appropriate timeout for the connection. This defeats the very purpose of connection pool as one connection will be in use and all database transactions will go through one connection limiting the throughput and application performance. One variation which QMF application developers may think of is to create multiple connections upfront and use them as required. The problem with this approach is that the other upfront connections will sit idle, and they may timeout requiring reconnection bringing back the original issue.

One more variation is to set large timeout (no timeout) for all the upfront connections, this may solve the problem, but this will keep the connections up till the lifetime of application. This may be a reasonable assumption as the application may use the same connection for the user for other workflows, the user is active on the application. The other option is to set the very large value of timeout (no timeout) for MFA token and keep the connection timeout to default value. Though this will also resolve the problem but may not be suitable for end-users as it may be risky from security posture point of view, and hence most of the provider have a range for this timeout (in RACF MFA_AUTHCACHE_UNUSED_TIME range is 120ms - 7200ms). If the connection timeout is not set appropriately in option one, user may see user account lockout error as without timeout when the QMF user makes the first connection it eventually times out during the workflow, then QMF fails to create the subsequent connections as passcode/token is not valid. QMF retries to create connection based on retries attempt configured/coded in the application, before the security provider, such as RACF, revokes the user ID because of too many failed login attempts.

Authentication and Authorization Points in QMF

QMF enforces access control at the following 5 key points:

1.  QMF Login in WebSphere Application:

·        Authentication is performed at application login.

·        Currently supports LDAP and Basic Authentication methods.

2.  Shared Repository Storage Creation (Pool1):

·        Involves setting up a JDBC connection to the underlying database.

·        This connection is pooled and used specifically for accessing Shared Repository Storage.

3.  Shared Repository Access Control:

·        Shared Repositories are logical groupings of QMF objects.

·        These are access-controlled based on the protection mechanism defined at the time of creation (e.g., LDAP, DB-level protection).

·        They don’t have a physical footprint but are managed through metadata ( a tuple entry in Shared Repository Storage tables).

·        To interact with objects within a Shared Repository, users must establish a Shared Repository Connection.

4.  Shared Repository Connection (Pool2):

·        A JDBC connection to the database hosting the Shared Repository Storage.

·        Uses credentials defined during repository connection creation.

·        This uses a separate connection pool (Pool2) from the one used in step b (Pool1).

5.  Data Source (DB) Connection (Pool3):

·        Involves direct JDBC connections to external databases.

·        These are configured with their own credentials and attributes.

·        Managed using a separate Pool3.

The Resolution

Changes to Db2 on z/OS

Db2 supports a kind of heuristic-based internal authentication cache, specifically designed to allow credential replay for users who have been updated for one-time-use credentials.  There are two key ZPARMs that help in this area….and this is key to allowing QMF for Workstation to work with MFA.

MFA_AUTHCACHE_UNUSED_TIME=0000 - The MFA_AUTHCACHE_UNUSED_TIME subsystem parameter specifies how long in seconds that multi-factor authentication (MFA) security credentials from a distributed client (DRDA or REST) can remain unused in the Db2 global authentication cache before new security credentials must be provided.  Valid values are 0, 120–7200 (seconds).  Db2 z/OS V12.

https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=dpp-mfa-auth-unused-time-field-mfa-authcache-unused-time-subsystem-parameter

Set this value to something like 3600.  This indicates the QMF for Workstation user needs to authenticate every hour.   Recommendation: Set the parameter for all members of the group to the same value.

On successful authentication of the user, RACF returns an indication to Db2 if MFA was used.

 – Db2 caches a hash value of the authentication token that includes multiple factors

Cached Authentication token purged:

 – RACF commands, ALTUSER REVOKE, DELUSER performed against the connection user ID or user ID revoked due to excessive bad passwords.

– The same MFA-based security credentials not reused by the same client IP address within the time specified in the subsystem parameter.

AUTHEXIT_CACHEREFRESH=xxx – This subsystem parameter specifies whether the cache entries of the package authorization, the routine authorization, the plan authorization, the DDF user authentication, and the dynamic statement are refreshed and whether the dependent packages are invalidated when a user profile or resource access is changed in RACF.  Values are ALL or NONE.  Db2 z/OS V11.

https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=dpp-auth-exit-cache-refr-authexit-cacherefresh-subsystem-parameter

Set this value to ALL

Note: Online update is supported only if the AUTHEXIT_CACHEREFRESH setting was ALL when Db2 was started. Otherwise, Db2 issues message DSNZ014I with DSNZCMD1 for load-sect-name for any online attempt to update the MFA_AUTHCACHE_UNUSED_TIME value.

Changes to QMF for Workstation

There are three settings that tells QMF how to handle connections.  Let’s review these 2 parameters and set them to zero.

Idle Connection Timeouts – This setting determines how long an inactive connection is retained before it's automatically closed. The "Cancel limit" field, specifically, defines the number of seconds an idle connection will be kept alive before it is cancelled. This setting helps manage system resources by automatically closing connections that are not actively being used, preventing them from unnecessarily consuming resources. A higher timeout limit can reduce connection overhead by minimizing the need to establish new connections.  Set this value to 0.

Repository storage connections timeout -  refers to the time (in minutes) a Single-Use Password Mode connection to a shared repository remains idle before automatically closing.  A setting of zero (0) in this field indicates an unlimited idle state, meaning the connection will only close when the user session ends. This setting is configurable within the shared repository connection settings in QMF for Workstation.  To clarify, the "Single-Use Password Mode" refers to a specific authentication method where a password is valid only for a single connection and a limited time. This setting in QMF is crucial for managing the persistence of these connections and ensuring they don't remain open indefinitely after becoming idle. 

Note: A value of zero 0 sets unlimited idle state for the connection timeout, meaning that connection will close only when the session ends.

1. Set Idle Connection Timeout to 0....unlimited (under resource limits)

    Right click Db2 subsystem ID repository connection; Properties; Resource Limits;

https://www.ibm.com/docs/en/qmf/13.1.2?topic=vrl-specifying-timeouts-resource-limits

 2. Under Shared Repository, click Edit, click Set user info,  Advanced...change to 0 

3. Turn off the ability to pass along the Id and password when you connect to a new Db2 subsystem ID.  Otherwise, QMF will try to use your last ID and password and that will cause a connection failure.

Creating a Shared Repository connection

A nice feature of QMF for Workstation is the ability to create a shared repository connection.  A repository is a centralized storage area that was created by your QMF administrator. It is the place where your objects such as queries, forms, and reports can be saved. It is also where QMF will look for the information necessary to connect to any data sources that you need to access. The Repository Connections view lists all of the currently available repository connections.  What’s nice about a Repository is that the QMF Administrator and create a repository pointing to all the Db2 subsystems IDs.  If something changes, such as an IP address or port for a Db2 subsystem, only one change to the repository is needed.  Anyone connecting to that repository will have the updated information.

Each time that you open QMF, an attempt is made to connect to the repository that was last used. At any time, you can connect to a different repository by selecting the repository connection information from the Repository Connections view.

https://www.ibm.com/docs/en/qmf/13.1.0?topic=crcq-creating-shared-repository-connections-2

If MFA is started and the QMF Shared Repository currently exists, all that’s needed are the recommendations above.  However, if creating the Shared Repository for the first time in QMF v13.1.2 using an RSA- enabled account, this will fail.  Due to multiple failed logins attempts with the RSA token being reused during the repository creation, the account becomes locked. Although Db2 ZPARM: MFA_AUTHCACHE_UNUSED_TIME is set Resource Limits have not yet been defined because the shared repositories are being set up for the first time.   As a result, the necessary changes to allow QMF to properly handle RSA token authentication cannot yet be applied.

As of QMF v13.1.2, there is no way to use MFA to create a new Repository.  The only way around this is to turn off MFA against the ID that creates this repository.  Once the repository is built, MFA can be turned on and this will resolve this issue.   An enhancement request to correct this issue has been created.  If you wish to vote on this IBM “aha” request, click on the following link:

https://ibm-data-and-ai.ideas.ibm.com/ideas/QMFEE-I-56  (Support for Multi-Factor Authentication)

Passphrases:          

Regarding supporting passphrases, Connection from QMF to DB2 on z/OS over DB2 Connect Type 4 JDBC driver works.  The user can provide a passphrase or a password in the credentials dialog while connecting to DB2 on z/OS, it will authenticate the user."

Use of PassTickets

Among these, the connection to a Data Source (when the Data Source is DB2 on z/OS) supports MFA through PassTickets. Below are some references in the QMF documentation regarding the use of PassTickets:

https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=requests-enabling-db2-receive-racf-passtickets

https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=mitbcr-enabling-caching-mfa-based-authentication-credentials-clients-sysplex-workload-balancing

https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=mitbcr-enabling-caching-mfa-racf-passtickets-credentials-clients-without-sysplex-workload-balancing

In summary:   QMF will continue to be enhanced to support the future of MFA. As of QMF V13.1.3, OKTA will be supported.  We believe most customer issues have been resolved and items in this blog should help resolve any issues you might face.  If you continue to have problems, please read out to IBM and open a case.