asked on

SQL Server 2008 Column Encryption: How to change encryption key

Having setup column level encryption in a SQL Server 2008 database, a requirement is to change the encryption every three months. Without dropping the encryption by copying the data out of the encrypted column to clear text and then back into a new column, how can this be done?

I know we can add encryption by certificate to a symmetric key and then drop the previous encryption, but this does not change the data in the column (done and compared).

Is there a command or set of commands that enable you to change the encryption and thus the data in the encrypted column (keeping the clear text value the same) without "showing" the data?

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

djrubin

ASKER

A couple of steps but works once you ensure you use N' in the rename. Thanks!

djrubin

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for djrubin's comment #a39566895

for the following reason:

Succinct solution

Anthony Perkins

Either the author selected the wrong comment as an answer or their solution is not complete enough to warrant it to be PAQd

Clemens Hoffmann

I've requested that this question be closed as follows:

Accepted answer: 500 points for aneeshattingal's comment #a39566825

for the following reason:

On behalf of the question author, I'm accepting the comment containing the solution (instead of a zero-point reference to that comment).

Modalot
Community Support Moderator

djrubin

ASKER

I had accepted the answer and graded it A so I'm not sure why zero points were assigned. 500 should be assigned.