Tuesday, January 20, 2015

Plain and clear: Native encryption in DB2

Native encryption in DB2
Built-in data encryption is a newly available DB2 feature as of today (see this updated fixpack summary page for fixpack 5). Here is a short report on how I tried and succeeded in setting it up and encrypting a new DB2 database on my Linux box. What I did is more or less to follow the implementation guide for native database encryption. So let's see how this worked out...

To use native encryption, i.e., encryption built into DB2 you need at least DB2 V10.5 with fixpack 5. The fixpack was released last December. If you are already on that software level, installation is (almost) complete. In most cases you only have to update the environment variable with path information for executables and libraries (PATH and LD_LIBRARY_PATH on Linux/UNIX). They have to include the "gskit" (Global Security Kit) that is shipped with DB2. Details are in the implementation guide. All I did for the setup was to edit the db2profile (or db2cshrc) file that manages modifications to the shell environment.

I ran into two issues that I could resolve:
  • When trying to call gsk8ver as a small test, linker errors were reported. This was called with another, incompatible gskit installation on my machine that was showing up in the environment before the DB2 variables were set. After removing the other kit, the problem was gone.
  • Even though I have a 64 bit Linux system, I had to set the library path to both the 64 bit and 32 bit libraries.
Once calling "gsk8ver" can be called successfully the system is ready for the actual setup of database encryption. You need a keystore on your machine and tell DB2 how to find it.

[henrik@mymachine]$gsk8capicmd -keydb -create -db db2pwstore.p12 -pw Str0ngPassw0rd -strong -type pkcs12 -stash

The above command creates the mentioned keystore, a "key database", with the name "db2pwstore.p12". The option "strong" causes the tool to check that the requiremnts for a strong password are met. The "stash" tells it to create a so-called stash file which allows to automatically provide the password, e.g., when DB2 starts up.

With the password database in place, DB2 needs to know about it. Else you recieve this error:

[henrik@mymachine]$ db2 create db enc1 encrypt
SQL1728N  The command or operation failed because the keystore could not be accessed. Reason code "1".

There are two new DB2 instance variables  (dbm configuration parameters) for encryption: KEYSTORE_TYPE tells which password type is used, KEYSTORE_LOCATION provides DB2 with the path to the key database we just created. Update the instance variables:

[henrik@mymachine]$ db2 update dbm cfg using keystore_type pkcs12 keystore_location /home/hloeser/db2pwstore.p12

Thereafter be sure to restart DB2 (db2stop / db2start). And finally (see CREATE DATABASE for the new ENCRYPT option):

[henrik@mymachine]$ db2 create db enc1 encrypt
DB20000I  The CREATE DATABASE command completed successfully.

[henrik@mymachine]$ db2 connect to enc1

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.5
 SQL authorization ID   = HLOESER
 Local database alias   = ENC1

Done. My database is encrypted. Because I am using a stash file (see above) I don't need to provide a password on startup. For environments where a higher degree of security is needed, there are other ways of providing the password. I plan to cover more details in upcoming blog posts. Today's entry should have provided you with a quick introduction into how to set up an encrypted database.


junsu's blog said...

Thank you for the good doc. Really useful and handy post.

masheed ullah said...

Thank you for the post.

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

I have generate keystore

gsk8capicmd -keydb -create -db db2pwstore.p12 -pw Str0ngPassw0rd -strong -type pkcs12 -stash

then update the instance variables

db2 update dbm cfg using keystore_type pkcs12 keystore_location /home/dbinst1/db2pwstore.p12

Now when I issue command

db2 backup database testdb encrypt encrlib 'libdb2encr.a'
SQLN2062N An error occurred while accessing media "libdb2encr.a". Reason code:"1".

Henrik Loeser (data_henrik) said...

Masheed, what operating system do you have? It could be that you need to specify "libdb2encr.so". If the backup needs to be compressed, libdb2compr_encr should be used.


masheed ullah said...

Its AIX 7.1.

If I want to take encrypted backup of existing database.
Will I drop and then create database with encrypt option. Or their is any option to take encrypted backup with out dropping/creating database.

Henrik Loeser (data_henrik) said...

On AIX you need to use libdb2encr.a or libdb2compr_encr.a. You can create an encrypted backup of a non-encrypted database. No need to recreate the database.


masheed ullah said...

As per the post by "Ember Crooks" http://db2commerce.com/2015/09/01/using-db2s-new-native-encryption-feature/
for existing database we have to backup, drop & restore database with encrypt option. Then backup will be encrypted.

Henrik Loeser (data_henrik) said...

No, Ember is not saying that. She explains the currently only way to transform an unencrypted into an encrypted database. If you only want to encrypt a backup, you can do it by using the new encrypt options and libraries. You need to be on DB2 10.5 fixpack 5 or higher.


masheed ullah said...

Henrik, We are on and db is licensed with encryption feature.

I am getting the following when taking backup of existing non-encrypt database on AIX 7.1

db2 backup database testdb encrypt encrlib 'libdb2encr.a'
SQL2062N An error occurred while assessing media "libdb2encr.a". Reason code: "1"

Henrik Loeser (data_henrik) said...

This looks like a problem with your system and the library not found (it is under sqllib/lib*). You could use "find" to try to locate it, check the library path settings, etc. If that didn't help, contact the IBM support or, for more ideas, ask on Stackoverflow.


masheed ullah said...

$ find / -name *db2encr* 2> /dev/null give me the below result


I try to backup database with the full path, but same error.

db2 backup database testdb encrypt encrlib '/opt/ibm/db2/V10.5/lib64/libdb2encr.a'
SQL2062N An error occurred while assessing media "libdb2encr.a". Reason code: "1"

masheed ullah said...

Henrik, the same command work on encrypted database. It takes encrypted backup.

db2 backup database testdb encrypt encrlib 'libdb2encr.a'

While it gives an error on non encrypted database.

Henrik Loeser (data_henrik) said...

Did you follow these steps...? http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.sec.doc/doc/t0061766.html?lang=en


Madhuri Jalli said...

I am also facing same issue. any thoughts?

Henrik Loeser (data_henrik) said...

What issues? Did you follow the steps?


Hesham said...

masheed ullah, I get this error:
SQL2062N An error occurred while assessing media "libdb2encr.a". Reason code: "1"

It happened to me in the next scenario:
Export the database encryption keys
Delete the keystore files
Create the keystore again
Recreate the instance and configure the keystore
import the exported keys to the keystore
Recreate the database from a backup
the backup is not encrypted but the databaase is.
If i don't delete the keystore and restore the backup database, then it works


Related Posts with Thumbnails