Monday, December 20, 2010

Using Strong Encryption with Data Dictionaries

Strong encryption is a new option available as an add-on that you can purchase for Advantage 10.1 and newer. This post will walk through the steps of creating a new data dictionary using the strong encryption and then converting an existing data dictionary to use strong encryption. I would recommend that you read my previous post Advantage Strong Encryption Overview or review the various encryption topics in the help file prior to reading through this post.

Creating a Strongly Encrypted Data Dictionary

StrongEncrypt_ErrorWhen creating a data dictionary with strong encryption you must specify an additional password to encrypt the database. This password is in addition to any user passwords or a table encryption password. This password is also required when connecting to the dictionary. You also must specify the encryption type to be used. These two new options have been added to the CREATE DATABASE statement and the new AdsDDCreate101 ACE function. These options are also available when using Advantage Data Architect (ARC).

Encryption options for the dictionary and tables are located on the Security tab of the Create Database dialog in ARC. The default encryption type is the RC4 encryption which is included with Advantage. You can now also select AES128 or AES256, in order to use these encryption types you must have the OpenSSL libraries ( purchased separately ) in your search path or application directory. The OpenSSL libraries are required by both the client and the server when using strong encryption. A 7160 error will occur if the OpenSSL libraries cannot be found on the server. If the OpenSSL libraries are not found on the client a 6323 error will be returned. ARC provides a custom error message (see right) if the libraries cannot be located. Instructions for distributing the OpenSSL libraries are included with the strong encryption download. After selecting the encryption option you will need to enter an encryption password as shown below.


The encryption password can be supplied as the value for the DDPassword connection option. However, we do not recommend sending this password with every connection when using remote server. Once the server has generated the keys for accessing the encrypted database they are cached and do not need to be regenerated unless the encryption type is changed. Passwords for strongly encrypted dictionaries should be stored on the server using the SE_PASSWORDS configuration parameter. The keys for accessing the encrypted dictionary will be generated when the server starts up and therefore the client does not need to provide the DDPassword.

Changing Encryption Type of an Existing Data Dictionary

In previous versions of Advantage ( 10.0 and below ) you could only encrypt a data dictionary when it was created. There was no mechanism for encrypting the data dictionary files ( ADD, ADM, AI ) after they were created. With version 10.1 and later you can change the encryption type of the data dictionary files using the sp_SetDDEncryptionType system procedure. This procedure can also decrypt the dictionary files.

Setting the encryption type encrypts the data dictionary files and sets the encryption type for all of the tables associated with the data dictionary. Therefore, all of the tables associated with the dictionary must be decrypted before using the sp_SetDDEncryptionType procedure. After running the procedure tables can be encrypted using ARC or using the sp_EncryptTable system procedure. It is very important to note that all user passwords will be lost when you change an existing dictionary's encryption type. Data dictionaries do not store user passwords it only stores password verification, therefore passwords cannot be recovered.

The procedure takes five parameters; Path to the dictionary, AdsSys password, DDPassword used to encrypt the files, Encryption type ( RC4, AES128 or AES256 ) and whether or not to encrypt the dictionary. This procedure requires exclusive access to the data dictionary so you cannot have any active connections to the data. It is probably easiest to make a connection to the directory containing the dictionary and then running the procedure. An example of this procedure is shown below.

// Encrypt the data dictionary files with 256-bit AES encryption
EXECUTE PROCEDURE sp_SetDDEncryptionType('Sample.add', 'password', 
                                         'StrongPassword', 'AES256', TRUE)

// Remove encryption from the data dictionary
EXECUTE PROCEDURE sp_SetDDEncryptionType('Sample.add', 'password', 
                                         'StrongPassword', 'RC4', FALSE)

You can verify the type of encryption that the dictionary is using with the sp_GetSecurityInfo system procedure. This procedure returns information about the various security options configured for the current connection. A screenshot of the security configuration for the example dictionary is shown below.


In my next post I will discuss the new connection options related to strong encryption.

1 comment:

J (Encrypted Flash Drive Guy) said...

I think it is a great step to encrypt data with data dictionary. New options added in the “Create Database” are really effective and useful. I think that changing encryption type of an existing data dictionary files is such a great idea but it is harm to losing the passwords when there is change in an existing dictionary’s encryption type.