DB2 Warehouse and encryption

 I spent some trying to move the database from one DB2 instance to another, just a trivial backup and restore scenario. Unfortunately, RESTORE command failed with a nasty message.

db2 restore db bludb from /tmp taken at 20210425102241 

SQL1743N  The RESTORE command failed because the backup image is of an 

encrypted database but the target database is not encrypted.

I was trying to restore the encrypted database into a plain world. To be sure:

db2ckbkp -H /tmp/BLUDB.0.db2inst1.DBPART000.20210425102241.001 

Encrypt Info Flags             -- 0x1
	                                  Source DB was encrypted

Yes, the backup is encrypted but how to recreate it?

To decrypt the backup, we need the key used for encryption. The source keystore and the label of the secret key can be extracted from the backup.

db2 "restore db bludb from /mnt/backup taken at 20210425102241   encropts 'show master key details'"

The result is tricky, in the diagnostic directory look for a specific file containing information about source keystore and label.

cd /database/config/db2inst1/sqllib/db2dump/DIAG0000

cat BLUDB.0.db2inst1.DBPART000.20210425102241.masterKeyDetails 

                  KeyStore Type: PKCS12

             KeyStore Location: /mnt/blumeta0/db2/keystore/keystore.p12
            KeyStore Host Name: thinkde
           KeyStore IP Address: 127.0.1.1
      KeyStore IP Address Type: IPV4
          Encryption Algorithm: AES
     Encryption Algorithm Mode: CBC
         Encryption Key Length: 256
              Master Key Label: DB2_SYSGEN_db2inst1_BLUDB_2021-03-19-13.57.02_A8CF4EED

The next step is to go to the source keystore and extract the secret key. If the source database is lost and there is no copy of the keystore, the backup is also lost because there is no way to decipher it.

Display the content of keystore.

gsk8capicmd_64 -cert - -list -db /mnt/blumeta0/db2/keystore/keystore.p12  -stashed

Certificates found
* default, - personal, ! trusted, # secret key
#	DB2_SYSGEN_db2inst1_BLUDB_2021-03-19-13.57.02_A8CF4EED

Yes, the wanted secret key still exists in the keystore. Now export it and transport it to the target database.

gsk8capicmd_64 -cert -export -db /tmp/keystore/keystore.p12 -stashed -label DB2_SYSGEN_db2inst1_BLUDB_2021-03-19-13.57.02_A8CF4EED -target secretkey.p12
Target database password :

The target database password is used to encrypt secretkey.p12 file. On the target host, create an empty keystore and reconfigure DB2.

gsk8capicmd_64 -keydb -create -db /mnt/blumeta0/db2/keystore/keystore.p12   -pw "secret" -type pkcs12 -stash

db2 update dbm cfg using keystore_location  /mnt/blumeta0/db2/keystore/keystore.p12  keystore_type pkcs12

Now import a secret key into the newly created keystore.

gsk8capicmd_64 -cert -import  -db  /mnt/backup/secretkey.p12 -pw "secret"  -target   /mnt/blumeta0/db2/keystore/keystore.p12 -stashed -label DB2_SYSGEN_db2inst1_BLUDB_2021-03-19-13.57.02_A8CF4EED  

And now restore the database again.

db2 restore db bludb from /mnt/backup taken at 20210425102241 no encrypt

Success.