With fixpack 5 of DB2 10.5, IBM introduced Native Encryption for data at rest in DB2. This is a fairly significant new feature for introduction in a fixpack. It does require separate licensing – either the Advanced Edition of ESE or WSE or the separate purchase of the Native Encryption feature.
DB2 Native Encryption is transparent data encryption for data at rest. It does not encrypt data that is in flight or in memory. There are no application changes that are necesary, and it includes functionality for managing encryption keys. You don’t change data encryption keys, but instead can change the key used to access the data encryption keys – the key encryption key.
Planning
DB2 Native Encryption is NOT performance neutral. It is likely to impact performance, and that performance impact is expected to be “less than 100%”. There may be some areas where the impact is more noticeable than others. It largely impacts CPU time. If you implement Native Encryption on a system that already runs at 80% CPU utilization, bad things will likely happen. It is very strongly recommended that you do through performance testing before implementing it in production. The system I’m enabling it on is currently extremely over-sized, averaging LESS than 5% cpu utilization. Because of this, I’m not terribly worried about the impact, but I sure would be with a more reasonably sized system.
The client I’m working with now chose to purchase the Native Encryption feature to use with a standard WSE implementation. The program number to get from IBM is:
5725T25 IBM DB2 Encryption Offering
The code for Native Encryption is included in db2 10.5 fixpack 5, so there is nothing separate to install. To get the license file you’ll need, you’ll need to download the following part from passport advantage:
CN30DML IBM® DB2® Encryption Offering - Quick Start and Activation 10.5.0.5 for Linux®, UNIX and Windows®
If your DB server is not already on 10.5 fixpack 5, you’ll need to upgrade to it before implementing Native Encryption.
Implementation
The steps for implementing Native Encryption are pretty well laid out in the IBM DB2 Knowledge Center page on Native Encryption. EXCEPT if you copy and paste the command for creating the keystore. I did and got this error:
CTGSK3020W Invalid object: –strong
The problem is documented in the comments on this page. No idea why IBM hasn’t fixed the documentation yet. The ‘-‘ character before two of the options on this command is incorrect in the info center, and it’s barely visable as such. In my steps below, I use the correct kind of dash, so you should be able to copy and paste the below.
Here are the steps for encrypting an existing database – you must do a backup and restore to do it at this time. All actions here are done as the DB2 instance owner.
- Apply the license file – unzip/untar the dowloaded activation file and navigate to db2ef/db2/license, and issue:
db2licm -a db2ef.lic
- Ensure your PATH and library variables are set properly. To do this, I added the following lines to my DB2 instance owner’s .bash_profile (you’d use .profile on AIX):
PATH=$PATH:$HOME/sqllib/gskit/bin export PATH LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/sqllib/lib64/gskit export LD_LIBRARY_PATH LIBPATH=$LIBPATH:$HOME/sqllib/lib64/gskit export LIBPATH SHLIB_PATH=$SHLIB_PATH:$HOME/sqllib/lib64/gskit export SHLIB_PATH
- Next, issue the command to create your keystore. This is the one with the incorrect dashes in the IBM DB2 Knowledge Center:
gsk8capicmd_64 -keydb -create -db /db2home/db2inst1/pdesignkeystore.p12 -pw MfsWq9UntZGGhe96 -strong -type pkcs12 -stash;
There is absolutely no output returned by this command. You’ll likely want to change the location, and the password you feed into this.
- Update the dbm cfg with the keystore location:
$ db2 update dbm cfg using keystore_type pkcs12 keystore_location /db2home/db2inst1/pdesignkeystore.p12 DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
- Backup your database
db2 backup db sample to /db2backups compress without prompting
- Drop your database (man, this is hard to do – I still cringe whenever using a drop command)
$ db2 drop db sample DB20000I The DROP DATABASE command completed successfully.
- Restore your database, with the encrypt option
db2 "restore db sample from /db2backups taken at 20150827182456 encrypt without rolling forward without prompting"
Your database is now encrypted, congratulations!
In my case, I’m dealing with a small database, and I didn’t find my restore/encryption time of less than 10 minutes any different than a recent restore of the same database.
Remember DB2 will now encrypt every backup you take with the same encryption options you’ve set in the dbm cfg. This means that part of what you now need to backup is that keystore that you created. I think you’ll also want to store the keystore password somewhere, as you may need it.
I have so far found that these backups take longer than non-encrypted backups. The backup I took of a database before enabling Native Encryption took 4 minutes. The one afterwards took 11 minutes. You may want to test backup duration as a part of your performance testing process.
Next month, I’ll be implementing Native Encryption for an HADR database, and will blog about it, and the extra wrinkles that adds.
Thanks for the detailed post.I am want know reason behind dropping and restoring the database.
It is required for enabling native encryption on an existing databsae. I don’t know IBM’s reasoning behind this methodology. They decided to do it that way instead of providing a utility to encrypt the database.
Do we still have to use the encrypt scalar function to mask data? The data is visible unless you do that ( though the database is encrypted).
Right – this is encryption of data at rest. You do not have to use the encrypt scalar function. Any privileged database user will be able to see the un-encrypted data (db2 decrypts it for them), but if someone tries to just scrape the files from disk, they will not be able to read them with Native Encryption.
Thanks Ember
Thank you for the post. Please let me know, if I only want to take a backup of existing database. Will I drop and restore the database with encrypt option OR we can take backup with out dropping existing database.
You have to drop the database in order to enable database level encryption.
Ember, we want to take encrypted database backup only. We do not want to encrypt the database.
For taking encrypted database backup will database to be dropped or we can take encrypted backup with out dropping and restore database.
I have not tested an encrypted backup on a non-encrypted database. I know it is possible, and you should not have to drop the database to do it.
When I try to update 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
Do you have the right kind of single quote at the beginning and end of your value? It looks like one of Word’s modified quotes at the beginning.
Yes, I have re-execute the command, but still have same 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
I agree with you that it does not work, and don’t know the proper syntax to make it work. I’m not finding it myself. Are these really things you need to specify there if you have specified the keystore location? In examples I can find, I only see those specified on the backup/restore database command, not in the db cfg.
I have add ENCROPTS option in backup command as well.
db2 BACKUP DATABASE testdb TO /db2data_1/BACKUP ENCRYPT ENCRLIB ‘libdb2encr.so’ ENCROPTS ‘Cipher=AES:Key Length=256’
SQL0104N An unexpected token “Length=256” was found following “”.
Expected tokens may include: “INCLUDE”. SQLSTATE=42601
What fixpack of 10.5 are you on?
Fix pack 5
Thanks for your post..Its is very useful ..
I’ve planned to implement DB2 native security encryption in my environment which is (PRIAMRY+STANDBY+AUXILIARY STANDBY).
But Im worried about performance , can you just let me know how it would be impact on DB performance.
Kindly share me implemented steps for HADR enev In case if you have already tested.
I know I’m horribly late in responding to this comment. The following has great steps for HADR implementations of native encryption – even without outage. https://www.ibm.com/developerworks/community/blogs/81c130c7-4408-4e01-adf5-658ae0ef5f0c/entry/Enable_DB2_native_encryption_in_an_HADR_environment?lang=en
HADR servers do not have to use the same encryption key nor do they even have to be encrypted, technically. Though realistically, they should be.
The impact for HADR is still at “two digits” (10-99 %), only if you’re using SYNC would that have any possibility of being worse with HADR.
I’m getting SQL2062N error when trying to Restore the backup of an encrypted database from a different Linux server.
SQL2062N An error occurred while accessing media “libdb2encr.so”. Reason
code: “1”.
Have you set up the GSK and moved your key encryption key from the source server?
Thanks for the post.It is very useful.
But if the Big DB’s they need the Encryption.There will be performance effect for the Backup and Applications connecting.What is the best use for Encrypting the data for Huge Databases without effecting the Performance.
Hi
This is really helpful, and I am big fan.
But, I really do not see benefits of encrypting data on OS level (at rest), as every authorized user using db2 client will be able to see un-encrypted data. DB servers are protected enough, but I would like to have only sensitive data encrypted. There is no other way except using old fashioned encrypt/decrypt functions ? And more on encrypting certain fields on existing table please.
There are roles for different kinds of encryption. Certainly I see encryption of data in motion as often more critical, but it depends on what you’re trying to protect against. Some industries or security standards require encryption of data at rest.
Hi Ember, thanks for the detailed article. We do have a 120TB warehouse that may need to be encrypted for security regulation, we are running DB2 11.5.7 on AIX 7.2 and VCipher is enabled. We do have a DPF environment (13 nodes distributed into 4 VM’s on 4 different frames), with BLU active. I would like to know if you have some info regarding benchmarks we could use as reference regarding CPU consumption and I/O latency increase that we could expect for our system.
Regards, Mauro.