Backup Restore 2 Support for Encrypted SQL/MX Objects

Transparent Data Encryption (TDE) support for creating encrypted SQL/MX objects has emerged as one of the most impactful database enhancements in the HPE Nonstop ecosystem. With BR2’s improved capabilities, it has become a powerful tool to secure sensitive data at rest—without compromising performance or manageability. This advancement marks a significant step forward in aligning Nonstop database security with modern compliance and threat protection standards.

TDE supports the creation of encrypted SQL/MX objects from SQL/MX 4.0 onwards for schema version 4000 or higher. To comply with the security standards, any sensitive data at rest must be encrypted. Hence, the encrypted SQL/MX objects must also be stored in the backup tape or disk in an encrypted form. BR2 supports storing encrypted SQL/MX objects in encrypted form on backup media such as tapes or disks and restoring these encrypted backups to encrypted SQL/MX objects on disk, ensuring that encryption is preserved throughout the backup and recovery process.

Encryption and decryption of SQL/MX objects rely on either a passphrase or a security token. The passphrase acts as the master key for encrypting backups, while the security token helps identify the passphrase that can be used in backup scripts. To restore an encrypted backup, you must know the original passphrase because it is never stored on tape or disk for security reasons.

In this article, let’s walk through the practical steps to create encrypted SQL/MX objects, backup, and restore the same using the PAK2/UNPAK2 utilities.

Steps to create encrypted tables

Execute the statements below from OSS environment on the Nonstop machine.

1. Create a catalog from MXCI (SQL/MX Conversational Interface) and exit.

mxci 
Create catalog abccat1;
exit;

2. Configure a keystore:

mxkms --config-ks --ks-name abc_ks1

output: Successfully configured the keystore ABC_KS1.

3. Configure virtual keystore by entering the security officer (SO) pin and the user pin:

mxkms --config-vks --ks-name abc_ks1 --vks-name abc_vks1

output: 

SO PIN/Password:

Confirm SO PIN/Password:

User PIN/Password:

Confirm User PIN/Password:

Successfully configured the virtual keystore ABC_VKS1.

4. Associate the virtual keystore with the catalog created:

mxkms --associate-vks --vks-name abc_vks1 --cat-name abccat1

output:

Successfully associated the catalog ABCCAT1 with virtual keystore ABC_VKS1.

5. Open the virtual keystore:

mxkms --OPEN-vks --vks-name abc_vks1 --cat-name abccat1

output:

User PIN/Password:

Successfully opened virtual keystore ABC_VKS1.

If the virtual key store was created long ago, one must open the virtual keystore before associating it with a catalog.

6. Proceed with the creation of schema and tables in MXCI as shown:

mxci

create schema abccat1.abcsch; 

set schema abccat1.abcsch; 

create table tab1( 
c1 int unsigned generated always as identity ( start with 1
increment by 1) not null, 
c2 int primary key, 
c3 clob, 
c4 blob, 
c5 int)encryption on using 'AES256'
location $sas9; 

insert into tab1 (c2, c3, c4, c5) values (10,'HPE','INDIA',20);
insert into tab1 (c2, c3, c4, c5) values (11,'HPE','INDIA',21); 

exit;

Backup and Restore of encrypted objects

Encrypted SQL/MX objects can be backed up and restored using either of the BR2 job options:
MXPASSPHRASE or MXSECURITYTOKEN. These job options are not supported for OSS objects.

MXPASSPHRASE

This job option specifies whether to backup/restore or skip the encrypted SQL/MX objects during backup/restore. OFF is the default.

Backup:

For example, assume passphrase xyz1234 is provided to the backup command below.

pak2 /home/pakfiles/mytab_pak, MX TABLE abccat1.abcsch.tab1, LISTALL ON, MXPASSPHRASE on

output:

Enter PASSPHRASE:

Confirm PASSPHRASE:

Encrypted table abccat1.abcsch.tab1 and all its subordinate objects will be backed up.

Restore:

For example, assume passphrase xyz1234 is provided for the below restore command.

unpak2 /home/pakfiles/mytab_pak, MX TABLE abccat1.abcsch.tab1, LISTALL ON, MXPASSPHRASE on

output:

Enter PASSPHRASE:

Confirm PASSPHRASE:

Encrypted table abccat1.abcsch.tab1 and all its subordinate objects will be restored.

MXSECURITYTOKEN

This job option specifies whether to backup or skip the encrypted SQL/MX objects during backup. This job option can be used as an alternative to the MXPASSPHRASE job option in backup scripts.

The MXKMS utility generates a security token by accepting passphrase as input. These tokens can be safely used in plain text within backup scripts. It is not mandatory to remember the security token used during backup until the restore time, but it is necessary to remember the passphrase that was used to generate the security token. If required, a different security token can be generated for the same passphrase during the restore time and use it for restoring the encrypted SQL/MX objects.

Backup:

Generate a security token using the MXKMS utility.

For example, assume passphrase abcd1234 is used to generate security token.

mxkms --register-sec-tok

output:   

Passphrase/Password:

Confirm Passphrase/Password:

Security Token: 796338c44618432bc0e1a7aff4b4ad843d4be96481358680e3072c79e9c8e213b 97af436b2a6472230d037f3f46beb50aa9f15a527511285d0389732fb068425

Successfully registered the security token.

Provide the generated security token in the BR2 backup command.

pak2 /home/pakfiles/mytab_pak, MX TABLE abccat1.abcsch.tab1, LISTALL on, MXSECURITYTOKEN 796338c44618432bc0e1a7aff4b4ad843d4be96481358680e3072c79e9c8e213b97af4 36b2a6472230d037f3f46beb50aa9f15a527511285d0389732fb068425;

Encrypted table abccat1.abcsch.tab1 and all its subordinate objects will be backed up.

Restore:

Provide the security token generated during backup for the encrypted objects restore.

unpak2 /home/pakfiles/mytab_pak, MX TABLE abccat1.abcsch.tab1, LISTALL on, MXSECURITYTOKEN 796338c44618432bc0e1a7aff4b4ad843d4be96481358680e3072c79e9c8e213b97af4 36b2a6472230d037f3f46beb50aa9f15a527511285d0389732fb068425;

Encrypted table abccat1.abcsch.tab1 and all its subordinate objects will be restored.

If the security token doesn’t exist in the system, it is allowed to use the MXPASSPHRASE job option during restore operation even though the MXSECURITYTOKEN was used during backup. The passphrase provided during restore must match the passphrase used to generate the security token during backup, which is abcd1234 in this example.

unpak2 /home/pakfiles/mytab_pak, MX TABLE abccat1.abcsch.tab1, LISTALL ON, MXPASSPHRASE on

output:

Enter PASSPHRASE:

Confirm PASSPHRASE:

Encrypted table abccat1.abcsch.tab1 and all its subordinate objects will be restored. We hope this article helps you get started with the new job options. Keep experimenting!!