SQL Server

Protecting SQL Server Data : CELL-LEVEL ENCRYPTION - Special Considerations

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
8/12/2011 9:27:12 AM

Granularity of Cell-level Encryption

At cell-level, encryption's finest level of granularity, each cell that contains encrypted data is protected by a key that is specific to the user that performed the encryption. Decryption is accomplished through the use of the same key, or a public key, depending on the encryption method applied.

At this level of granularity, the user is presented, in plain text, with only the data elements on which they have been granted decryption permissions, as depicted in Figure 1.

Figure 1. Cell-Level Encryption Granularity.

Alternatively and more commonly, one can apply cell-level encryption at a less granular level, encrypting all cells within a single column with the same key. Permissions to decrypt with this key would then be granted to members of a database role.

The level of granularity that is used is entirely dependent upon the requirements dictated by the data classification process, its surrounding policies and the fine balance between security and performance.

Benefits and Disadvantages of Cell-Level Encryption

Cell-level encryption is a valuable tool in the tool belt of the Database Administrator; but much like the hammer in the carpenter's tool belt, it is not suited to all situations. The following review of cell-level encryption's benefits and disadvantages will provide some aid in the determination of whether this approach is best suited for your situation.

The benefits of cell-level encryption include:

  • Granular – encryption can be provided at a much finer-grained level than the entire database. It offers the means to encrypt a single cell within the table uniquely from another cell.

  • Secure – the element of data that is encrypted remains in that state, even when recalled into memory, until it is actively decrypted.

  • User Specific – users can be granted access to keys that encrypt and decrypt data that is exclusive to their use.

The disadvantages of cell-level encryption include:

  • Data type restrictions – implementation of cell-level encryption requires schema modifications. All encrypted data must be stored with the varbinary data type.

  • Expensive table scans – due to the nature in which the values are encrypted any referential constraints, primary keys and indexes on data that is encrypted is no longer used. This results in table scans when encrypted data is referenced in queries.

  • Processing overhead – the additional processing required for encrypting and decrypting data has a cost to the overall performance of the database.

The use of cell-level encryption does present its own unique set of challenges and costs; but these should not dissuade from the consideration of its use.

The actual performance impact of cell-level encryption will vary depending upon the environment in which the database resides. The size of the impact that cell-level encryption has on the database performance will depend on the server's hardware, such as load balancing, number of processors and amount of available memory, as well as on non-hardware items such as query optimization, index placement and overall database design.

In the HomeLending database, we took advantage of normalization to isolate our sensitive data. It is only accessible, through a view, to a select number of users that perform decryption. Also, it is through a stored procedure, which also is available to a very limited number of users, that the encryption functions are executed. Thus, the performance impact is much less than if it were available to all users of the database, or if the column remained in a high-traffic table. When cell-level encryption is strategically implemented, it is a very effective method of protecting sensitive data at the highest degree of granularity.

Special Considerations

The consideration of the benefits and disadvantages of cell-level encryption is important in the decision to select this method to protect sensitive data. Additionally, there are also some special considerations that are worthy of note:

Searching Encrypted Data

It is not uncommon for an application to allow authorized users to retrieve information regarding an individual, based upon a query that uses sensitive data in its search condition. An example would be a system that recalls a customer by their Social Security Number or Tax Identification Number.

This scenario presents a paradox that confounds protection efforts of sensitive data. On one hand, the business requirements demand the ability to recall individuals through identifiable data. On the other hand, granting the ability to pass plain text into queries presents a very high risk of disclosing sensitive data to unauthorized users who are monitoring database activity.

One approach might be to first decrypt the data that is stored in the database and then compare the plain text to the plain text that is passed in the WHERE clause. However, this approach does not provide a solution to the passing of sensitive data as plain text into the query and it will have a severe impact on the performance of the query, for the following reasons:

  • The query must perform the decryption for all rows in the table, one-by-one, in order to determine a match.

  • Indexes will not be used, so the query execution will result in a table scan.

An alternative approach would be to encrypt the plain text, using the same process that generated the cipher text that is stored in the column, before it is passed into the WHERE clause of the query.

While this may overcome the security issue of passing the plain text value into the query, it would not provide the expected results. This is due to the fact that when cipher text is generated through an encryption method in SQL Server, a salt is appended.

A salt is a series of random characters that are added to the plain text prior to its encryption. In this way, a unique hash is created each time the plain text is encrypted. This provides protection from the cipher text being revealed through the observance of its patterns. As a result, the query will never return a match, even with the underlying plain text being identical.

If efficient searchability, as well as security of the sensitive data, is required then cell-level encryption will not be the solution for the data in question

Encrypting Large Plain Text Values

Documents and manuscripts may contain information that would be classified at a high sensitivity level. The plain text size of these items can often be very lengthy. The challenge that this type of data presents is that all of the cryptographic functions for symmetric and asymmetric keys in SQL Server return a varbinary type with a maximum length of 8,000 bytes. This translates to a maximum plain text length, for encryption, of 7,943 characters.

When the plain text length exceeds this upper limit, the encryption method returns a NULL value, so the encryption not only fails but you also lose the data you were trying to encrypt.

In order to get round this limitation, and use cell-level encryption for large plain text documents, one option is to chop the data into smaller units. For example, the manuscript can be encrypted in segments according to natural breaks that occur in the document. However, this approach would require additional schema modifications since these separate units will require relational storage for efficient retrieval.

Another option is to investigate whether or not the large text really does require encryption because the intent to obfuscate data does not necessarily call for encryption. As an alternative, consider converting the plain text directly into a varbinary data type with a length designation of max, using the CONVERT method. An example of this syntax is:


The max length designation increases the storage limit from a maximum of 8,000 bytes to 2,147,483,647 bytes. Assuming that the plain text length is within this size this statement will return a varbinary version of the plain text that is equal to the full length of the string.

Its resulting value is not discernable to the naked eye. To return this value to plain text requires only the conversion of the varbinary(max) data to a varchar(max) data type using the same CONVERT method. This approach does not provide the same security level as that offered by cell-level encryption, but it does overcome the data type size limitations associated with the cryptographic functions, and it may suffice if a limited degree of obfuscation is required.

Other -----------------
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Harnessing Linked Servers
- Monitoring SQL Server 2005 Performance : Using Windows System Monitor & Using SQL Server Profiler
- Monitoring SQL Server 2005 Performance : Monitoring and Recording Performance
- SQL Server 2008 R2 : Replication - The Publisher, Distributor, and Subscriber Magazine Metaphor
- SQL Server 2008 R2 : Replication - What Is Replication?
- SQL Server 2008 High Availability : Other HA Techniques That Yield Great Results & High Availability from the Windows Server Family Side
- SQL Server 2008 High Availability : Building Solutions with One or More HA Options
- SQL Server 2008 High Availability : The Fundamentals of HA
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 4)
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 3) - Performing a Database Backup
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 2) - Common OS-Related Tasks
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 1) - General Tasks & Scheduling Scripts
- PowerShell in SQL Server 2008
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Using Database Object Schemas
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Protection via Normalization
- Troubleshooting and Optimizing SQL Server 2005 : Server Configuration Maintenance
- Troubleshooting and Optimizing SQL Server 2005 : Tuning the Database Structure
- Troubleshooting and Optimizing SQL Server 2005 : Data Analysis and Problem Diagnosis
- SQL Injection Attacks and Defense : Exploiting the Operating System - Consolidating Access
- SQL Injection Attacks and Defense : Executing Operating System Commands
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS