IBM i: The SQL Way · #18

Inspect an IBM i External Key Manager Configuration with SQL

Use QSYS2.EKM_INFO to read the type, IAM endpoint, service endpoint, and instance ID stored in an IBM i external key manager description file.

IBM i 7.6 external key manager description files store the connection information required to communicate with an external key management service. QSYS2.EKM_INFO exposes the non-secret configuration details as a one-row SQL result.

The table function is:

QSYS2.EKM_INFO

Basic query

SELECT *
FROM TABLE(
    QSYS2.EKM_INFO(
        '/home/EKMconfig'
    )
);

Replace the path with the actual external key manager description file.

Focused query

SELECT
    EKM_TYPE,
    IAM_ENDPOINT_DOMAIN,
    SERVICE_ENDPOINT_DOMAIN,
    INSTANCE_ID
FROM TABLE(
    QSYS2.EKM_INFO(
        '/home/EKMconfig'
    )
);

The function returns one row.

Result columns

EKM_TYPE

The configured external key manager type.

Current IBM documentation identifies:

*KEYPROT

for IBM Key Protect.

IAM_ENDPOINT_DOMAIN

The Identity and Access Management endpoint used to authenticate service requests.

SERVICE_ENDPOINT_DOMAIN

The external key manager API endpoint used for key-management operations.

INSTANCE_ID

The UUID identifying the IBM Key Protect instance.

Compare two configurations

WITH CONFIGURATIONS
(
    CONFIGURATION_NAME,
    CONFIGURATION_PATH
) AS
(
    VALUES
      ('PRODUCTION', '/home/ekm/prod.ekmd'),
      ('RECOVERY',   '/home/ekm/dr.ekmd')
)
SELECT
    C.CONFIGURATION_NAME,
    C.CONFIGURATION_PATH,
    E.EKM_TYPE,
    E.IAM_ENDPOINT_DOMAIN,
    E.SERVICE_ENDPOINT_DOMAIN,
    E.INSTANCE_ID
FROM CONFIGURATIONS AS C
CROSS JOIN LATERAL
    TABLE(
        QSYS2.EKM_INFO(
            C.CONFIGURATION_PATH
        )
    ) AS E;

This can verify that production and recovery descriptions point to the intended instances and regional endpoints.

Find an unexpected endpoint

WITH EKM AS
(
    SELECT *
    FROM TABLE(
        QSYS2.EKM_INFO(
            '/home/ekm/prod.ekmd'
        )
    )
)
SELECT *
FROM EKM
WHERE SERVICE_ENDPOINT_DOMAIN
      <> 'us-south.kms.cloud.ibm.com';

Replace the expected endpoint with the organization’s approved IBM Key Protect region.

A mismatch does not automatically mean the file is malicious. It can indicate:

• a regional migration
• a test configuration
• an outdated file
• a copy from another environment
• a configuration mistake

Validate the expected instance

SELECT
    CASE
        WHEN INSTANCE_ID =
             '11111111-2222-3333-4444-555555555555'
          THEN 'EXPECTED INSTANCE'
        ELSE 'REVIEW INSTANCE'
    END AS VALIDATION_STATUS,
    INSTANCE_ID,
    SERVICE_ENDPOINT_DOMAIN
FROM TABLE(
    QSYS2.EKM_INFO(
        '/home/ekm/prod.ekmd'
    )
);

Use the organization’s actual approved instance UUID.

Path handling

The function accepts an IFS path.

A relative path is resolved using the job’s current working directory.

For production diagnostics, an absolute path is usually clearer:

/home/ekm/prod.ekmd

rather than:

prod.ekmd

Authority

The caller needs:

*X authority to every directory in the path
and
*R authority to the stream file

Do not grant write authority simply to allow the query.

A read-only monitoring profile should receive only the minimum directory traversal and file-read authority required.

What the function does not return

EKM_INFO exposes configuration identification and endpoint information.

It should not be treated as a way to retrieve secret credentials.

The result is suitable for validating:

• manager type
• IAM endpoint
• service endpoint
• instance ID

It does not prove that authentication succeeds or that a key can be retrieved.

Connectivity still needs testing

A correct-looking description file can still fail because of:

• DNS resolution
• firewall policy
• proxy requirements
• TLS trust
• expired or invalid credentials
• IBM Cloud service availability
• incorrect service authorization
• regional endpoint access

Use the supported EKM and cryptographic-services interfaces to test actual operation.

Navigator and CL alternatives

External key manager descriptions can also be reviewed through:

IBM Navigator for i
DSPEKMD

Use Navigator when:

• an administrator wants a graphical inventory
• files are being created or maintained
• cryptographic-services administration is already centralized there

Use DSPEKMD when:

• working interactively from a command line
• reviewing one description file

Use EKM_INFO when:

• configuration must be compared
• a report is needed
• validation should be automated
• several description files must be checked
• results need to join with inventory data

Build a controlled inventory

CREATE TABLE MYSEC.EKM_DESCRIPTION_INVENTORY
(
    CONFIGURATION_NAME VARCHAR(50) NOT NULL,
    CONFIGURATION_PATH VARCHAR(1024) NOT NULL,
    EXPECTED_TYPE      VARCHAR(20),
    EXPECTED_IAM       VARCHAR(128),
    EXPECTED_SERVICE   VARCHAR(128),
    EXPECTED_INSTANCE  CHAR(36),
    PRIMARY KEY (CONFIGURATION_NAME)
);

Query each registered file:

SELECT
    I.CONFIGURATION_NAME,
    I.CONFIGURATION_PATH,
    E.EKM_TYPE,
    E.IAM_ENDPOINT_DOMAIN,
    E.SERVICE_ENDPOINT_DOMAIN,
    E.INSTANCE_ID
FROM MYSEC.EKM_DESCRIPTION_INVENTORY AS I
CROSS JOIN LATERAL
    TABLE(
        QSYS2.EKM_INFO(
            I.CONFIGURATION_PATH
        )
    ) AS E;

A production utility should handle missing or unreadable files explicitly.

IBM i 7.6 only

IBM lists QSYS2.EKM_INFO as supported at:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3

It is not listed as supported on IBM i 7.5 or earlier releases.

IBM Navigator management for EKM description files also requires the applicable IBM i 7.6 PTF level and a secure HTTPS connection.

Security considerations

An EKM description file is security-sensitive configuration.

Protect:

• the stream file
• containing directories
• backup copies
• exported configuration reports
• change authority
• any associated authentication material

Do not expose instance identifiers and endpoint architecture unnecessarily.

A practical review workflow

1. Identify the approved EKM description path.
2. Verify directory and file ownership.
3. Query EKM_INFO.
4. Compare type, endpoints, and instance ID with the approved design.
5. Confirm the file belongs to the correct environment.
6. Test actual EKM connectivity through supported tooling.
7. Record evidence without exposing secrets.
8. Review the configuration after endpoint, region, or credential changes.

Final takeaway

QSYS2.EKM_INFO provides a clean SQL inventory of the external key manager identity and endpoints stored in an IBM i description file.

It is an important validation tool, but it verifies configuration content—not successful authentication, network reachability, or key availability.