Virtuoso “Encryption At Rest” (EAR) Usage Guide

Virtuoso “Encryption At Rest” (EAR) Usage Guide


New “Encryption At Rest” (EAR) functionality that provides data encryption, at the table level, using the Advanced Encryption Standard (AES).
Encryption key length support includes 128-, 192-, and 256-bit which are configured using new entries in the Virtuoso configuration file (virtuoso.ini) or at runtime using new built-in stored procedures.


“Encryption At Rest” (EAR) prevents data visibility in the event of its unauthorized access or theft, and is commonly used to protect data in persistent form (at rest) following secure transmission using the Transport Layer Security (TLS) protocol.


AES is a symmetric encryption system based on a single key (various sizes) functionaing as a shared secret during the act of encryption or decryption. This key is identified to a Virtuoso instance via a configuration file entry or at runtime using buiilt-in stored procedures.

The process breaks down as follows:

  • data stored on disk pages is encrypted using AES. 128-bit, 192-bit, and 256-bit symmetric keys;
  • each encrypted database page has a flag that indicates encrypted state;
  • when reading an encrypted page from disk, content decryption succeeds if the current encryption key is valid for the encrypted page; otherwise it will fail with a decryption error;
  • on startup, if encryption key identification is handled via the virtuoso INI file, Virtuoso will check whether current and previous encryption keys match, using the following approach:
    1. If the key is set for the first time, an id encrypted with the key is written into the database cfg page
    2. On subsequent startup, when reading the database cfg page, the current key is tested against the encrypted ID. If the current key does not match the previous key (i.e., if the ID cannot be decrypted), the current key will be unset
    3. If the key is determined to be unset, queries on an encrypted table will fail with an error 42000: VD [Virtuoso Server]ENC00: The DB key is not set.

Relational Table Encryption

This is achieved by a new ENCRYPT keyword added to the CREATE TABLE CLAUSE of a table creation statement.



Encrypting Relational Table

An existing or new database can have encryption enabled on it, although note in the case of an existing database only the data added post encryption being enabled will be encrypted. Thus, migration of existing data entails:

  1. Creating a new Encrypted Table
  2. Copying data over to the newly Encrypted Table
  3. Dropping the old Table

Encrypting RDF Data

RDF support in Virtuoso is implemented via a collection of purpose-specific Relational Tables. Thus, EAR is enabled via the following commands:

;; Shutdown and Restart Virtuoso

Encrypting an existing RDF System

Data Reloading from RDF Documents

  1. Create a new Virtuoso DB instance
  2. Encryption-enable RDF System Tables
  3. Load data from RDF Documents using the Virtuoso Bulk Loader

Data Reloading across existing systems

  1. Create a new Virtuoso DB instance
  2. Encryption-enable RDF System Tables
  3. Load data from existing database instance to new instance using an RDF dump and reload sequence; SPARQL INSERT, Sponger Crawling, or Custom Stored Procedure

Setup Steps

Encryption should be set up by the DBA before any operations are attempted on encrypted tables, in one of three ways:

  1. The Virtuoso config file (virtuoso.ini) setting can be used for isolated environment, e.g., for testing.
EncryptKey             = ieQynrlXqTYIDEk/gssgS1z/ndcNTOIRrj3w2ZGtTzU=
  1. Use the Virtuoso license file key for the database encryption key, also controlled through the Virtuoso config file (virtuoso.ini), which will use AES128 key of the license file.
