Encryption and Keys

This section provides more detail regarding encryption and key management, together with examples showing how to encrypt and decrypt data.

When you have configured the KeyControl SQLEKM provider, and you have a suitable credential associated with your login, you can use the provider to:

  • Manage keys.

  • Encrypt or decrypt entire databases, or fields within tables, within SQL Server using TDE or cell-level encryption, or both at the same time.

Keys can be created in the KeyControl and referenced by the appropriate database as required for use. When a reference of a key is no longer required for active use in the database, it should be deleted from the database while retaining the original copy of the key in the provider, which also acts as a secure backup. Storing original copies of keys in the KeyControl is more secure than leaving key references and associated data together in the database. As long as the key is not deleted from the KeyControl, its reference can be restored when next required for active use in the database.

Note: Copying and deletion of keys does not apply to a TDE Database Encryption Key (TDEDEK), which is created as an integral part of a user database. On the other hand, this can apply to the wrapping key (TDEKEK) which is used to protect the TDEDEK. See Encryption and Keys .

It is recommended to regularly re-encrypt your data using fresh keys so that any persistent attempts to decipher or compromise your encrypted data are impeded.

Examples

To run the examples included in this section, open SQL Server Management Studio and connect to a SQL Server instance, then open a query window to execute a query.

In the example T-SQL statements, the names used for keys (such as `dbAES256Key`) and databases (such as `TestDatabase`) are example names only.

The exception is `master` database, which is a real database.

Note: You must have a SQL Server login and appropriate permissions to configure or access Microsoft SQL Server or the KeyControl SQLEKM provider.

Key naming, tracking and other identity issues

Keys held in the database are really references to actual keys held in the KeyControl. For the purpose of key tracking, it is suggested that you use the same name for both the database and the provider version of a key. Use a suffix or prefix to distinguish between the database and provider versions.

In a database there can be only one key with a specific name at any one time.

If you have a significant number of keys, you may wish to implement a key naming convention that helps you track which keys encrypt which data, backed up with some form of secure documentation.

To use the examples in this document you will first need to create `TestDatabase` and `TestTable` as shown in Creating a database and Creating a table Otherwise, provide your own database and table to perform encryption operations and adapt the examples accordingly. Refer to Encryption and Keys before adapting any examples.

Note: Keys created under a login that is mapped to a particular credential will be protected by that credential.

Supported cryptographic algorithms

The KeyControl SQLEKM provider supports the following algorithms: `AES_256`, `RSA_ 2048`, `RSA_3072` and `RSA_4096`.