Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column Encryption in SQL Server 2005 Expand / Collapse
Author
Message
Posted Wednesday, August 13, 2008 1:42 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
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?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #552238
Posted Friday, August 22, 2008 3:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 1,154, Visits: 866
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.
Post #557148
Posted Friday, August 22, 2008 3:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
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?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #557169
Posted Tuesday, September 2, 2008 1:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:17 PM
Points: 33, Visits: 221
Here is some more reference items from msdn for sql server 2005 encryption:

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

Post #562644
Posted Tuesday, September 2, 2008 2:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 2008, MVP
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

Post #562654
Posted Tuesday, September 2, 2008 2:17 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:44 PM
Points: 684, Visits: 619
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.
Post #562665
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse