Data Encryption and Protection - Part 1

A client has recently revisited their need to protect sensitive data in their application and the database that it connects to. As a DBA it is easy to focus solely on the database but as I was considering their infrastructure and all the parts of their system it became clear that that was a small piece of the puzzle. In terms of protecting data for an entire IT solution there are quite a few other areas to consider. In this and a few subsequent posts I will discuss what options there are in securing data. I will attempt to touch on the major advantages and disadvantages of those options. Obviously, as I'm not a network or systems admin I will only touch on the concepts to consider that are beyond the scope of securing the database.

First I will cover what I know best: SQL Server.  There are 2 main encryption methods serving different purposes:

1.  Transparent Database Encryption (TDE) - available for SQL Server 2005+
2. Cell-level encryption

1.    Transparent Database Encryption (TDE)

This is used to encrypt all the data in the database. It is called transparent because any applications or users accessing the database are not affected and need not be modified to handle the data. However, what this means is that the data is presented to the end-user or application with permissions to read it in an unencrypted format. TDE provides protection in the case of loss or theft of a backup or other database files. Any lost files cannot be attached or restored to another instance of SQL Server without the appropriate encryption key. TDE is quick to implement, efficient in terms of database performance, but may not be a sole solution for protecting sensitive data stored in the database.

Although TDE is quick, easy and transparent to implement careful consideration is required prior to making the jump to using it. Rolling back to an unencrypted database state is not straight forward and may require, especially in the case of complex topologies and redundancy strategies, significant downtime - not to mention testing.

2.    Cell-level encryption

With cell level - as in a ‘cell’ in MS Excel spread sheets - the data is encrypted and decrypted “manually”. What that means is any query writing to or reading from an encrypted cell needs to be changed to include the encryption/decryption function. There are two main advantages to this encryption type: 

• The data is only decrypted when it is needed. Consequently standard queries of the database will not expose the sensitive data. 
• Permissions can be assigned in a very granular fashion. This type of encryption adds an additional security layer to database permissions at the encryption key level. So the data can be hidden even from users with permissions to the database and table(s) where encrypted rows exist.      

 There are two main downsides to this type of encryption:

• Existing applications and databases need to be modified to allow for inserting and reading the           encrypted data.
• Cell level encryption is resource intensive.
• With the added power of security stated above comes the cost of security admin

For optimal protection a combination of TDE and cell-level encryption should be employed. For full database level protection of the backups and data files employ TDE. In addition, identify a limited number of crucial data fields that need extra protection and encrypt them at the cell level.

In the next post I’ll delve into the considerations for implementing encryption.

For a complete overview of SQL Server encryption see Books Online.