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 keypasswordcertificates. 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 columncell 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).

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 codefunctionalities 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 datarows 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.

Read more from the original source:
Granular or Cell Level Encryption in SQL Server