August 24, 2009 at 5:33 am
I am just a learner. Not a expert, I was just trying to help him.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 24, 2009 at 6:49 am
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
August 24, 2009 at 8:34 am
Mike, Plz go through my post once or drop me a mail at luckysql.kinda@gmail.com
That would be helpful.
Regards,
LK
August 24, 2009 at 8:45 am
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.
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
August 24, 2009 at 9:48 am
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.
Thanks
Mike C
August 24, 2009 at 9:52 am
luckysql.kinda (8/24/2009)
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
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.
BACKUP SERVICE MASTER KEY TO FILE = 'c:\smkey.dat'
ENCRYPTION BY PASSWORD = 'abc@123$'
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
backup master key to file = 'c:\mk.dat'
encryption by password = 'Pass@12'
4. Backup the Certificate
backup certificate mycert to file = 'c:\cert21.dat'
with private key(
encryption by password = 'certpass@123',
file = 'c:\cert21_key.dat')
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
RESTORE SERVICE MASTER KEY FROM FILE = 'c:\smkey.dat'
DECRYPTION BY PASSWORD = 'abc@123$'[FORCE]
7. Restore the Database Master Key
restore master key from file = 'c:\mk.dat'
decryption by password = 'Pass@12'
encryption by password = 'Pass@12'
Note: In Encryption By option Use the password which you gave at the time of creating the master key on your production server
8. Recreate the Certificate
create certificate mycert from file = 'C:\cert21.dat'
with private key(file = 'Z:\cert21_key.dat',
decryption by password = 'certpass@123')
[/code] 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
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')
Try this and let me know
this should work.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 24, 2009 at 10:03 am
luckysql.kinda (8/24/2009)
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
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.
Thanks
Mike C
August 25, 2009 at 3:52 am
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
August 25, 2009 at 8:32 am
luckysql.kinda (8/25/2009)
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
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?
Thanks
Mike C
August 25, 2009 at 9:37 am
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.
Thanks
Mike C
August 25, 2009 at 9:47 am
You have really been a great help Mike. Thanks for a great help once again.
-LK
September 3, 2009 at 11:52 am
HI I am having same problem can i have script for this .
thanks
Rohit
MCP
September 3, 2009 at 12:38 pm
ROHIT KAPOOR (9/3/2009)
HI I am having same problem can i have script for this .thanks
Rohit
MCP
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.
Thanks
Mike C
September 4, 2009 at 6:13 am
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 DW01
GO
CREATE 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_DES
ENCRYPTION BY CERTIFICATE test
i am backing the certificate with following sql
BACKUP SERVICE MASTER KEY TO FILE = 'C:\DWH_ServiceMasterKey.smk'
ENCRYPTION BY PASSWORD = 'pass123
GO
BACKUP MASTER KEY TO FILE = 'C:\DWH_MasterKey.msk'
ENCRYPTION BY PASSWORD = 'pass123'
GO
BACKUP CERTIFICATE P14N TO FILE = '' -- Location to backup, for example: C:\DWH_P14NCertificate.cer
WITH PRIVATE KEY(
FILE = 'C:\DWH_P14NCertificate_PrivateKey.pky' ,
ENCRYPTION BY PASSWORD = 'pass123');
GO
i 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 SQL02
DROP SYMMETRIC KEY testKey
GO
DROP CERTIFICATE test
GO
- Create certificate from backed up certificate file
CREATE 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 certificate
CREATE SYMMETRIC KEY testkey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE test
GO
But 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/DW01
So i was am not sure where i am going wrong .can you please help me .
Thanks
Rohit
September 4, 2009 at 9:02 am
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
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply