Granular or Cell Level Encryption in SQL Server

Introduction

Encryption brings data into a state that cannot be interpreted by anyone who does not have access to the decryption key, password, or certificates. Though encryption does not restrict the access to the data, it ensures in case of data loss, then that data is useless for the person that does not have access to the decryption key\password\certificates. When you use encryption, there should be a maintenance strategy for passwords, keys, and certificates.

To meet the demands of regulatory compliance and corporate data security standards, SQL Server allows you to enable encryption at the column\cell level or on the entire database level. You can even use file level encryption, provided by Windows for database files.

In my last article, Transparent Data Encryption (TDE) in SQL Server I talked about enabling encryption at the entire database level and in this article I am going to further discuss and demonstrate a more granular level or each individual cell level encryption in detail and how it differs from Transparent Data Encryption (TDE).

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

Transparent Data Encryption is applicable at the entire database level unlike granular or cell level encryption, which applies to a specific column of a table.

Transparent Data Encryption encrypts data in pages before it is written to the disk and decrypts when reading from disk at the I/O level. This means, data in the buffer pool remains there in clear text format whereas in the case of granular or cell level encryption – you have more granular control – data is encrypted when you use the EncryptByKey inbuilt function while writing and decrypts the data only when you use the DecryptByKey inbuilt function so that even if a page is loaded into memory, sensitive data is not in clear text. This means unlike Transparent Data Encryption in which the data in the buffer pool remains in clear text format, with cell level encryption even in the buffer pool data remains encrypted.

Transparent Data Encryption, as its name implies, is completely transparent to your application. This means literally no application code changes (only an administrative change to enable it for a database) are required and hence there is no impact on the application code\functionalities when enabling TDE on a database being referenced by that application whereas in the case of Granular or Cell level encryption a code change is required. In the case of Granular or Cell level encryption, first you need to change the data type to VARBINARY data type from their original data type (re-cast it back to the appropriate data type when read) and then you need to manually use inbuilt functions to encrypt or decrypt the data.

Transparent Data Encryption performs the encryption in bulk at the entire database level whereas in the case of Granular or Cell-level encryption the performance impact will vary based on the number of columns you are encrypting or the amount of data\rows each column contains, i.e. the more columns you encrypt the more overhead and performance penalties you will have.

Granular level encryption has higher performance penalties and administration costs as the encryption is always salted so the same data will have a different value after encryption. As a result, foreign key constraints and primary key constraints do not provide any benefit on these encrypted columns. Query optimization also gets impacted as indexes on these encrypted columns offer no benefits and as a result range and equality searches turn into full table scans whereas with TDE your query can fully utilize indexes and avoid table scans.

Transparent Data Encryption was introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level whereas Granular or cell-level encryption was introduced in Microsoft SQL Server 2005 and available in later versions for encrypted data at column level.

Getting Started with Granular or Cell Level Encryption

There are several inbuilt functions to encrypt data at granular or cell level. For example, the ENCRYPTBYKEY function allows you encrypting data by using a symmetric key, the ENCRYPTBYASYMKEY function allows encrypting data with an asymmetric key, the ENCRYPTBYCERT function allows encrypting data with the public key of a certificate and the ENCRYPTBYPASSPHRASE allows encrypting data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length. There are equivalent inbuilt functions available for decrypting encrypted data when encrypted using the above mentioned inbuilt functions.

Next I am going to demonstrate how you can use a symmetric key for encrypting and decrypting data. These are the steps you need to perform to do encryption and decryption using a symmetric key. This assumes you have the required permissions for creating a database master key and certificates in the master database and CONTROL permissions on the user database.

  • Create a master key – A master key is a symmetric key that is used to create certificates and asymmetric keys.
  • Create or obtain a certificate protected by the master key – Certificates can be used to create symmetric keys for data encryption.
  • Create a symmetric key and encrypt it by using the above created certificate
  • Open or decrypt the symmetric key for its usage.
  • You can now use the EncryptByKey inbuilt function to encrypt the data and DecryptByKey to decrypt it. Please note, as mentioned you need to have the VARBINARY data type.
  • Close symmetric key when you are not using it.
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';
GO
 
--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';
GO
 
--Create a symmetric key and encrypt it by using above created   certificate
CREATE SYMMETRIC KEY   Key4CellEncryption
     WITH ALGORITHM   = AES_256 --Supported encryption algorithms are AES with 128-bit,   192bit, or 256bit keys or 3 Key Triple DES
     ENCRYPTION BY   CERTIFICATE Cert4Cell;
GO
 
--You need to open\decrypt symmetric key before you it available   for use.
--This applies to the session not to the security context. 
--An open key will continue to be available until it is either   explicitly closed or the session is terminated. 
OPEN SYMMETRIC KEY   Key4CellEncryption
     DECRYPTION BY   CERTIFICATE Cert4Cell;
 
--Create a table with a column with VARBINARY data type
CREATE TABLE dbo.Customer
(
          CustomerID   INT IDENTITY PRIMARY KEY,
          FirstName   VARCHAR(100),
          LastName   VARCHAR(100),
          CreditCardNumber   VARBINARY(8000) NULL
)
GO
 
--To encrypt data using symmetric key use the EncryptByKey   inbuilt function
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'))
GO
--When you query you will see data in is encrypted
SELECT   FirstName, LastName,   CreditCardNumber FROM dbo.Customer
GO

Results Screenshot

--To decrypt the encrypted data you need to use another inbuilt   function called DecryptByKey
SELECT   FirstName, LastName,   CreditCardNumber, CONVERT(VARCHAR(50), DecryptByKey(CreditCardNumber)) DecryptedCreditCardNumber 
FROM   dbo.Customer
GO

Results Screenshot

--Close the symmetric key open in the current session
CLOSE SYMMETRIC KEY   Key4CellEncryption;
GO

When you try encrypting or decrypting data without opening the symmetric key, the command will not fail but also, it will not work; for example the command given below will not fail as symmetric is not open but it will return NULLs instead of data:

SELECT FirstName, LastName, CreditCardNumber, CONVERT(VARCHAR(50), DecryptByKey(CreditCardNumber)) DecryptedCreditCardNumber 
FROM dbo.Customer
GO

Results Screenshot

It’s very important and essential to take a backup of the keys and certificates in order to restore or attach the encrypted database on another SQL Server instance after restoring these keys\certificates there.

Please note, enabling encryption at cell level has overhead and performance penalties (as it is a resource intensive operation and salts the data differently, which causes a table scan) as discussed above and hence it’s recommended to first evaluate the need and then plan for its implementation.

Conclusion

In this article, I discussed how Transparent Data Encryption (TDE) differs from Granular or Cell level encryption and what different inbuilt functions to do encryption\decryption are and then I demonstrated with an example by creating a table, encrypting its data using a symmetric key and decrypting it back while reading.

Resources

Securing SQL Server

Encrypt a Column of Data

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles