SQL Server Cell Level Encryption.. It might not be as hard as you might think.

Thursday, March 15, 2012

SQL Server Cell Level Encryption.. It might not be as hard as you might think.

IT Pro Connection - “The SQL Guy” Post #20: Using Cell Level Encryption in SQL Server

"Last week you learned how to setup the encryption key hierarchy in SQL Server. Now, let’s use encryption to encrypt sensitive data in SQL Server.

It is quite possible that you might have sensitive data that needs encryption at a finer level of detail than the entire database. Most of the row might need to be visible to users, while certain sensitive information such as employee salary might require encryption. You might also want the ability for certain users to be able to encrypt/decrypt certain cells as shown in figure 1.

The solution is to use cell-level encryption in SQL Server.

With cell-level encryption in SQL Server, it is possible to encrypt data in individual cells within a table.

BENEFITS OF USING CELL-LEVEL ENCRYPTION:

(1) Granular, user specific control on encrypting individual cells or column values rather than entire databases (compared to using Transparent Data Encryption – TDE).

(2) Data retains its encrypted state in memory unless it is actively decrypted.

DRAWBACKS OF USING CELL-LEVEL ENCRYPTION:

(1) Requires application changes and analysis of tables to locate sensitive data that needs to be encrypted.

(2) Encryption of data introduces randomization. This makes it impossible to index data and causes a performance impact since indexes on encrypted columns cannot be used while searching for a value.

(3) Cell-level encryption built-in functions only return varbinary type data and the output is limited to up to 8000 bytes.

..."

If you're saying to yourself, "Gee, I wonder if we should encrypt this..." or "Boy, it would suck if someone unauthorized got this data..." or "Heck, no I wouldn't put my personal data in this, even for testing..." or "Encryption is for sissies" or "But we've got lasers protecting our data center!" then you might be in need of this article...