Encryption is a crucial aspect of data security. It ensures that even if data is lost or stolen, it remains useless to unauthorized individuals. SQL Server provides various encryption options to meet regulatory compliance and corporate data security standards. In addition to Transparent Data Encryption (TDE), which encrypts the entire database, SQL Server also offers granular or cell level encryption.

Granular or Cell Level Encryption vs. Transparent Data Encryption (TDE)

Transparent Data Encryption operates at the entire database level, while granular or cell level encryption applies to specific columns of a table. With TDE, data is encrypted at the I/O level, meaning it remains in clear text format in the buffer pool. On the other hand, granular or cell level encryption allows for more granular control. Data is encrypted using the EncryptByKey function when writing and decrypted using the DecryptByKey function when reading. This ensures that even if a page is loaded into memory, sensitive data remains encrypted.

Unlike TDE, which requires no application code changes, granular or cell level encryption necessitates modifying the data type to VARBINARY and manually using inbuilt functions to encrypt or decrypt the data. Additionally, TDE performs encryption in bulk at the database level, while granular level encryption incurs performance penalties based on the number of columns being encrypted and the amount of data in each column.

Getting Started with Granular or Cell Level Encryption

SQL Server provides several inbuilt functions for encrypting data at the granular or cell level. For example, the ENCRYPTBYKEY function allows encryption using a symmetric key, the ENCRYPTBYASYMKEY function enables encryption with an asymmetric key, the ENCRYPTBYCERT function encrypts data with the public key of a certificate, and the ENCRYPTBYPASSPHRASE function encrypts data with a passphrase using the TRIPLE DES algorithm.

Here are the steps to perform encryption and decryption using a symmetric key:

1. Create a master key: A master key is a symmetric key used to create certificates and asymmetric keys.
2. Create or obtain a certificate protected by the master key: Certificates can be used to create symmetric keys for data encryption.
3. Create a symmetric key and encrypt it using the certificate created in the previous step.
4. Open or decrypt the symmetric key for usage.
5. Use the EncryptByKey function to encrypt the data and the DecryptByKey function to decrypt it. Note that the data type should be VARBINARY.
6. Close the symmetric key when not in use.

Here’s an example of using a symmetric key for encryption and decryption:

USE master
GO

CREATE DATABASE CellLevelEncryptionDemo
GO

USE CellLevelEncryptionDemo
GO

-- Create a database master key (DMK) for the master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStrongPassword@4802';

-- Create a certificate for use as the database encryption key (DEK) protector and is protected by the DMK.
CREATE CERTIFICATE Cert4Cell WITH SUBJECT = 'Certificate for cell level encryption';

-- Create a symmetric key and encrypt it using the above-created certificate
CREATE SYMMETRIC KEY Key4CellEncryption WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Cert4Cell;

-- Open/decrypt the symmetric key for usage
OPEN SYMMETRIC KEY Key4CellEncryption DECRYPTION BY CERTIFICATE Cert4Cell;

-- Create a table with a column of VARBINARY data type
CREATE TABLE dbo.Customer (
  CustomerID INT IDENTITY PRIMARY KEY,
  FirstName VARCHAR(100),
  LastName VARCHAR(100),
  CreditCardNumber VARBINARY(8000) NULL
);

-- Encrypt data using the symmetric key
INSERT INTO dbo.Customer (FirstName, LastName, CreditCardNumber)
VALUES
  ('Steve', 'Savage', EncryptByKey(Key_GUID('Key4CellEncryption'), '1111-1111-1111-1111')),
  ('Ranjit', 'Srivastava', EncryptByKey(Key_GUID('Key4CellEncryption'), '2222-2222-2222-2222')),
  ('Akram', 'Haque', EncryptByKey(Key_GUID('Key4CellEncryption'), '3333-3333-3333-3333'));

-- Query the table to see the encrypted data
SELECT FirstName, LastName, CreditCardNumber
FROM dbo.Customer;

-- Decrypt the encrypted data
SELECT FirstName, LastName, CreditCardNumber, CONVERT(VARCHAR(50), DecryptByKey(CreditCardNumber)) AS DecryptedCreditCardNumber
FROM dbo.Customer;

-- Close the symmetric key
CLOSE SYMMETRIC KEY Key4CellEncryption;
GO

It’s important to note that attempting to encrypt or decrypt data without opening the symmetric key will not fail but will return NULL values instead of the data.

When implementing cell level encryption, it’s crucial to take backups of the keys and certificates to ensure the encrypted database can be restored or attached to another SQL Server instance.

Conclusion

In this article, we explored the differences between Transparent Data Encryption (TDE) and granular or cell level encryption in SQL Server. We discussed the various inbuilt functions available for encryption and decryption and demonstrated an example of using a symmetric key for encryption and decryption. It’s important to evaluate the need for granular or cell level encryption and plan its implementation accordingly, considering the associated overhead and performance penalties.

Resources:

• Securing SQL Server: Encrypt a Column of Data

See all articles by Arshad Ali