How to Encrypt DB2 Database using Centralized KeyStore

In blog How to Encrypt DB2 database we went through step by step process of configuring DB2 Native Encryption using Local KeyStore. Local KeyStore has overhead of backup and maintenance and in case Local KeyStore is not accessible due to any reason and you are unable to recover it then you cannot access your encrypted database, also restore from encrypted database backups will not be possible. To reduce this overhead you can use Centralized KeyStore. In this blog we will see step by step process to configure DB2 Native Encryption using Centralized KeyStore.

How to Encrypt DB2 Database using Centralized KeyStore?

Step1: Prerequisite - Digital Certificate

You can use self signed digital certificate as well but it is advised to use digital certificated signed by CA when using Centralized KeyStore.

Step 2: Include gskit in LD_LIBRARY_PATH, LIBPATH, SHLIB_PATH environment variable.

Update LD_LIBRARY_PATH, LIBPATH, SHLIB_PATH to include both 32 and 64 bit gskit path. Update DB2 instance user profile file to include below

PATH=$HOME/sqllib/gskit/bin:$PATH
export PATH
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/sqllib/lib64/gskit
export LD_LIBRARY_PATH
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/sqllib/lib32/gskit
export LD_LIBRARY_PATH
LIBPATH=$LIBPATH:$HOME/sqllib/lib64/gskit
export LIBPATH
LIBPATH=$LIBPATH:$HOME/sqllib/lib32/gskit
export LIBPATH
SHLIB_PATH=$SHLIB_PATH:$HOME/sqllib/lib64/gskit
export SHLIB_PATH
SHLIB_PATH=$SHLIB_PATH:$HOME/sqllib/lib32/gskit
export SHLIB_PATH

Step 3: Enable SSL

You can check blog DB2 SSL for detailed instructions on configuring DB2 SSL. Follow below quick step to enable DB2 SSL.
  • Create local keystore
gsk8capicmd_64 -keydb -create -db SSL_Keystore.p12 -pw ssl_p@ss -type pkcs12 -stash
  • Import certificate for SSL into Local KeyStore
gsk8capicmd_64 -cert -import -db ssl_certificate.pfx -pw P@ssw0rd -target SSL_Keystore.p12 -target_stashed
  • Verify certificate import is successful
gsk8capicmd_64 -cert -list -db SSL_Keystore.p12 -stashed
  • Update DBM CFG values
db2 update dbm cfg using SSL_SVR_KEYDB $HOME/.db2_ssl/SSL_Keystore.p12
db2 update dbm cfg using SSL_SVR_STASH $HOME/.db2_ssl/SSL_Keystore.sth
db2 update dbm cfg using SSL_VERSIONS TLSV12
db2 update dbm cfg using SSL_SVCENAME db2i3s
db2 update dbm cfg using SSL_SVR_LABEL db2ssl
  • Update registry vairable
db2set DB2COMM=SSL
  • Restart db2 instance
db2stop
db2start

 Step 4: Configure Encryption using Centralized KeyStore

  • Create local keystore

gsk8capicmd_64 -keydb -create -db TEMP_KeyStore.p12 -pw P@ssw0rd -type pkcs12 -stash

  • Create Master Key

gsk8capicmd_64 -secretkey -create -db TEMP_KeyStore.p12 -stashed -label NPMSTR -size 16

  • Import certificate for encryption into Local KeyStore

gsk8capicmd_64 -cert -import -target TEMP_KeyStore.p12 -target_stashed -db db2_encryption.pfx -pw P@ssw0rd

  • Verify certificate import is successful

gsk8capicmd_64 -cert -list -db TEMP_KeyStore.p12  -stashed

  • Create KMIP Configuration file

$ cat KMIP_KeyStore.cfg
VERSION=1
PRODUCT_NAME=KEYSECURE
ALLOW_KEY_INSERT_WITHOUT_KEYSTORE_BACKUP=TRUE
SSL_KEYDB=/home/db2i3/.db2_ssl/SSL_Keystore.p12
SSL_KEYDB_STASH=/home/db2i3/.db2_ssl/SSL_Keystore.sth
SSL_KMIP_CLIENT_CERTIFICATE_LABEL=db2ssl
MASTER_SERVER_HOST=192.168.2.160
MASTER_SERVER_KMIP_PORT=9300
CLONE_SERVER_HOST=192.168.2.170
CLONE_SERVER_KMIP_PORT=9300
CLONE_SERVER_HOST=192.168.2.180
CLONE_SERVER_KMIP_PORT=9300
CLONE_SERVER_HOST=192.168.2.190
CLONE_SERVER_KMIP_PORT=9300
ALLOW_NONCRITICAL_BASIC_CONSTRAINT=TRUE
COMMUNICATION_ERROR_RETRY_TIME=1
UNAVAILABLE_SERVER_BLACKOUT_PERIOD=300
ALL_SERVER_UNAVAILABLE_SLEEP=0
$
  • Migrate Local KeyStore to Centralized KeyStore

db2p12tokmip -from TEMP_KeyStore.p12 -to KMIP_KeyStore.cfg

  • Update DBM CFG values

db2 update dbm cfg using keystore_location /home/db2i3/.Key/KMIP_KeyStore.cfg keystore_type kmip

  • Restart db2 instance
db2stop
db2start

 Step 5: Encrypt Database

To encrypt existing database you need to take backup of existing database and restore it with encrypt option.

db2 backup db TESTDB compress
db2 drop db TESTDB
db2 restore db TESTDB taken at 20201005122701  ENCRYPT without rolling forward without prompting 

You can also create a new database with encryption enabled.

db2 create db ENCDB encrypt

Step 6: Verify Encryption enabled

You can either check Database Configuration (DB CFG)  parameter to check database encryption status or query SYSPROC.ADMIN_GET_ENCRYPTION_INFO() table function to get encryption details.

db2 get db cfg for TESTDB | grep -i ENCRYPT

db2 "SELECT substr(master_key_label,1,40) as MasterKey, substr(KEYSTORE_NAME,1,30) as KeystoreName  FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"



If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.

