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?
djrubinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Add a column, update this column with the values from your Encrypted column, decrypt the new column -> encrypt the new column with the new key -> drop the original column, rename the new column
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
djrubinAuthor Commented:
A couple of steps but works once you ensure you use N' in the rename. Thanks!
0
djrubinAuthor Commented:
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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Anthony PerkinsCommented:
Either the author selected the wrong comment as an answer or their solution is not complete enough to warrant it to be PAQd
0
ModalotEE ModeratorCommented:
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
0
djrubinAuthor Commented:
I had accepted the answer and graded it A so I'm not sure why zero points were assigned. 500 should be assigned.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.