Adding an encrypted column to a table in Jitterbit App Builder

Application-level encryption can be used to protect sensitive data stored in table columns. Encryption and decryption take places within the App Builder business engine. Consequently, application-level encryption is vendor-neutral: App Builder supports application-level column encryption on all RDBMS providers.

To add an encrypted column to a table, start by signing into App Builder as an administrator.

1. Navigate to the App Workbench
2. Click the Tables tile
3. Identify the Table to add the Column to and click the corresponding Open Record icon
4. In the Columns panel, click the + Column button
5. Provide the column Name and select the following options:
   • Logical Data Type: String
   • Physical Data Type: Encrypted Text
6. Click the Save button
7. Click the Close icon (X) to dismiss the popup

Any values written to the column will be automatically encrypted. Values read from the column will be automatically decrypted.

If you have direct access to the database, you can confirm that the data is encrypted. Start by encrypting a value:

1. In the Table panel, locate the table and click the Results icon to view the table data
2. Click the Edit icon to modify an existing row
3. Provide a value for the encrypted column
4. Click the Save icon

Connect to the database using a tool like SQL Server Management Studio. Select the data from the table. The encrypted data will be prefixed with the byte sequence 0x09F0.

Using an encrypted table column

Encrypted columns can be used in one of several ways. Typically, a developer will create a data object which selects the encrypted column from the target table. Using roles-based security, the developer can limit access to the data object. The developer can then bind a panel to the data object, and a Text or Password control to the encrypted column. At which point, users with permission to the data object will be able to view and modify the plain text value.

In addition to binding controls to encrypted columns, it's also possible to create CRUD business rules which copy data to and from encrypted columns. CRUD business rules can be used for:

• Migrating data from unencrypted columns to encrypted columns (or vice versa).
• Coping sensitive data into or out of a third-party system.
• Passing sensitive data to a REST or OData web service endpoint.
• Finally, encrypted columns can be used by plugins. However, support for encrypted columns varies on a plugin-by-plugin basis. Consult the Plugin documentation for additional information.

Data encryption keys

Column data is encrypted with a Data Encryption Key (DEK). Each data source has its own set of rolling keys. Although any key can be used to decrypt data, only one key is used for encryption at any given time. A new DEK is created every 90 days.

DEKs are tied to the following attributes in the App Builder logical model:

• DataSourceId - The data source identifier isolates keys.
• TableId - The table identifier is used to create a derived key for the table.
• ColumnId - The column identifier is used to create a derived key for the column.

In addition, each column value has a unique initialization vector. As a result, the same plain text value will have a different binary cipher text in different rows.

Viewing data encryption keys

Because DEKs are used to encrypt data within the database, the cryptographic key material cannot be stored in the database. For this reason, DEKs are stored in one of several configurable locations. See Data Encryption Configuration for more information.

Even though the key material itself is not stored in the database, key attributes are visible from within the App Builder. To a data source's encryption key details, start by signing into App Builder as an administrator.

1. Navigate to the App Workbench
2. Click the Data Sources tile
3. Select the Relational database from the Data Source panel
4. Click the Encryption Keys button

The Data Encryption Keys page will list the existing key properties:

• Creation Date - The date and time at which the key was generated.
• Activation Date - The date and time at which the key was used to encrypted column data.
• Expiration Date - The date and time at which the key was no longer used to encrypt data.

Encryption and validation algorithms

Encryption provides confidentiality; validation, authenticity (a.k.a. tamper-proofing).

App Builder encrypts data using AES-256 in the CBC block cipher mode with PKCS #7 padding.

App Builder ensures the integrity of the encrypted data using HMAC-SHA256.

Known issues and limitations

The following list describes known issues an limitations with App Builder's implementation of column-level encryption.

• Only the logical data type String supports encryption.
• Only RDBMS data providers support encryption.
• A column cannot be converted to the Encrypted storage data type.
• A column cannot be converted from the Encrypted storage data type to another data type.
• Encrypted columns do not support Filter, Search, or Sorting.
• Controls such as pages, panels and lists cannot be bound on an encrypted column.
• Encrypted columns do not support translation.
• App Builder does not automatically migrate values encrypted with old keys: it is left to the developer to re-encrypt data periodically.
• Data cannot be migrated to/from encrypted columns using a migration rule. Developers must use CRUD business rules to move data to/from encrypted columns.
• Data cannot be copied to/from encrypted columns using CRUD database rules. Developers must use CRUD business rules to move data to/from encrypted columns.
• mvSQL expressions can only reference the binary cipher text, not the plain text value of an encrypted column.
• If a encrypted value cannot be decrypted, no error is displayed: the value appears to be NULL.