Lost Certificate and Master Key

  • 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

  • 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
  • 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

  • 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
  • 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

  • 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 😎
    Sarabpreet.com
    SQLChamp.com
    Twitter: @Sarab_SQLGeek

  • 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 😎
    Sarabpreet.com
    SQLChamp.com
    Twitter: @Sarab_SQLGeek

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

  • 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 😎
    Sarabpreet.com
    SQLChamp.com
    Twitter: @Sarab_SQLGeek

  • 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?

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

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • 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, 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
  • 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.

  • 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

  • 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

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply