Remote reporting on the EKMF database

Overview

Remote reporting from an SQL database containing cryptography key information is a crucial process for managing security, compliance, and operational continuity. By retrieving key details such as creation dates, expiration dates, and key encryption key (KEK) attributes, organisations can monitor their cryptographic assets, ensure timely renewals, and maintain audit trails. This process can be further enhanced by exporting the data into CSV format or integrating with an API to automate notifications—such as sending reminder emails to key owners.

Key Details Maintained in the Database

·       Key ID or Name: Unique identifier for each cryptographic key.

·       Creation Date: The date when the key was generated, useful for lifecycle management.

·       Expiration Date: The date when the key will expire, indicating when renewal or replacement is needed.

·       Key Encryption Key (KEK) Details: Information about the KEK used to protect the main keys, including KEK ID and rotation status.

·       Key Owner: Contact details of the individual or team responsible for the key.

·       Status: Current status of the key (e.g., active, expired, revoked).

Remote Reporting Process

1.      Secure Connection to the SQL Database:

2.      Establish a secure, authenticated connection to the SQL server using tools such as ODBC, JDBC, or direct SQL client access. Ensure appropriate permissions are configured to access cryptographic key tables.

3.      Querying Key Information:

4.      Run SQL queries to extract relevant fields, for example:

5.      SELECT key_id, key_name, creation_date, expiration_date, kek_id, owner_email, status FROM cryptography_keys;

6.      Exporting to CSV Format:

7.      Use built-in SQL features or external scripting (Python, PowerShell, etc.) to export query results into CSV. This format is widely supported and easily imported into spreadsheets or other systems.