Steve Jones - SSC Editor (2/10/2012)
Richard Sisk (2/10/2012)
It works quite well, we have key change procedures that are used to regularly update the keys. If a backup is stolen, it's no good unless they also know to steal the key backups which are stored protected on another device. It has passed several PA-DSS audits.
How often do you change keys and how big a deal is it? Performance issues? Resources in use? blocking?
This is an excellent question, because if the data set if very large it may not be practical to re-encypt data en-mass when new keys are generated, Having to pull data outside of SQL server and re-encrypt it and write it back is a slow way to do it unless you have a change key mechanism that does it slowly over time.
I prefer to use a hybrid system where I have CLR version of the encrypt and decrypt methods on the server so I can process data in sets very fast. Only admin has execute on theses methods and they keys come from a separate location.
The probability of survival is inversely proportional to the angle of arrival.