Application Development

Column Encryption

Column-level encryption is used to deny unauthorized users and users with access to the underlying database visibility to information of a sensitive nature.

Terminology

  • FIPS 197 Compliance – Federal Information Processing Standard 197 is a US Government standard that provides best practices for implementing crypto algorithms, handling key material and data buffers, and working with the operating system.
  • Decrypt/Pass – When reading data from an encrypted column, the decrypt mechanism is designed to look at the data, find the magic marker and attempt to decrypt the data. If the magic marker does not exist, there is no attempt to decrypt the data and the data is preserved as is.

When encrypting a column:

  • All encryption and decryption is performed at the application level. Data entered into the database outside of the Stewardship Tier-specific mechanism will be seen as plain text because this is application-level encryption and decryption.
  • Triple DES or AES-256 is used.
  • Encryption Keys are stored in the database. Each instance of Stewardship Tier can have one or more encryption keys that can be used to encrypt one or more columns.
  • Proprietary keys are protected by a Site Master Password (which is system-generated if one is not specified) that must be identical across all servers for a specific instance. The Site Master Password is stored in web.config. The system-generated Site Master Password is based on the server name and the database; therefore, all host files or DNS aliases must be in place and identical across machines.
  • Users only need rights to view the page to view the encrypted data.
  • Encryption is available to developers through the plugin API with encrypt and decrypt/pass. To use API, the KeyID must be known (located in the Key table, GUID).
  • Encrypted columns must be nullable and be configured with data type nvarchar. The size of the column must be between 128 and 256.
  • WebApps with encrypted data must be decrypted before a CTS package is built. Move the package, then re-encrypt.
  • Database-level mechanisms cannot be used to read or write encrypted data. These operations must be performed by plugins or through the Stewardship Tier front end.
  • Joins, search or indexing are not supported on encrypted columns.
  • Once data is encrypted with a specific key, the key cannot be deleted until the data is decrypted.

This section contains the following topics:

Encrypt a Column

To encrypt a column:

  1. Click Admin > Data Sources on the Navigation pane.
  2. Click Encryption for the WebApp.
  3. Select the name of the table that contains the column from Table Name list box.
  4. Select the column name to encrypt from Column Name list box.
  5. Select System Administration Passwords from Key ID list box.
  6. Click Save.
  7. Click Enable button under the Encryption column to encrypt the values.

Decrypt Data

On the Decrypt page, users can perform an ad hoc decryption of encrypted data. For instance, when reviewing data in SQL, a user may need to know the value of one of the encrypted columns. The user could paste the encrypted value into this page to view the decrypted value.

The Decrypt page can also be used to test the decryption process to ensure it is working and set up correctly.

To perform ad hoc decryption:

  1. Select Admin > Configuration > Encryption > Decrypt in the Navigation pane.
  2. Paste the encrypted data into the ENCRYPTED DATA field.
  3. Click Save.
  4. Click Show.
  5. The decrypted data displays as a notification.