Solved

MASTER KEY ENCRYPTION - SQL Server 2008

Posted on 2012-04-12
4
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 24

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 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 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 24

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

738 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