Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Lost Certificate and Master Key


Lost Certificate and Master Key

Author
Message
RBarryYoung
RBarryYoung
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10092 Visits: 9517
For those who may not know, the "Mike C" above is actually the amazing Michael Coles an expert in this area (see here: http://www.amazon.com/Expert-SQL-Server-2008-Encryption/dp/1430224649) and a great guy to boot. I know that Michael is a humble person and doesn't normally promote himself, however, I thought it important that the OP know that between Gail, Sarab and now Mike, you're getting some of the best help in existence.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54408 Visits: 44643
RBarryYoung (8/23/2009)
I thought it important that the OP know that between Gail, Sarab and now Mike, you're getting some of the best help in existence.


Well, I know just about enough to spell encryption, so don't count me in with Mike.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Mike C
Mike C
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1485 Visits: 1168
Hi Barry, thanks for the props Smile Gail, don't be so modest Smile

I think the most important thing for the OP to do is to make a backup of the current database before he does anything else to it. If something goes wrong along the way while trying to recover the DMK and certificate he can compound the problem and make his data completely unrecoverable. If he can export the DMK and certificate from an old backup of the database, he should be able to import them back into this one. He might have to use the FORCE option though. I've run into a similar situation myself, where I had to basically "copy" a certificate from one database to another, and the FORCE option was needed. FORCE is a dangerous last-ditch option though, since it can cause data loss in many situations.

I sent the OP a private message and told him to send me an email if he'd like some help going over the specifics of his situation.
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 659
Thanks Mike C for this information but still I am not able to retrieve my data. I was able to take the backup of master key and certificate to the database where these were deleted. But when I try to fetch the data this gives me NULL value for encrypted data.

Following script was used to create master key and certificate:
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Cheeku'

CREATE CERTIFICATE TestCertificate
WITH SUBJECT = 'This is a test certificate',
EXPIRY_DATE = '10/31/2009';



Following script was used to take backup of Master Key and Certificate:
-- New DB is created. Followoing command is issued from the new database testcopy.
backup master key to file = 'C:\Documents and Settings\v-ajohri\Database Backup\mk.dat'
encryption by password = 'Cheeku'

backup certificate TestCErtificate to file = 'C:\Documents and Settings\v-ajohri\Database Backup\cert21.dat'

-- Restoring the certificate to the test database.
RESTORE MASTER KEY FROM FILE = 'C:\Documents and Settings\v-ajohri\Database Backup\mk.dat'
DECRYPTION BY PASSWORD = 'Cheeku'
ENCRYPTION BY PASSWORD = 'Cheeku'

CREATE certificate TestCertificate from file = 'C:\Documents and Settings\v-ajohri\Database Backup\cert21.dat'

-LK
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 659
I also used FORCE while restoring the master key. But in vain. Need help, Mike!!

RESTORE MASTER KEY FROM FILE = 'C:\Documents and Settings\v-ajohri\Database Backup\mk.dat'
DECRYPTION BY PASSWORD = 'Cheeku'
ENCRYPTION BY PASSWORD = 'Cheeku'
FORCE

-LK
Sarab_SQLGeek
Sarab_SQLGeek
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 558
I am just a learner. Not a expert, I was just trying to help him.

Regards,
Sarabpreet Singh Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 659
Its ok Sarab. Thanks for your comments and input as well.

BUT my problem remained unsoved Sad. I think there is some contention because of SERVICE MASTER KEY. M I missing anything. Please go through my previous two posts.

-LK
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 659
Mike, Plz go through my post once or drop me a mail at luckysql.kinda@gmail.com
That would be helpful.

Regards,
LK
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54408 Visits: 44643
Some advice not directly related. Tell your colleague that it's better to own up to a mistake than for it to be discovered and for the boss to have to hunt for the responsible person.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Mike C
Mike C
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1485 Visits: 1168
Hi Lucky,

I just got out of a meeting and saw your messages. I have a question for you -- are you encrypting the data in your database with a symmetric key? And is the symmetric key protected by the certificate you mentioned? If so, depending on how you created the symmetric key we might not have to recover the certificate to recover your data. I sent you an email response, and I need some more details from you to determine your exact status. Before you do anything else to your database though, and I can't stress this enough, get a backup of it immediately!

Also, Gail is 100% correct. If your DBA owns up to the mistake she might find someone else inside the company who can provide immediate hands-on help.

Thanks
Mike C
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search