-1

I want to take encrypted backup of my existing database which is not encrypted.

db2 backup database sample encrypt does not work, it gives me an error:

"SQL0104N An unexpected token "encrypt" was found following "SAMPLE". Expected tokens may include: "USER". SQLSTATE=42601"

DB version is 10.5.0.3.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Masheed
  • 35
  • 3
  • 13
  • Go ahead then taking that backup... :) Or ask a question here. – data_henrik Dec 11 '15 at 14:44
  • db2 backup database sample encrypt does not work, it give me an error "SQL0104N An unexpected token "encrypt" was found following "SAMPLE". Expected tokens may include: "USER". SQLSTATE=42601" – Masheed Dec 11 '15 at 15:13
  • I'm not sure encryption was generally available before fix pack 5. You should install the latest fix pack anyway. – mustaccio Dec 11 '15 at 16:39
  • Thank you for the reply. Just for confirmation, after upgrading to Fixpack 5. We can encrypt database backup of existing database with command `db2 backup database sample encrypt` – Masheed Dec 11 '15 at 17:26

2 Answers2

2

As @mustaccio states, the Native Encryption feature was not available until DB2 10.5 Fixpack 5.

In addition, you must be licensed for the feature; It's included with DB2 Express-C, DB2 Advanced Workgroup Server Edition and DB2 Advanced Enterprise Server Edition. If you are running other editions (Express, Workgroup Server Edition or Enterprise Server Edition), you'll need to purchase a license for the feature.

Once your environment is sorted, to back up a non-encrypted database you will need to perform a few configuration steps:

  1. Create PCKS#12 keystone
  2. Generate a master key and add it to the keystore
  3. Set the KEYSTORE_TYPE and KEYSTORE_LOCATION database manager configuration parameters
  4. (Optional) Set the ENCRLIB and ENCROPTS database configuration parameters.

If you do not set the database configuration parameters, you'll have to specify them in the backup database command. At a minimum, you must have the Master Key Label option set to tell DB2 which master key to use for encrypting the data encryption key.

If you set the database configuration parameters, all database backups will be encrypted regardless of whether you specify the encrypt option.

Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
  • If database is already licensed. Will we need a separate license to purchased for encryption feature??? – Masheed Dec 11 '15 at 18:50
  • That depends on what edition of DB2 you have. If you are running Express Edition (not Express-C), Workgroup Server Edition or Enterprise Server Edition, then you'll need to purchase the *IBM Encryption Offering* – Ian Bjorhovde Dec 11 '15 at 19:12
  • After license to implement encryption feature, now I receiving the following error. `db2 backup database testdb encrypt encrlib 'libdb2encr.a` SQL2062N An error occurred while assessing media "libdb2encr.a". Reason code: "1" – Masheed Dec 17 '15 at 06:06
  • `db2 backup database sample encrypt encrlib 'libdb2encr.a'` command successfully completed on encrypted database on same instance. Where it give me error SQL2062N An error occurred while assessing media "libdb2encr.a". Reason code: "1" on `non encrypted database` – Masheed Dec 17 '15 at 15:07
  • That's because DB2 doesn't know what master key to use for encrypting the backup. For an encrypted database DB2 will use the master key already established for the database by default, but for a non-encrypted database it can't. So you have to specify `ENCROPTS` and the name of the master key you want to use. – Ian Bjorhovde Dec 17 '15 at 22:02
  • echo $LD_LIBRARY_PATH `:/home/db2inst1/sqllib/lib64/gskit` `db2 get dbm cfg |grep KEYSTRE Keystore type (KEYSTORE_TYPE) = PKCS12 Keystore location (KEYSTORE_LOCATION) = /home/db2inst1/mykeystore.p12` Generate a 256bit (32byte) key by selecting 32 bytes from /dev/random `head -c 32 /dev/random > /tmp/cccl` then Put password in file /tmp/ccc1 `gsk8capicmd_64 -secretkey -add -db /home/db2inst1/ccardskeystore.p12 -file /tmp/ccc1 -label inst1.bkup -pw Str0ngPassw0rd` Still got the same error – Masheed Dec 18 '15 at 10:38
  • `db2 "backup database testdb encrypt encrlib 'libdb2encr.a' encropts 'Cipher=AES:Mode=CBC:Key Lenght=256:Master Key Label=inst1.bkup'"` SQL2062N An error occurred while accessing media "libdb2encr.a". Reason Code: "1"` – Masheed Dec 18 '15 at 10:39
  • When I try to set parameter ENCROPTS for existing non encrypted database it give me an error: `$ db2 update database cfg for testdb using ENCROPTS 'CIPHER=AES:MODE=CBC:KEY LENGTH=256' ` SQL0104N An unexpected token "LENGTH=256" was found following "". Expected tokens may include: "ALT_COLLATE". SQLSTATE=42601 How can I update this parameter? – Masheed Dec 23 '15 at 17:55
1

DB2 encryption feature is introduced in 10.5 fix pack 5.

Masheed
  • 35
  • 3
  • 13