I've been researching options for column-level encryption. I understand the fundamentals of how to run the encryption update using EncryptByKey. These are some specific questions that I'd like to address:
1) The need to add a new varbinary column to store the encrypted data - if I'm understand this correctly, you can't run the encryption directly over the column with the sensitive data--a new varbinary column must be created and the encrypted data is stored in that column. Correct? The column I'm encrypting happens to be varchar.
2) After the encryption process, does a query over the table that pulls in just the "sensitive" column still show the unencrypted values? It did in my testing, but I'm not sure if I'm doing something wrong.
3) Does the application that uses the database need to call the encryption stored procedure in order for the column to be encrypted at the the point of inserting or updating?
4) Is it possible to use a trigger to perform the encryption?
I've read the BOL information and have done a little testing by creating the master key, certificate and symmetric keys, then used the EncryptByKey function. But I'm still a little unsure of how to make this work in a real-world scenario.