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
luckysql.kinda
luckysql.kinda
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57609 Visits: 44707
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, 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


luckysql.kinda
luckysql.kinda
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57609 Visits: 44707
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, 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


luckysql.kinda
luckysql.kinda
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 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
Sarab_SQLGeek
Sarab_SQLGeek
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 558
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 Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Sarab_SQLGeek
Sarab_SQLGeek
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 558
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 Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Mike C
Mike C
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1541 Visits: 1168
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.
Sarab_SQLGeek
Sarab_SQLGeek
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 558
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 Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Mike C
Mike C
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1541 Visits: 1168
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?
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