Column Encryption in SQL Server 2005

Question

Column Encryption in SQL Server 2005

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
August 13, 2008 at 1:42 pm

#189326

I'd like to know what is possible with column encryption in SQL Server 2005.
Do people have any experiences with this topic that they would like to share?
Any performance-related issues with column encryption?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

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

abmore Hall of Fame Points: 3628 More actions · Answer 1

yes we can implement column level encryption using the ASYMMETRIC KEY and SYMMETRIC KEY to encrypt & decrypt the column data.

check msdn for brief help on ASYMMETRIC KEY and SYMMETRIC KEY.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Abhijit (8/22/2008)
yes we can implement column level encryption using the ASYMMETRIC KEY and SYMMETRIC KEY to encrypt & decrypt the column data.
check msdn for brief help on ASYMMETRIC KEY and SYMMETRIC KEY.

Thank you, I also found a couple of articles here on this site.

Any stories to share on the performance impact of column encryption? Any caveats to watch out for?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

KDASQL Ten Centuries Points: 1103 More actions · Answer 3

Here is some more reference items from msdn for sql server 2005 encryption:

http://msdn.microsoft.com/en-us/library/ms189586.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Main perf problem is that indexes on an encrypted column are useless. The encryption routines are no deterministic and so you can't encrypt a value and compare with the encrypted column. You have to decrypt the column and compare with the value. That means no index seeks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan. T. Hall of Fame Points: 3221 More actions · Answer 5

One issue I have run into is the restoration of the encryption key if it is lost from the server. If you have a copy of the master it is possible to restore it and have access to you encrypted data but BOL make it sound easy but there are many steps that aren't in one place. I would practice a few times as it is easy to over write to information and render it useless.

If you find a better guide than BOL please post it as my notes have worked but are not complete.