Solved

MASTER KEY ENCRYPTION - SQL Server 2008

Posted on 2012-04-12
4
266 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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Closing Comment

by:glo-dba
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now