Cell-Level Encryption (CLE)

In CLE separate data fields in the same table can be encrypted under different keys. These keys can be protected by different credentials. Unlike TDE protection, the user will need to obtain keys from the KeyControl SQLEKM provider, and must have the correct credential to authorize and load the key(s) for the specific encrypted data they wish to access. Non-encrypted data is not affected by this and is visible to any authorized user.

Note: This first release of KeyControl as an EKM for Microsoft SQL Server does not support key caching and this might impact CLE performance.

Cell-level encryption will only work on data stored in the database as VARBINARY type. You must provide any necessary type conversions so that data is in VARBINARY form before encryption is performed. Decryption will return the data to its original VARBINARY structure. It may then be necessary to reconvert to its original type for viewing in human-readable form.

Note: Database backup files that use the VARBINARY type are not human-readable. Therefore, the previous inspection method, as used for TDE to directly check if data has been encrypted on disk, cannot be used for cell-level encryption.

If you have not already created the following keys and made them available in your current database copy, then create them now.

Symmetric key

Copy 
USE TestDatabase
CREATE SYMMETRIC KEY dbAES256Key
FROM PROVIDER SQLEKM
WITH PROVIDER_KEY_NAME='ekmAES256Key',
IDENTITY_VALUE='Rg7n*9mnf29xl4',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=AES_256;
GO

Asymmetric key

Copy 
USE TestDatabase
CREATE ASYMMETRIC KEY dbRSA2048Key FROM PROVIDER SQLEKM
WITH PROVIDER_KEY_NAME='ekmRSA2048Key',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM=RSA_2048;
GO

Encrypting and decrypting a single cell of data

Before you start, make sure you have a fresh version of the `TestTable` that is unencrypted.

Note: In the example below, the encrypted and decrypted data is stored separately. Normally, the original data would be overwritten with the processed data.

  1. View `TestTable` by running the following query:

    Copy 
    SELECT TOP 10 [FirstName]
    ,[LastName]
    ,CAST(NationalIdNumber AS decimal(16,0)) AS [NationalIDNumber]
    ,(NationalIdNumber) AS VarBinNationalIdNumber
    ,[EncryptedNationalIdNumber]
    ,[DecryptedNationalIdNumber]
    FROM [TestDatabase].[dbo].[TestTable]

    You will see the column NationalIdNumber in its original decimal form, and the column VarBinNationalIdNumber which shows the same number in its VARBINARY form (as stored in the database), and in which it will be encrypted.

    The columns EncryptedNationalIdNumber and DecryptedNationalIdNumber should contain NULL.

  2. To encrypt a single cell in the `TestTable`, run the following query:

    Copy 
    USE TestDatabase
    UPDATE TestTable
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('dbAES256Key'),
    NationalIDNumber)
    WHERE FirstName = 'Kate' AND LastName = 'Austin';
    GO

    This query encrypts the `NationalIdNumber` for Kate Austin using the symmetric key `dbAES256Key`, and stores the result in the column `EncryptedNationalIDNumber`.

    If encrypting using an asymmetric key, run the following query:

    Copy 
    USE TestDatabase
    UPDATE TestTable
    SET EncryptedNationalIDNumber =
    ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'), NationalIDNumber)
    WHERE FirstName = 'Kate' AND LastName = 'Austin';
    GO

  3. Run the previous `View Table` query. The `EncryptedNationalIdNumber` will now contain the encrypted value against the name Kate Austin.

  4. Run the following query to decrypt the information:

    Copy 
    USE TestDatabase
    UPDATE TestTable
    SET DecryptedNationalIDNumber = DecryptByKey(EncryptedNationalIDNumber)
    WHERE FirstName = 'Kate' AND LastName = 'Austin';
    GO

    If decrypting using an asymmetric key, run the following query:

    Copy 
    USE TestDatabase
    UPDATE TestTable
    SET DecryptedNationalIDNumber =
    DECRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'), EncryptedNationalIDNumber)
    WHERE FirstName = 'Kate' AND LastName = 'Austin';
    GO
  5. Run the previous `View Table` query. The `DecryptedNationalIdNumber` will now contain the decrypted value against the name Kate Austin. Ensure that this value matches the corresponding value in the `VarBinNationalIdNumber` column. If the values match, then the decryption worked successfully.
  6. To view the decrypted value in its original decimal form, run the following query:

    7. Copy 
    SELECT TOP 10 [FirstName]
    ,[LastName]
    ,CAST(NationalIdNumber AS decimal(16,0)) AS [NationalIDNumber]
    ,(NationalIdNumber) AS VarBinNationalIdNumber
    ,[EncryptedNationalIdNumber]
    ,CAST(DecryptedNationalIdNumber AS decimal(16,0)) AS
    [DecryptedNationalIdNumber]
    FROM [TestDatabase].[dbo].[TestTable]

  7. Reset the `EncryptedNationalIdNumber` and `DecryptedNationalIdNumber` columns by running the following query:

    8. Copy 
    USE TestDatabase
    UPDATE TestTable
    SET EncryptedNationalIDNumber = NULL, DecryptedNationalIDNumber = NULL;
    GO