15 comments:

  1. PeterVB24 March 2021 at 20:29

    When we do a reset of our db cfg my database is not encrypted anymore. After this i cannot reconnect ... can someone help me ?

    C:\db2backups>db2 get db cfg for udb_xt |grep Encrypted
    Encrypted database = YES

    C:\db2backups>db2 reset db cfg for udb_xt
    DB20000I The RESET DATABASE CONFIGURATION command completed successfully.

    C:\db2backups>db2 get db cfg for udb_xt |grep Encrypted
    Encrypted database = NO

    C:\IBM\SQLLIB\BIN>db2 connect to udb_xt
    SQL1655C The operation could not be completed due to an error accessing data
    on disk. SQLSTATE=58030

    C:\IBM\SQLLIB\BIN>

    ReplyDelete
    Replies
    1. Sumit Choudhary24 March 2021 at 22:25

      You are on which version of DB2? I tried to reproduce this on DB2 11.5 GA and was unable to reproduce it.
      You need to restore your database from latest backup to get it working.

      Delete
    2. PeterVB25 March 2021 at 15:01

      We working in version 11.1.4
      But i did an upgrade to db2 11.5 GA and have the same problem.
      If i create a new database then i don't have the problem
      db2 create database udb_XT AUTOMATIC STORAGE NO alias udb_XT using codeset utf-8 territory BE encrypt
      But it's no solution.

      When resetting to factory these are the parameters that were different

      Checking which options are different from factory settings
      #Options different from factory settings: 23 (AUTO_MAINT,AUTO_RUNSTATS,AVG_APPLS,DATABASE_MEMORY,DBHEAP,DFT_PREFETCH_SZ,LOCKLIST,l,LOGBUFSZ,LOGFILSIZ,LOGPRIMARY,LOGSECOND,MAXAPPLS,MAXFILOP,MAXLOCKS,NUM_IOCLEANERS,PCKCACHESZ,REC_HIS_RETENTN,SELF_TUNING_MEM,SHEAPTHRES_SHR,SORTHEAP,STMT_CONC,WLM_AGENT_LOAD_TRGT)

      We are settings this parameters to

      db2 "UPDATE DATABASE CONFIGURATION FOR test2 USING AUTO_MAINT OFF AUTO_RUNSTATS OFF AVG_APPLS 50 DATABASE_MEMORY 141031 DBHEAP 26000 AUTOMATIC DFT_PREFETCH_SZ 32 LOCKLIST 9216 AUTOMATIC LOGARCHMETH1 LOGRETAIN LOGBUFSZ 4096 LOGFILSIZ 1024 LOGPRIMARY 6 LOGSECOND 30 MAXAPPLS 250 MAXFILOP 10000
      MAXLOCKS 98 AUTOMATIC NUM_IOCLEANERS 2 AUTOMATIC PCKCACHESZ 15000 REC_HIS_RETENTN 366 SELF_TUNING_MEM ON SHEAPTHRES_SHR 5000 AUTOMATIC SORTHEAP 755 AUTOMATIC STMT_CONC LITERALS WLM_AGENT_LOAD_TRGT 12 AUTOMATIC)
      But on the new created database still no problem.
      Only on the existed :)...

      These steps we use to put our system into encryption

      DB2 FORCE APPLICATIONS ALL
      DB2STOP
      "C:\Program Files\IBM\gsk8\bin\gsk8capicmd_64" -keydb -create -db C:\db2backups\pdesignkeystore.p12 -pw "This66Must@Be&Strong!Password_88" -type pkcs12 -stash
      db2 terminate -> Is nodig dat het volgende commando onmiddellijk van toepassing is...
      db2 update dbm cfg using keystore_type pkcs12 keystore_location C:\db2backups\pdesignkeystore.p12
      db2start
      Db2 BACKUP DATABASE UDB_XT TO "C:\Db2backups"
      Db2 drop database udb_xt
      Db2 restore database udb_xt encrypt
      Db2 rollforward database udb_xt to end of logs and stop
      Db2 connect to udb_xt
      db2 "SELECT * FROM TABLE (SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"





      Delete
    3. Sumit Choudhary25 March 2021 at 15:18

      Since for new database I presume on 11.5 GA you don't have this issue, I assume it might be bug with 11.1.4. I would suggest to create a SR with IBM to see if it is known issue and if there is patch or workaround available to fix this.

      Delete
  2. PeterVB26 March 2021 at 12:57

    Hi,

    I found the problem.
    My databases where created with AUTOMATIC STORAGE NO.
    So i'm now looking if i can change this parameter to prevent the problem.
    Best regards,
    Peter

    ReplyDelete
  3. Unknown26 March 2021 at 19:57

    Do you have a good site where i can change my databases to AUTOMATIC STORAGE YES ?

    ReplyDelete
    Replies
    1. Sumit Choudhary26 March 2021 at 20:11

      Its not possible to change your database from AUTOMATIC STORAGE NO to AUTOMATIC STORAGE YES.
      You can create a blank database with automatic storage YES and restore you database using into clause to achieve that.

      e.g db2 drop db udb_xt
      db2 create db udb_xt automatic storage YES encrypt
      db2 restore db udb_xt from taken at into udb_xt

      Delete
    2. Sumit Choudhary26 March 2021 at 21:37

      I tried reproducing this issue on DB2 11.1 in windows environment by creating new database with automatic storage NO but I was unable to replicate it. Are you sure automatic storage no is the actual culprit.

      I would suggest to create a SR with IBM to know root cause.

      Delete
  4. PeterVB30 March 2021 at 13:55

    You are right it is not the AUTOMATIC STORAGE NO.
    I must pass head of development to ask if they create a SR with IBM :(... That can take a while because i had a former situation and i waited more then 6 months for an answer.

    I will keep on searching for the problem because we prepare our databases in a way that causes this error...

    021-03-30-09.37.38.703000+120 I431912F628 LEVEL: Severe
    PID : 3188 TID : 3868 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : UDB_XT
    APPHDL : 0-7 APPID: *LOCAL.DB2.210330073723
    AUTHID : GETADMIN HOSTNAME: CleanWindows
    EDUID : 3868 EDUNAME: db2agent (UDB_XT) 0
    FUNCTION: DB2 UDB, buffer pool services, sqlbReadPage, probe:1199
    MESSAGE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad"
    DIA8400C A bad page was encountered.
    DATA #1 :
    Converting to non-critical read error

    2021-03-30-09.37.38.703000+120 E432542F689 LEVEL: Warning
    PID : 3188 TID : 3868 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : UDB_XT
    APPHDL : 0-7 APPID: *LOCAL.DB2.210330073723
    AUTHID : GETADMIN HOSTNAME: CleanWindows
    EDUID : 3868 EDUNAME: db2agent (UDB_XT) 0
    FUNCTION: DB2 UDB, buffer pool services, sqlbReadPage, probe:1199
    MESSAGE : ADM6006E The database manager encountered an error while reading
    page "0" from table space "0" for object "1" (located at offset "0"
    of container "C:\DB2\NODE0000\SQL00001\SQLT0000.0\SQL00001.DAT").

    I will search for the statements that they do. If i know more i keep you posted.

    Best regards,
    Peter

    ReplyDelete
    Replies
    1. Sumit Choudhary30 March 2021 at 14:38

      IBM SR team specifically for DB2 LUW does take a while to reply. You might have to follow up, work with IBM SR duty manager to prioritize to get the solutions.

      Was there any other operation performed after db2 reset db cfg?

      Delete
    2. PeterVB31 March 2021 at 18:59

      Hey,

      Before they want to go for me to ibm i must have a clean simulation. And i succeeded ! These are the steps :

      Install clean windows (windows 7 pro x64)

      Installation of db2 software v11.1.4fp6_ntx64_universal_fixpack

      logon with local db2admin instance owner
      db2 create database udb_xt
      db2stop
      db2start

      Configure system for encryption

      "C:\Program Files\IBM\gsk8\bin\gsk8capicmd_64" -keydb -create -db C:\temp\keystore.p12 -pw "Password" -type pkcs12 -stash
      db2 update dbm cfg using keystore_type pkcs12 keystore_location C:\temp\keystore.p12
      db2stop
      db2start

      Encryption of existing database

      db2 backup database udb_xt
      db2 drop database udb_xt
      db2 restore database udb_xt encrypt
      db2 get db cfg for udb_xt (here you will read Encrypted YES)
      db2 reset db cfg for udb_xt
      db2 get db cfg for udb_xt (here you will read Encrypted NO)
      db2stop
      db2start

      db2 connect to udb_xt (this will not succeed anymore).

      ----------------

      We hope to get a quick response from ibm :)

      But i'm not 100% because this sounds to easy... so i think we are doing something wrong. If we do not a database restore but go further with a db2 create database udb_xt encrypt... everything is ok. But then also if i do restore INTO i have the same problem.

      Best regards,
      Peter

      Delete
  5. PeterVB31 March 2021 at 16:56

    This comment has been removed by the author.

    ReplyDelete
  6. PeterVB31 March 2021 at 17:40

    This comment has been removed by the author.

    ReplyDelete
  7. PeterVB10 June 2021 at 11:16

    Hi,

    APAR IT37088 is created for this and will be release very soon in the next fixpack.

    Best regards,
    Peter

    ReplyDelete