Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Lost Certificate and Master Key Expand / Collapse
Author
Message
Posted Sunday, August 23, 2009 12:29 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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
Posted Sunday, August 23, 2009 1:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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
Posted Sunday, August 23, 2009 2:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134
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
Posted Monday, August 24, 2009 4:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, 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
Post #775874
Posted Monday, August 24, 2009 4:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, 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
Post #775878
Posted Monday, August 24, 2009 5:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
I am just a learner. Not a expert, I was just trying to help him.

Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #775906
Posted Monday, August 24, 2009 6:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
Its ok Sarab. Thanks for your comments and input as well.

BUT my problem remained unsoved . 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
Posted Monday, August 24, 2009 8:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, 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
Post #776040
Posted Monday, August 24, 2009 8:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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
Posted Monday, August 24, 2009 9:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134
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 »

Add to briefcase ««1234»»»

Permissions Expand / Collapse