SQLServerCentral / Administering / SQL Server 2005 / Lost Certificate and Master Key / Latest Posts

RE: Lost Certificate and Master Key

Mike C — Wed, 09 Sep 2009 14:56:07 GMT

[quote][b]ROHIT KAPOOR (9/9/2009)[/b][hr]Thanks yes i tried that as well but no luck ...[/quote]It's tempting to say "sorry it didn't work out, good luck", but if you'd be willing to post the exact code you ran that failed I would be willing to attempt to reproduce your issue on my servers. I've already run my own tests locally and was able to encrypt data on one server and decrypt it by re-creating the exact same symmetric key (on two other servers). At this point since we seem to have conflicting results, the next step would be to review the process you used to get to your results.ThanksMichael

RE: Lost Certificate and Master Key

ROHIT KAPOOR — Wed, 09 Sep 2009 09:37:39 GMT

Thanks yes i tried that as well but no luck ...

RE: Lost Certificate and Master Key

Mike C — Fri, 04 Sep 2009 12:32:02 GMT

Hi ROHIT,One thing you need to do is specify the IDENTITY_VALUE and KEY_SOURCE options in your CREATE SYMMETRIC KEY statement. By not specifying these SQL Server generated a random GUID and used random key material to generate your symmetric key. You can't export or backup a symmetric key, so the only way to ensure you generate the exact same symmetric key in two different databases is to specify these two options, and then run the exact same CREATE SYMMETRIC KEY statement with the same IDENTITY_VALUE and KEY_SOURCE options/values.ThanksMike C

RE: Lost Certificate and Master Key

luckysql.kinda — Fri, 04 Sep 2009 09:02:39 GMT

Rohit,As you are moving to different server so take a backup of master key and restore this on new server. This should resove the issue. Do inform me if this doesn't work.RESTORE MASTER KEY FROM FILE = 'c:\Database Backup\mk.dat'DECRYPTION BY PASSWORD = 'yourpassword'ENCRYPTION BY PASSWORD = 'yourpassword';-LK

RE: Lost Certificate and Master Key

ROHIT KAPOOR — Fri, 04 Sep 2009 06:13:05 GMT

Thanks Mike I have one production server (SQ01 ) which has one DB (DW01) where application is using Certificate .intialy i have created master key USE DW01GOCREATE MASTER KEY ENCRYPTION BY PASSWORD='pass123' and then created certificate using CREATE CERTIFICATE test WITH SUBJECT 'test' , START_DATE ='01/01/2009'and then created symmeter key using CREATE SYMMETRIC KEY testkey WITH ALGORITHM =TRIPLE_DESENCRYPTION BY CERTIFICATE testi am backing the certificate with following sql BACKUP SERVICE MASTER KEY TO FILE = 'C:\DWH_ServiceMasterKey.smk' ENCRYPTION BY PASSWORD = 'pass123 GOBACKUP MASTER KEY TO FILE = 'C:\DWH_MasterKey.msk' ENCRYPTION BY PASSWORD = 'pass123'GOBACKUP CERTIFICATE P14N TO FILE = '' -- Location to backup, for example: C:\DWH_P14NCertificate.cerWITH PRIVATE KEY(FILE = 'C:\DWH_P14NCertificate_PrivateKey.pky' ,ENCRYPTION BY PASSWORD = 'pass123');GOi have backed my DDW01 database Now i want to restore the above database on different server (SQL02) as DW02 datbase so 1. I have restored DW01 file backup on SQL02 server as DW02 database 2. then i used following sql to restore certificate as it is different server after moving all certificate and private key files in c:\ on SQL02DROP SYMMETRIC KEY testKeyGODROP CERTIFICATE testGO - Create certificate from backed up certificate fileCREATE CERTIFICATE P14N FROM FILE = 'C:\DWH_P14NCertificate.cer'WITH PRIVATE KEY (FILE = 'C:\DWH_P14NCertificate_PrivateKey.pky', DECRYPTION BY PASSWORD = 'pass123'); GO -- Create symmetric key based on restored certificateCREATE SYMMETRIC KEY testkey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE testGOBut when i am trying to decrypting the data i am unable to decrypt the data on SQL02/DW02 using the same script which is runnng on SQL01/DW01So i was am not sure where i am going wrong .can you please help me .Thanks Rohit

RE: Lost Certificate and Master Key

Mike C — Thu, 03 Sep 2009 12:38:58 GMT

[quote][b]ROHIT KAPOOR (9/3/2009)[/b][hr]HI I am having same problem can i have script for this .thanksRohit MCP[/quote]Hi ROHIT,There's no one-size-fits-all script for this. The basic steps are:1. Back up your existing database immediately! I can't stress this enough. If something goes wrong with the following steps for some reason you can permanently lose access to all your data if you don't have a recent backup! If you have a current backup you can always get back to your current state, at the very least.2. Restore an older backup copy of your database to the server, under a new name.3. Backup/export the certificates (with private keys) and/or master keys (depending on which ones you're having problems with) from the restored older copy of the database to files in the file system.4. Finally import these certificates and/or master keys from the files in the file system back into the database you're having problems with. For best results you might try eliminating the database master key/encryption key hierarchy from the equation: try protecting the certificate with a password when you import it. That will tell you whether or not there's an issue with the master keys.If you can give specifics about your problem, we'd be happy to help.ThanksMike C

RE: Lost Certificate and Master Key

ROHIT KAPOOR — Thu, 03 Sep 2009 11:52:54 GMT

HI I am having same problem can i have script for this .thanksRohit MCP

RE: Lost Certificate and Master Key

luckysql.kinda — Tue, 25 Aug 2009 09:47:42 GMT

You have really been a great help Mike. Thanks for a great help once again.-LK

RE: Lost Certificate and Master Key

Mike C — Tue, 25 Aug 2009 09:37:32 GMT

Lucky,I sent you an email with a modified sample script. Based on what you sent me, you need to modify your BACKUP CERTIFICATE statement slightly to backup the certificate private key (used for decryption) as well as the public key (used for encryption). Right now your statement doesn't appear to be backing up the certificate's private key, which is probably why you can't decrypt your data when you restore it. I also modified the script slightly to use a variation of the DecryptByCert function that accepts the third "password" parameter, which will bypass the database master key and the service master key completely. That will take any possible interference from your master keys out of the equation completely and we can determine if the restored certificate itself works or not.ThanksMike C

RE: Lost Certificate and Master Key

Mike C — Tue, 25 Aug 2009 08:32:25 GMT

[quote][b]luckysql.kinda (8/25/2009)[/b][hr]Hello Mike,Thanks for your help. When I use following command as per Sarab's response, I get following information:RESTORE SERVICE MASTER KEY FROM FILE = 'D:\Database Backup\smk.dat'DECRYPTION BY PASSWORD = '#56$$#jo'FORCE--The old and new master keys are identical. No data re-encryption is required.Here I am using the same password that I used for database master key. I am not sure about the password for service master key. How do you know the password for service master key, Sarab? I couldn't retrieve the data.Mike, I didn't create the new database master key and certificate. But these are the old ones that I backed up from old backup and restored them in my current database. I sent you the queries in mail. I feel this is an issue of SERVICE MASTER KEY contention.I am safe for at least next 4-5 days and if this is not resolved, I shall report the issue :(.-LK[/quote]Hi Lucky,When I didn't receive a response from you yesterday I assumed you had resolved the issue.The SMK shouldn't be a problem (note the word "shouldn't"), since you're not restoring to a different SQL Server instance. You can test whether the SMK is an issue by explicitly opening the DMK with the OPEN MASTER KEY statement and supplying your password to it. This bypasses the SMK. Can you post the code you mentioned that you used to verify that the decryption failed after you restored the DMK and Certificate?ThanksMike C

RE: Lost Certificate and Master Key

luckysql.kinda — Tue, 25 Aug 2009 03:52:53 GMT

Hello Mike,Thanks for your help. When I use following command as per Sarab's response, I get following information:RESTORE SERVICE MASTER KEY FROM FILE = 'D:\Database Backup\smk.dat'DECRYPTION BY PASSWORD = '#56$$#jo'FORCE--The old and new master keys are identical. No data re-encryption is required.Here I am using the same password that I used for database master key. I am not sure about the password for service master key. How do you know the password for service master key, Sarab? I couldn't retrieve the data.Mike, I didn't create the new database master key and certificate. But these are the old ones that I backed up from old backup and restored them in my current database. I sent you the queries in mail. I feel this is an issue of SERVICE MASTER KEY contention.I am safe for at least next 4-5 days and if this is not resolved, I shall report the issue :(.-LK

RE: Lost Certificate and Master Key

Mike C — Mon, 24 Aug 2009 10:03:13 GMT

[quote][b]luckysql.kinda (8/24/2009)[/b][hr]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 TestCertificateWITH 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[/quote]Hi Lucky,When you create a master key or a certificate, SQL Server uses random key generation data to generate the DMK symmetric encryption key and the certificate public/private key pair. Unfortunately you have no control over that aspect. When you created a new cert and DMK, they were not the same as the old one you lost. However, you do have control over symmetric key generation (if you chose to exert it). We need a little more information to determine whether your data is recoverable. I just sent you an email, send me a response and I'll walk through it with you.ThanksMike C

RE: Lost Certificate and Master Key

Sarab_SQLGeek — Mon, 24 Aug 2009 09:52:48 GMT

[quote][b]luckysql.kinda (8/24/2009)[/b][hr]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[/quote]Hey Mike just try this option see if this works i did this a while back and this worked for me.1. Go to the Production server and backup the Service Master Key with this command.[code] BACKUP SERVICE MASTER KEY TO FILE = 'c:\smkey.dat' ENCRYPTION BY PASSWORD = 'abc@123$'[/code]2. Restore the Old backup of the database(the one which has the master key & Certificate) on a test Server.3. Backup the Master Key[code]backup master key to file = 'c:\mk.dat' encryption by password = 'Pass@12'[/code]4. Backup the Certificate[code]backup certificate mycert to file = 'c:\cert21.dat'with private key(encryption by password = 'certpass@123', file = 'c:\cert21_key.dat')[/code]5. Now import the data you want to decrypt to a new instance where we can test.6. Restore Service Master key with this command[code]RESTORE SERVICE MASTER KEY FROM FILE = 'c:\smkey.dat' DECRYPTION BY PASSWORD = 'abc@123$'[FORCE][/code]7. Restore the Database Master Key[code]restore master key from file = 'c:\mk.dat' decryption by password = 'Pass@12'encryption by password = 'Pass@12'[/code] [b]Note: In Encryption By option Use the password which you gave at the time of creating the master key on your production server[/b]8. Recreate the Certificate[code]create certificate mycert from file = 'C:\cert21.dat'with private key(file = 'Z:\cert21_key.dat',decryption by password = 'certpass@123')[/code][/code] [b]Note: In Encryption By option Use the password which you gave at the time of creating the Certificate on your production server & the code will be as below mentioned code[/b][code]CREATE CERTIFICATE User1Cert FROM FILE = 'C:\myCert.dat' WITH PRIVATE KEY (FILE = 'c:\myCertPrivateKey.dat' DECRYPTION BY PASSWORD = 'WOhPorN5VIKZl$Eqcz4CR104O' ENCRYPTION BY PASSWORD = 'WOhPorN5VIKZl$Eqcz4CR104O')[/code]Try this and let me knowthis should work.

RE: Lost Certificate and Master Key

Mike C — Mon, 24 Aug 2009 09:48:14 GMT

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.ThanksMike C

RE: Lost Certificate and Master Key

GilaMonster — Mon, 24 Aug 2009 08:45:16 GMT

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.

RE: Lost Certificate and Master Key

luckysql.kinda — Mon, 24 Aug 2009 08:34:10 GMT

Mike, Plz go through my post once or drop me a mail at luckysql.kinda@gmail.com That would be helpful.Regards,LK

RE: Lost Certificate and Master Key

luckysql.kinda — Mon, 24 Aug 2009 06:49:23 GMT

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

RE: Lost Certificate and Master Key

Sarab_SQLGeek — Mon, 24 Aug 2009 05:33:49 GMT

I am just a learner. Not a expert, I was just trying to help him.

RE: Lost Certificate and Master Key

luckysql.kinda — Mon, 24 Aug 2009 04:41:37 GMT

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

RE: Lost Certificate and Master Key

luckysql.kinda — Mon, 24 Aug 2009 04:37:29 GMT

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

RE: Lost Certificate and Master Key

Mike C — Sun, 23 Aug 2009 14:31:19 GMT

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.

RE: Lost Certificate and Master Key

GilaMonster — Sun, 23 Aug 2009 13:23:22 GMT

[quote][b]RBarryYoung (8/23/2009)[/b][hr]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.[/quote]Well, I know just about enough to spell encryption, so don't count me in with Mike.

RE: Lost Certificate and Master Key

RBarryYoung — Sun, 23 Aug 2009 12:29:23 GMT

For those who may not know, the "Mike C" above is actually the amazing Michael Coles an expert in this area (see here: [url]http://www.amazon.com/Expert-SQL-Server-2008-Encryption/dp/1430224649[/url]) 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.

RE: Lost Certificate and Master Key

Mike C — Sun, 23 Aug 2009 10:45:20 GMT

[quote][b]Sarab (8/23/2009)[/b][hr][b]They don't have any Masker key and certificate available now.[/b][/quote]The question was asked if they had a backup of the database with DMK and certificate. So the OP actually does [b]not[/b] have a backup of the database then?

RE: Lost Certificate and Master Key

Sarab_SQLGeek — Sun, 23 Aug 2009 10:28:16 GMT

[quote][b]Mike C (8/23/2009)[/b][hr]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.[/quote][b]They don't have any Masker key and certificate available now.[/b]Read this[quote]I encrypted my data with one certificate but unfortunately another dba deleted master key and certificate. [/quote]

RE: Lost Certificate and Master Key

Mike C — Sun, 23 Aug 2009 10:16:59 GMT

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.

RE: Lost Certificate and Master Key

Sarab_SQLGeek — Sun, 23 Aug 2009 05:33:53 GMT

If you're testing this on the same server then its Okotherwise you need to backup and restore the Service Master key also.

RE: Lost Certificate and Master Key

Sarab_SQLGeek — Sun, 23 Aug 2009 05:29:24 GMT

You can take the backup of Master key as well as the Certificate with the below mentioned scripts:[b]From the Instance which you've restored from your backup[/b]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')[b]To Restore the same on your test server Use these Scripts[/b]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')[b]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.[/b]Test this if u face any problem, let me know.

RE: Lost Certificate and Master Key

luckysql.kinda — Sun, 23 Aug 2009 05:22:46 GMT

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

RE: Lost Certificate and Master Key

GilaMonster — Sun, 23 Aug 2009 04:05:19 GMT

[quote][b]luckysql.kinda (8/23/2009)[/b][hr]Yes the older one I have but that is really very old. [/quote]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.

RE: Lost Certificate and Master Key

luckysql.kinda — Sun, 23 Aug 2009 03:29:29 GMT

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

RE: Lost Certificate and Master Key

GilaMonster — Sun, 23 Aug 2009 02:57:51 GMT

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.

Lost Certificate and Master Key

luckysql.kinda — Sun, 23 Aug 2009 02:56:21 GMT

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