Encrypting and decrypting columns of data

Before you start, make sure you have a fresh version of the `TestTable` that is unencrypted.

Note: In the example below, the encrypted and decrypted data is stored separately. Normally, the original data would be overwritten with the processed data.

Perform the same steps as shown in Cell-Level Encryption (CLE) , but in this case where encryption or decryption occurs, replace with the following queries.

  • Encrypt an existing column of data using the symmetric key:

    Copy 
    USE TestDatabase
    UPDATE TestTable
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('dbAES256Key'),
    NationalIDNumber);
    GO

  • Decrypt an existing column of data using the symmetric key:

    Copy 
    USE TestDatabase
    UPDATE TestTable
    SET DecryptedNationalIDNumber = DecryptByKey(EncryptedNationalIDNumber);
    GO

  • Encrypt an existing column of data using the asymmetric key:

    Copy 
    USE TestDatabase
    UPDATE TestTable
    SET EncryptedNationalIDNumber = ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'), NationalIDNumber);
    GO

  • Decrypt an existing column of data using the asymmetric key:

    Copy 
    USE TestDatabase
    UPDATE TestTable
    SET DecryptedNationalIDNumber = DECRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'), EncryptedNationalIDNumber);
    GO

Creating a new table and inserting cells of encrypted data

The following assumes you have available `TestDatabase` and the keys `dbAES256Key`, `dbRSA2048Key` as created previously.

Create a table with an encrypted field:

To create a new database table `Customers`, where individual cells of data held in the third column (`CardNumber`) will be encrypted, execute the following query:

Copy 
USE TestDatabase 
GO
CREATE TABLE Customers (FirstName varchar(MAX), SecondName varchar(MAX), CardNumber varbinary (MAX));
GO

Insert encrypted data with the symmetric key:

The following query allows the user to enter the sensitive data (`CardNumber`) via the keyboard and then immediately encrypt using a symmetric key, sending the `CardNumber` directly into memory (and database) in an encrypted state.

Copy 
USE TestDatabase 
INSERT INTO Customers (FirstName, SecondName, CardNumber) VALUES ('Joe', 'Blogg', ENCRYPTBYKEY(KEY_GUID('dbAES256Key'), CAST('<16 digit card number>' AS VARBINARY))); 
INSERT INTO Customers (FirstName, SecondName, CardNumber) VALUES ('Iain', 'Hood', ENCRYPTBYKEY(KEY_GUID('dbAES256Key'), CAST('<16 digit card number>' AS VARBINARY))); 
INSERT INTO Customers (FirstName, SecondName, CardNumber) VALUES ('Joe', 'Smith', ENCRYPTBYKEY(KEY_GUID('dbAES256Key'), CAST('<16 digit card number>' AS VARBINARY))); 
GO

where <16 digit card number> is a 16-digit payment card number to be encrypted.

View data encrypted with the symmetric key in plain text:

The following query allows the user to view, in plain text on screen, the sensitive data (`CardNumber`) for customers named 'Joe'. The data remains encrypted in memory and (database).

Copy 
USE TestDatabase
SELECT [FirstName], [SecondName],
CAST(DecryptByKey(CardNumber) AS varchar) AS 'Decrypted card number'
FROM Customers WHERE [FirstName] LIKE ('%Joe%');
GO

If an asymmetric key (`dbRSA2048Key`) is used, similar actions can be achieved using the following queries.

Insert encrypted data with the asymmetric key:

Copy 
USE TestDatabase 
INSERT INTO Customers (FirstName, SecondName, CardNumber) 
VALUES ('Joe', 'Connor', ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'),  CAST('<16 digit card number>' AS VARBINARY))); 
INSERT INTO Customers (FirstName, SecondName, CardNumber) 
VALUES ('Richard', 'Taylor', ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'),  CAST('<16 digit card number>' AS VARBINARY))); 
INSERT INTO Customers (FirstName, SecondName, CardNumber) 
VALUES ('Joe', 'Croft', ENCRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'), CAST('<16 digit card number>' AS VARBINARY))); 
GO

where <16 digit card number> is a 16-digit payment card number to be encrypted.

View data encrypted with the asymmetric key in plain text:

Copy 
USE TestDatabase 
SELECT [FirstName], [SecondName], 
CAST(DECRYPTBYASYMKEY(ASYMKEY_ID('dbRSA2048Key'),CardNumber) AS varchar) AS 'Decrypted card number' FROM Customers WHERE [FirstName] LIKE ('%Joe%');
GO

Note: It is possible to encrypt separate table cells using different keys. When decrypting with a particular key, it should not be possible to see data that was encrypted using another key.