EncryptKey             = license-file ; which comprises an AES 128 symmetric encryption key
  1. Set the database key manually using the xenc_set_db_key() function, either –
  • on its own, as in –
  ( 'ieQynrlXqTYIDEk/gssgS1z/ndcNTOIRrj3w2ZGtTzU=' 
  • with the KMIP client plugin ( ), as in –
  ( encode_base64
    ( CAST
      ( kmip_get
        ( 'KMIP server address', 
          'KMIP server port', 
          'key id' 
        ) AS VARCHAR

Key Security & Management – Using Key Encryption Key

The security of a Database Encryption Key (DEK) is subject to how access is managed, like any other protected resource. For instance, storing it on the same machine as a Virtuoso Database and associated configuration files isn’t optimal, which is where Key Encryption Keys (KEKs) come into play.

As is already widely used in the world of PKI, an additional Asymmetric Key (conforming to RSA or DSA standards) can be used to encrypt a DEK as part of a more secure Key Management and Protection scheme; in addition, keeping the DEK protected using PKI also protects against the inconvenience associated with symmetric key changes that ultimately require data reloads. Wrapping a DEK using a KEK solves these problems i.e., the DEK stays constant while KEKs can be rotated as required.

The Key Wrapping scheme described above can be achieved using Key Management Interoperability Protocol (KMIP) compliant Key Management Service as outlined in the following steps:

  • Create a DEK using the various options supported by Virtuoso
  • Generate KEK using KMIP API provided by your KMIP Server, Virtuoso XENC API, or OpenSSL command line utility.
  • Encrypt the DEK with KEK, export encrypted DEK as ASN binary structure (DER) and encode as base64 string.
  • Configure KMIP interaction config files
; Via KMIP KEK via plugin 
WrappingKey               = kmip:// ; URI that denotes a Private Key managed by a KMIP server (in this example "7" is the actual Private Key ID component of the URI)
EncryptKey                = xWX9NTng...H7acgRklA== ; base64 representation of DEK encrypted using Private Key
; Via KEK stored in file id_rsa.key
WrappingKey              = id_rsa.key ; -- path to the Private Key file, so take measures to protect access via file permissions etc 
EncryptKey               = Yav4RAC...SYsRoCX8o= 
; Via Plain key
EncryptKey               = ieQynrlXqTYIDEk/gssgS1z/ndcNTOIRrj3w2ZGtTzU=

Sample kmip.cfg file:

Certificate = /path_to_keys/certificate.pem ; Certificate that comprises Public Key
KeyFile = /path_to_keys/key.pem ; secure PEM that comprises Private Key 
CAlist = /path_to_keys/ca-chain.pem ; Certificate Authority (CA) Trust Chain for Certificate Verification

Scripts to create KEK and wrapped DEK.

Using openssl command line utility and bash utils:
(copy&paste to a bash script, or download the attachment.


while [[ $name = "" ]]; do
echo -n "Enter key name: "
   read name

if [ -f "$key.asc" -o -f "$kwk.pem" ]; then
echo "File alreay exists : $key"
exit -1
echo -n "Enter password (or enter to use default 'secret'): "
read pass
if [[ $pass = "" ]]; then

# create a 256 bit AES key with random salt and `password` 
openssl enc -aes-256-cbc -k password -md sha256 -pbkdf2 -iter 1 -P > "$key.asc"
#openssl enc -aes-256-cbc -k "$pass" -P > "$key.asc"
# encode key material as base64 string
cat "$key.asc" | grep 'key=' | sed 's/key=//' | xxd -p -r | openssl base64 >  "$key.b64"

# create a wrapping key (KEK) RSA 2048 bit

# at this step if KMIP server is used to keep KEK should
# check PyKMIP server and client documentation for details how to setup or ask sys admin to do that
# ./
# ./ [priv-key-index] | xxd -p -r | openssl rsa -inform der > "$kwk.pem"

openssl genrsa 2048 2>/dev/null > "$kwk.pem"

# wrap the AES key with RSA KEK, use RSA v1.5 (PCKS#1) padding (IMPORTANT)
cat "$key.b64" | openssl base64 -d | \
    openssl rsautl -inkey "$kwk.pem" -pkcs -encrypt | \
    openssl base64 | tr -d '\n' > "$key-wrapped.b64"

echo "The DB key is in $key.b64, Wrapping key is $kwk.pem"
echo ""
echo "Insert these lines in the config file"
echo ""
echo "WrappingKey = $kwk.pem"
echo "EncryptKey = $(cat $key-wrapped.b64)"
echo ""
echo "bye"

Using Virtuoso PKI functions and Virtuoso/PL:
(use command line isql tool connected to an existing Virtuoso server instance:

xenc_key_AES_read ('key-aes-256', encode_base64 (subseq (PBKDF2_HMAC ('sha256', 1, 'password', 'salt'), 0, 32)));
-- OR xenc_key_AES_create ('key-aes-256', 256, 'password', 'sha256');

-- this next value can be used as non-wrapped DEK 'EncryptKey' in the ini
select xenc_key_serialize ('key-aes-256');

xenc_key_RSA_create ('kwk-rsa-2048', 2048); -- set WrappingKey = kwk-rsa-2048.pem in the ini
string_to_file ('kwk-rsa-2048.pem',xenc_pem_export ('kwk-rsa-2048'), -2); -- RSA KWK 
-- the next value to be used as wrapped 'EncryptKey' in the ini
select encrypt (decode_base64 (xenc_key_serialize ('key-aes-256')), 'kwk-rsa-2048'); -- Wrapped DEK (1.3 KB)

KMIP Encryption Configuration Using OpenSSL


  • OpenSSL
  • Python
  • Python PyKMIP module

Obtain the following from the KMIP Administrator for the envirnment:

  1. KMIP server address (kmip_address)
  2. KMIP server port (kmip_port)
  3. KMIP client certificate (client_cert.pem)
  4. KMIP CA certificate (kmip_ca.pem)

Setup a KMIP config file kmip.cfg with client certificate details and place in the Virtuoso databasedirectory:

Certificate = client_cert.pem
KeyFile = client_cert.pem
CAlist = kmip_ca.pem

Setup a python KMIP config file pykmip.conf with details for accessing the KMIP server and place in the Virtuoso database directory:


Generate Data Encryption Key as a symmetric AES key using OpenSSL with the command:

openssl enc -aes-256-cbc -k password -md sha256 -pbkdf2 -iter 1 -P  | grep 'key=' | sed 's/key=//' | xxd -p -r > demo-dek.asn

and keep the generated AES key (demo-dek.asn) in a secure place for future use.

Using the (482 Bytes) python script run the following to command to generate a KMIP Key ID:


This command generates public and private KMIP key ids which are the first and second values returned by the python script respectively, for example:

$ python3 
('7', '8')

The (385 Bytes) python script can then be run to generate the Encryption private key RSA certificate:

python3 ./ <kmip_private_key_id>  | xxd -p -r | openssl rsa -inform der > demo-rsa-priv-kwk.pem

where kmip_private_key_id is the KMIP private key id generated previously.

Then from the RSA certificate generate a base64 Enryptkey value for addition the virtuoso.iniconfig file:

openssl rsautl -encrypt -in demo-dek.asn -inkey demo-rsa-priv-kwk.pem | openssl base64 | tr -d '\n' && echo

Typical output being:

$ openssl rsautl -encrypt -in demo-dek.asn -inkey demo-rsa-priv-kwk.pem | openssl base64 | tr -d '\n' && echo

The demo-rsa-priv-kwk.pem can be removed once the Encryptkey value has been generated and saved.

Add the KMIP server access details (kmip_address & port), private key id and Encryptkey value to the virtuoso.ini config file in the [Parameters] section as follows:

WrappingKey  = kmip://<kmip_address>:<kmip_port>/<kmip_private_key_id> ; URI that denotes a Private Key managed by a KMIP server 
EncryptKey  = csD3scKSS2HQ8FOSaCPWPLIclFWGyrv3R9wy6DwYjMcob0IOltFgrZgYXM+bmp8HoyapZE0HX0Knex90FWAFAArsl3mK14HMxdk5zGCAOWwcEtV9Z3v9ClK/kuRddlElAUih58/MDYNi7euGyuwc++lDbs8zMuhu8cW86OjLJDmOPDEiR/3osdTAAZo5lux2nNwLEcPYVScQ+RQo0ImOBdbKOYlM/SM7+quiXv2WGvqQqtTykpFGfIZQZ/jBlrb6hxrq4ZVG5Ic0+0f20SxcJQaRbTtcpRIJ+7TfOh0BblH86xdAMYywsSPQkvJoko1LPMcqtcqQdSn3051OWK0yfA== ; base64 representation of DEK encrypted using Private Key

Start the Virtuoso server and it is ready for use with database encryption.

Simple Usage & Verification Exercise

To validate the encryption at rest (EAR) feature is enabled in a Virtuoso instance a simple test that can be performed is:

  1. Use the "EncryptKey = license” setting in the “[Parameters]” section of the “virtuoso.ini” configuration file, which is the easiest method to enabling the database encryption.
  2. Create and encrypted a table by adding the keyword ENCRYPT to the end of the table creation command:
create table <table-name> (col1 datatype, col2 datatype ... ) ENCRYPT;
  1. Insert some text string data into the table
  2. Run the Virtuoso checkpoint; command to write all data to the database file and shutdown the database
  3. Run the Linux strings command against the virtuoso.db database file to check if the string text inserted previously can be found, which it should not indicating the data in the table is encrypted.

For example:

SQL> create table T1 (ID integer not null primary key, STRING1 varchar(3)) ENCRYPT;

Done. -- 13 msec.
SQL> insert into T1 values (1,'HFW'); 

Done. -- 0 msec.
SQL> select * from T1;
ID                STRING1

1                 HFW

1 Rows. -- 0 msec.
SQL> checkpoint;

Done. -- 91 msec.
SQL> shutdown,

Use the Linux strings command to attempt access to a string pattern in the database file (e.g., HFW):

ubuntu:~/v83-EAR/database$ strings virtuoso.db | grep HFW

There shouldn’t be a string search match since the keyword in question no longer exists in unencrypted form.