Skip to main content

DSP® Column-Level Encryption Instruction Guide

Jenny Drummey
  • Updated

Overview

Column-level encryption is used to protect sensitive information that is stored in the underlying database. Without this feature, such information is stored in plain text and is readable by any user with access to the underlying database and potentially by unauthorized users if login security is breached.

This article outlines how to implement column-level encryption in the DSP® (version 6.0 and higher) using application-level encryption and decryption.

Terminology 

The following terms are used in this article:

  • 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.

Technical Notes

Become familiar with the following technical notes before implementing column-level encryption:

  • All encryption and decryption is performed at the application level.
  • Triple DES or AES-256 is used.
  • All methods are called through system-level FIPS 197 compliant libraries.
  • Encryption Keys are stored in the database. Each instance of DSP® 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 config.xml. 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 all machines that have DSP® server software running. In general, this is just the web server. For example:

    <add key="EncryptionAlgorithm" value="AES256" />

  <add key="EncryptionKey" value="e02337ddc69f762d2881602d4b4bcbf9cbd144a2" />

  • Users only need rights to view the page in order to view the encrypted data.
  • Encryption is available to developers through the plugin API with encrypt and decrypt/pass. To use the API, the KeyID must be known (located in the Key table, GUID).

Encryption Security Considerations

Once a value is encrypted, the encrypted value will only display as the encryption string in the DSP and in the underlying database table. To ensure that unauthorized users do not have the ability to view the unencrypted value, administrators must implement Page security and secure the encryption keys. Refer to the [DSP / SysAdmin] How to Configure WebApp Page Security article in the Knowledge Base for more information.

Decrypted values can only be viewed in the DSP if the user has access to the Encryption Keys and Decrypt pages in System Administration.

To view an encrypted value in System Administration:

  1. Click Configuration > Encryption > Decrypt in the Navigation
  2. Click Add.
  3. Paste the encryption string into the ENCRYPTED DATA text box.
  4. Click the Show button.

    Decrypt.png

If users do not have permission to access the Encryption Keys and Decrypt pages in System Administration, then they cannot view plain text values that have been encrypted in the DSP.

Limitations

The following are limitations to the column-level encryption feature:

  • 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.
  • Data entered directly into the database outside of the DSP®-specific mechanism will be seen as plain text because this is application-level encryption and decryption.
  • Database-level mechanisms cannot be used to read or write encrypted data. These operations must be performed by plugins or through the DSP® 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.

Implementation

To encrypt a column:

  1. Grant Permissions.
  2. Create an Encryption Key.
  3. Encrypt a Column.

Grant Permissions

Grant permissions to the Power User and Power Designer security groups for the System Administration WebApp. Refer to the DSP® Security documentation in the online help for details on granting permissions to security groups.

Create an Encryption Key

Each instance of DSP® is delivered with a System Administration Passwords key. Additional encryption keys can be created.

Encryption keys are stored encrypted in the database. The key to decrypt the encryption key is stored in the file system on the web server.

To create an encryption key:

  1. Log in to the DSP®.
  2. Select Admin > Configuration > Encryption > Encryption Keys in the Navigation pane.
  3. Click Add on Page Toolbar.

Encryption_Keys_page.png

Encryption Keys page

4. Enter an encryption key name in Display Name

5. Click Save.

Encrypt a Column

To encrypt a column:

  1. Select Admin > Data Sources in the Navigation pane. 
  2. Locate the Data Source Name.
  3. Click the Encryption icon; all enabled column encryptions display.
  4. Click Add on the Page Toolbar.

Data_Source_Column_Encryption_page.png

Data Source Column Encryption page

5. Select a table from TABLE NAME list box.

6. Select a column from COLUMN NAME list box.

7. Select an encryption key from KEY ID list box.

8. Click Save.

9. Click Enable.

NOTE: To decrypt a column, click Disable.

Related articles