Lost Certificate and Master Key

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Lost Certificate and Master Key

33 posts, Page 2 of 4

««1 2 3 4 »»»

Lost Certificate and Master Key

Rate Topic

Display Mode

Topic Options

Author

Message

RBarryYoung

Posted Sunday, August 23, 2009 12:29 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

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."

Post #775709

GilaMonster

Posted Sunday, August 23, 2009 1:23 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 38,065, Visits: 30,360

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 2008, MVP
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

Post #775715

Mike C

Posted Sunday, August 23, 2009 2:31 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276, Visits: 1,112

Hi Barry, thanks for the props :) Gail, don't be so modest :)

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.

Post #775721

luckysql.kinda

Posted Monday, August 24, 2009 4:37 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649

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

Post #775874

luckysql.kinda

Posted Monday, August 24, 2009 4:41 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649

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

Post #775878

Sarab_SQLGeek

Posted Monday, August 24, 2009 5:33 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:10 AM
Points: 368, Visits: 524

I am just a learner. Not a expert, I was just trying to help him.

Regards,
Sarabpreet Singh Cool

SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek

Post #775906

luckysql.kinda

Posted Monday, August 24, 2009 6:49 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649

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

Post #775946

luckysql.kinda

Posted Monday, August 24, 2009 8:34 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310, Visits: 649

Mike, Plz go through my post once or drop me a mail at luckysql.kinda@gmail.com
That would be helpful.

Regards,
LK

Post #776040

GilaMonster

Posted Monday, August 24, 2009 8:45 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 38,065, Visits: 30,360

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 2008, MVP
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

Post #776058

Mike C

Posted Monday, August 24, 2009 9:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276, Visits: 1,112

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

Post #776116

« Prev Topic | Next Topic »

33 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions