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 2:56 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
Hello Team,

I encrypted my data with one certificate but unfortunately another dba deleted master key and certificate. I am sure that data can't be retrieved. This is a critical data. I don't want to escalate this issue otherwise that dba would lose her job. Can anyone tell me how to decrypt the data back.

I still have that script that I used to create master key and certifacate. I created the master key and certificate again using those scripts. But in vain. Any thoughts?

-LK
Post #775644
Posted Sunday, August 23, 2009 2:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
As far as I'm aware, without the certificate the data cannot be decrypted at all.

Do you have an older backup that still has the certificate and master key in? If so, restore it elsewhere and you can get at the data, at least. Not sure if you'll be able to copy the certificate and key over.



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 #775645
Posted Sunday, August 23, 2009 3:29 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
Yes the older one I have but that is really very old. I can make a new database with that. Now the question comes of copying the certificate and master key... if possible..

Anyway thanks for your post

-LK
Post #775646
Posted Sunday, August 23, 2009 4:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
luckysql.kinda (8/23/2009)
Yes the older one I have but that is really very old.


If this is critical data, how come you only have one very old backup? Isn't this getting backed up daily? Or did no one notice the certificate deletion for long enough for the older backups to be deleted?

I've asked someone who knows encryption better to help, he might not get to it today though.



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 #775648
Posted Sunday, August 23, 2009 5:22 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 for a quick response Gila. No one noticed the certificate deletion for long enough till I tried to execute the select query. I shall visit the forum again tomorrow to see some help.

-LK
Post #775650
Posted Sunday, August 23, 2009 5:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 1:00 PM
Points: 368, Visits: 544
You can take the backup of
Master key as well as the Certificate with the below mentioned scripts:
From the Instance which you've restored from your backup

backup master key to file = 'c:\mk.dat'
encryption by password = 'Pass@12'

backup certificate mycert to file = 'c:\cert21.dat'
with private key(
encryption by password = 'sarab',
file = 'c:\cert21_key.dat')

To Restore the same on your test server Use these Scripts

restore master key from file = 'Z:\mk.dat'
decryption by password = 'Pass@12'
encryption by password = 'Pass@12'

create certificate mycert from file = 'Z:\cert21.dat'
with private key(file = 'Z:\cert21_key.dat',
decryption by password = 'sarab')

NOTE: Use Encryption by option while creating\Restoring Certificate\Master Key only if you specified the password while creating Master Key or Certificate on your server & use the same password in the ENCRYPTION BY option.

Test this if u face any problem, let me know.


Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #775651
Posted Sunday, August 23, 2009 5:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 1:00 PM
Points: 368, Visits: 544
If you're testing this on the same server then its Ok
otherwise you need to backup and restore the Service Master key also.


Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #775653
Posted Sunday, August 23, 2009 10:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Before you do anything further with this database, immediately take a backup and store it somewhere.

Next restore the backup database to a different database and immediately try to backup the DMK and certificate in the database to files. Then go to your current database and try restoring the DMK and certificate from the file (in that order). You may have to use the FORCE option, but try it without the FORCE option first. I've used this method to "copy" a DMK and certificates over to new databases before.

For future reference, your DBA needs to immediately back up all certificates and encryption keys on the server/in the database immediately after creating them, and store them in a secure (preferably off-site) location.
Post #775699
Posted Sunday, August 23, 2009 10:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 1:00 PM
Points: 368, Visits: 544
Mike C (8/23/2009)
Before you do anything further with this database, immediately take a backup and store it somewhere.

Next restore the backup database to a different database and immediately try to backup the DMK and certificate in the database to files. Then go to your current database and try restoring the DMK and certificate from the file (in that order). You may have to use the FORCE option, but try it without the FORCE option first. I've used this method to "copy" a DMK and certificates over to new databases before.

For future reference, your DBA needs to immediately back up all certificates and encryption keys on the server/in the database immediately after creating them, and store them in a secure (preferably off-site) location.


They don't have any Masker key and certificate available now.

Read this
I encrypted my data with one certificate but unfortunately another dba deleted master key and certificate.


Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #775701
Posted Sunday, August 23, 2009 10:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Sarab (8/23/2009)


They don't have any Masker key and certificate available now.


The question was asked if they had a backup of the database with DMK and certificate. So the OP actually does not have a backup of the database then?
Post #775703
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse