Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Advice on data encryption Expand / Collapse
Posted Friday, March 12, 2010 12:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 17, 2011 10:05 AM
Points: 85, Visits: 394
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.
Post #882079
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse