Planning Encryption
Encrypting columns with plaintext data to columns with encrypted data is an important task that requires planning and collaboration between the database administrator and the security engineer. The database encryption process should be transparent to all the applications that interact with the databases. You should not have to make changes to applications that access your sensitive information. If you are integrating at the application level, then you might have to make some changes to the application. Refer to the following
When planning for database encryption, you must consider:
Type of data to encrypt
How to encrypt
How to manage the encrypted data
Selecting the Data to Encrypt
When selecting the data to be encrypted, you must consider the character type, data type, column properties, the length of the plaintext column, and actual length of the row.
Supported Character Types
CDP for MS SQL Server supports the encryption of:
Single-byte
Multi-byte
Unicode data
Supported Data Types
The following table lists the supported data types for SQL Server that can be migrated. Data types that do not appear in this list cannot be encrypted.
| Data Type | Data Type | Data Type |
|---|---|---|
| BIGINT | BIT | CHAR |
| DATE | DATETIME | DATETIME2 |
| DECIMAL | FLOAT | IMAGE |
| INT | MONEY | NCHAR |
| NTEXT | NUMERIC | NVARCHAR |
| NVARCHAR MAX | SMALLDATETIME | SMALLINT |
| SMALLMONEY | TEXT | TIME |
| TINYINT | VARBINARY MAX | VARCHAR |
| VARCHAR MAX | XML |
Note
In CDP, only large objects up to 500MB are supported. This size may be less depending upon the heap size available on the CipherTrust Manager.
Converted Data Types
After encryption, certain data types get converted into other data types. For example, after encryption, the VARCHAR data of length <=4000 gets converted into VARBINARY(8000). The following tables provide the mapping of plaintext data types and their corresponding encrypted data types:
Non-versioned keys
| Data Type | Length | Encrypted Data Type |
|---|---|---|
| VARCHAR | <=4000 | VARBINARY(MAX) |
| VARCHAR | 4001-8000 | VARBINARY(MAX) |
| NVARCHAR | <=3999 | VARBINARY(8000) |
| NVARCHAR | 4000 | VARBINARY(MAX) |
| CHAR | <=4000 | VARBINARY(8000) |
| CHAR | 4001-8000 | VARBINARY(MAX) |
| NCHAR | <=4000 | VARBINARY(8000) |
Versioned keys
| Data Type | Length | Encrypted Data Type |
|---|---|---|
| CHAR | >= 3985 | VARBINARY(8000) |
| VARCHAR | >=3984 | VARBINARY(8000) |
| NVARCHAR | >=3992 | VARBINARY(8000) |
| NCHAR | >= 3997 | VARBINARY(8000) |
Multibyte Data
| Data Type | Length | Encrypted Data Type |
|---|---|---|
| VARCHAR | >= 3985 | VARBINARY(8000) |
| VARCHAR | 1001-8000 | VARBINARY(MAX) |
| CHAR | <=1000 | VARBINARY(8000) |
| CHAR | 1001-8000 | VARBINARY(MAX) |
Column Encryption Guidelines
Before applying encryption properties on columns, note the following:
During encryption, some columns in a table cannot be encrypted. Below is a list of roles that columns can play and their effect on encryption.
Identity column – Cannot be encrypted.
Primary key – Primary keys are dropped during encryption. You must manually recreate primary keys if you want to preserve the conditions established by the primary keys. If the primary key is not referenced in a foreign key constraint, you should verify that the key is not referenced implicitly as a foreign key before encrypting. During unencryption, the primary keys, which were dropped at the time of encryption, are not created back. You must manually recreate primary keys, if needed.
Primary keys referenced in another table – To encrypt the primary keys referenced in another table, you must first drop the reference. You must manually recreate the primary keys and the reference if you want to preserve the conditions established by the primary keys referred in another table.
Foreign key – To encrypt a foreign key, you must manually drop the constraints prior to data encryption. After encryption, you can re-establish them.
Indexed columns – Index columns can be encrypted, however, the sort order of the encrypted data will not be consistent with the sort order of the plaintext data.
The constraints placed on your columns must be evaluated, as these may affect the data encryption process. The following list provides types of constraints and describes their effect on encryption.
Joins – Make sure that the columns you are encrypting are not part of a join. If you are encrypting a column that is part of a join, you should encrypt both columns.
Unique constraints – When encrypting a column with a unique constraint, that constraint is dropped during the data encryption process. If you want to retain the unique constraint after encryption, you should manually recreate the unique constraint. You cannot use field-level IVs on a column with unique constraints. Instead, you should choose one IV for the entire column.
Check constraint – To encrypt a column with a check constraint, you must drop the check constraint.
Default values – Columns with a default value assigned to them cannot be encrypted. This is because the default constraint adds plaintext data to the column. Applications accessing that data then try to decrypt plaintext data, yielding unexpected results.
NULL values – NULL values are not encrypted by CDP. If a migrated column contains NULL values, those values remain unencrypted in the resulting encrypted column. When a database query yields a NULL value, no cryptographic process is required, so CDP does not interact with the CipherTrust Manager for that query.
Columns referenced in non-CDP triggers on the table – These columns cannot be encrypted. If you attempt to encrypt such columns, the system returns:
"Before executing this operation, you must delete non-CDP trigger (<trigger_name>) defined for this table."
Note
Columns that are currently encrypted cannot be encrypted.
Length of the Plaintext Column
CDP for MS SQL Server uses the VARBINARY data type to store ciphertext for standard encryption.
The following table illustrates how padding affects column length when encryption is performed with AES keys.
| Plaintext Column Length (bytes) | Resulting Ciphertext Column Length (bytes) |
|---|---|
| 15 | 16 |
| 16 | 32 |
| 17 | 32 |
The following table illustrates how padding affects column length when encryption is performed with DES/DESEDE keys.
| Plaintext Column Length (bytes) | Resulting Ciphertext Column Length (bytes) |
|---|---|
| 7 | 8 |
| 8 | 16 |
| 9 | 16 |
Length of the Row
The data encryption process adds columns to the migrated tables. SQL Server limits actual row length to 8060 bytes. If the data encryption process results in a row of size greater than 8060 bytes, SQL Server prevents the encryption.
If your table includes columns of NVARCHAR or VARBINARY data type, the actual row length may differ from the sum of the column definitions, since those data types have variable length. SQL Server’s row limitation applies to the actual data length, not the column definitions.
How to Encrypt Data
When deciding how to encrypt your data, you must consider:
which type of encryption to perform
which algorithm to use
what mode to use
how to apply initialization vectors
whether to use padding
select replacement values
determine how much space the resulting data requires.
Choosing Encryption Type
CDP for MSSQL supports two types of encryption:
Space Requirements Considerations
When allotting space for an encrypted solution, note the following:
The algorithm used to encrypt data
Whether you use field-level or column-level IVs
Whether your table needs an identity column
Managing the Encrypted Data
Prior to encrypting your database, you must consider how you will manage the encrypted data. The sections below discuss ciphertext data types, identity columns, space requirements, and changes to the original column.
Ciphertext Data Types
CDP for MSSQL Server stores ciphertext as VARBINARY data types.
Identity Columns
In SQL Server databases, the ING_ROW_ID column is added to the base table only if such a column does not already exist.
Size of Encrypted Data
Encrypted data is predictably larger than plaintext data; so it is important to ensure that you have enough table space for the operation you are performing (data encryption, unencryption, or key rotation). During the data encryption process, a new column is created. The original column might have a length of 50 bytes; the new column, however, has a different length. The length of the new column depends on the character type of the original data (single-byte, multi-byte, or Unicode), and the algorithm you use to encrypt your data.
For columns encrypted with symmetric keys (AES, DES-EDE, ARIA, and DES), the new column size increases to the next multiple of the block size. Remember that DES and DES-EDE use block sizes of 8 bytes, and AES/ SEED/ARIA uses a block size of 16 bytes. So, if the original column was 50 bytes, then the new column is 56 bytes for DES and DES-EDE; for AES/SEED/ARIA, the new column is 64 bytes.
If the length of the original column is a multiple of the block size, then the new column is the original column size plus the block size. For example, if a column contained a 56 byte string, and that string was encrypted with a DESEDE key, then the ciphertext would be 64 bytes long.
In addition, if you use a random IV for each field, then your table has another column of either 8 bytes (DES and DES-EDE) or 16 bytes (AES).
Consider a scenario where you have a column that is 50 bytes in length and you want to encrypt that column with a DES-EDE key. There are 10,000 rows in the column, and you have decided to use a unique IV for each value.
The original column is set to NULL and a new column is created. The new column is 56 bytes in length. Before encryption, the column required 500,000 bytes. After encryption, the column requires 560,000 bytes. Additionally, a new column is created to hold the IVs. The IVs are 8 bytes each, so an additional 80,000 bytes of space is required for the IVs.
To execute encryption on column, refer to the Encrypt a column article.
