Solved

MASTER KEY ENCRYPTION - SQL Server 2008

Posted on 2012-04-12
4
268 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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Max Consumption Rate (MCR) 3 50
Getting max record but maybe not use Group BY 2 27
migrate a SQL 2008 to 2016, 2 26
MS SQL BCP Extra Lines Between Records 2 15
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

785 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