Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MASTER KEY ENCRYPTION - SQL Server 2008

Posted on 2012-04-12
4
Medium Priority
?
275 Views
Last Modified: 2012-04-13
Dear Experts,

I have a problem. We had a database promoted from dev to test via redirected restore.

The database has an encrypted column. As you can imagine, the key no longer works.

Is there a way to drop the key and recreate a new one?

Or

Do I have to drop the key, create a new key, then update the column with the values using the encrypt function?

Thanks!
Keith
0
Comment
Question by:glo-dba
  • 3
4 Comments
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 37841130
What you need to do is to open the Master Key with the password it was created with on the other system and then close the key again.  This will allow the new server to encrypt the master key with the Service Master Key and you will be good to go.  You should not drop the key.
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 2000 total points
ID: 37841138
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password that was used to create the key'

You would do this while in the database that has the master key.
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 37841142
0
 

Author Closing Comment

by:glo-dba
ID: 37842128
Thanks I'll try that next time.. I was out of time and so I did drop and create a new key. I was lucky enough to have the column stored elsewhere and a column to join on so nothing was lost. Thanks for your response,

Keith
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question