When to do column only encryption?

Question

When to do column only encryption?

repent_kog_is_near

SSCertifiable

Points: 7278
More actions
March 31, 2011 at 5:12 pm

#236181

since TDE does whole DB encryption, and with less overhead, what may be situation where column only encryption would be a better choice to use with SQL 2008? or is it a feature really ready to become obsolete?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

repent_kog_is_near (3/31/2011)
since TDE does whole DB encryption, and with less overhead, what may be situation where column only encryption would be a better choice to use with SQL 2008? or is it a feature really ready to become obsolete?

Less overhead? Where did you get that?

There are significant disadvantages to TDE:

Reqires significant CPU power to encrypt/decrypt the data on the fly.

Requires Enterprise Edition

Prevents effective database backup compression.

TDE protects the data "on disk", but does not protect against ordinary users with data reader access to the database from being able to see the encrypted data.

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 2

>>Less overhead? Where did you get that?

quoted from http://msdn.microsoft.com/en-us/library/cc278098%28v=sql.100%29.aspx

"

performance for a very basic query (that selects and decrypts a single encrypted column) when using cell-level encryption tends to be around 20% worse. This inversely scales with workload size resulting in performance degradations that are several magnitudes worse when attempting to encrypt an entire database. One sample application with 10,000 rows was four times worse with one column encrypted, and 20 times worse with nine columns encrypted. Because cell-level encryption is custom to each application, performance degradation will vary depending on application and workload specifics. As noted in Impact on the Database, this compares to 3-5% for TDE on average and 28% in the worst case (assuming the encryption scan is not running)."

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 3

>>Reqires significant CPU power to encrypt/decrypt the data on the fly.

This is true for column-level encryption, too?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Usually, you only encrypt one or two columns in a database, like a credit card number, and when you encrypt column level data, you only encrypt it when you add new rows or change data in that column. When you query the data you only need to decrypt the encrypted data if the cleartext of that particular column is needed in a particular query.

With TDE, everything in the database is encrypted, so the overhead is present in every operation.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

I've also seen folks that do it just the opposite. Encrypt the item you're trying to find and look that up in the encrypted column. Makes for a nice SARG-able search.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sturner SSC-Insane Points: 22411 More actions · Answer 6

Jeff Moden (4/1/2011)
I've also seen folks that do it just the opposite. Encrypt the item you're trying to find and look that up in the encrypted column. Makes for a nice SARG-able search.

This doesn't work due to the randomization of good encryption algorithms. If your algorithm produces the same results every time it is less secure than one that doesn't. What people do is put an index on the hash of the (unencrypted) date and do the lookup that way.

The probability of survival is inversely proportional to the angle of arrival.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 7

sturner (4/6/2011)
Jeff Moden (4/1/2011)
I've also seen folks that do it just the opposite. Encrypt the item you're trying to find and look that up in the encrypted column. Makes for a nice SARG-able search.
This doesn't work due to the randomization of good encryption algorithms. If your algorithm produces the same results every time it is less secure than one that doesn't. What people do is put an index on the hash of the (unencrypted) date and do the lookup that way.

And if this does work, you want to re-examine your encryption algorithms.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

sturner (4/6/2011)
Jeff Moden (4/1/2011)
I've also seen folks that do it just the opposite. Encrypt the item you're trying to find and look that up in the encrypted column. Makes for a nice SARG-able search.
This doesn't work due to the randomization of good encryption algorithms. If your algorithm produces the same results every time it is less secure than one that doesn't. What people do is put an index on the hash of the (unencrypted) date and do the lookup that way.

The indexed hash partially defeats the security of randomization of the encryption algorithm, since the hash will be the same for every occurrence of the same cleartext value.

For example, a hash of SSN could be used to defeat the encryption of the SSN, because it's easy to create a table of all one billion possible SSNs and their matching hash if the hash algorithm is known or can be guessed.

For a value with a larger domain, like credit card numbers, that method is less effective, since you would have to create 10 to the 16th power hashes to cover all possible credit card numbers. However, it still reduces the security of the data.