|
|
|
SSC Veteran
 
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 11:41 PM
Points: 247,
Visits: 379
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 17,117,
Visits: 12,216
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 11:41 PM
Points: 247,
Visits: 379
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 17,117,
Visits: 12,216
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 11:41 PM
Points: 247,
Visits: 379
|
|
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
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 254,
Visits: 265
|
|
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 Anand 
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 254,
Visits: 265
|
|
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 Anand
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 4:35 PM
Points: 1,133,
Visits: 703
|
|
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.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 254,
Visits: 265
|
|
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 Anand
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 4:35 PM
Points: 1,133,
Visits: 703
|
|
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?
|
|
|
|
