Hi
Can anyone help?
I have a database using encryption with symmetric keys created using asymmetric keys.
I backed up this database and restored it on another machine along with the service master key.
I can read the encrypted data fine. But all the encrypted stored procs that are associated with the application that uses the data cannot read the data, they just return Nulls.
If I regenerate all the encrypted stored procs from script they work fine again.
Is there a way to restore the database without having to regenerate all the encrypted procs?
You are probably missing this sequence after you restore the database:
1) open master key using password
2) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Your script probably creates the database master key too, so you end up recreating it with an encryption of the service master key, and everything works, but you only need the two commands above, to make your database work after restore.
See http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx for additional information on the master keys.
Thanks
Laurentiu
Hi
Well now I am really puzzled. I created the same database on 2 machines then restored both databases back on to a third machine. I can read the eccrypted data from one of the backups but not from the other. All 3 machines are running XP and SQL 2005
On the both databases with encrypted data I ran the following
OPEN MASTER KEY DECRYPTION BY PASSWORD = asecretpassword
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then ran the script to read my encrypted data
Just done some more checking. If i take a database that I created on one machine and can read on another then restore it onto the 3rd then I cannot read it on the 3rd.
(This is also the machine I created the database on that I could not read on other machines)
When I try to open my symmetric key I get the following error
This is after opening and altering the master key as above
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
It seems that their is something incompatable on the third machine yet it is running the same version of OS and SQL as the other machines
|||
Are your machines in the same time-zone? Is the time properly synchronized between them? There is a known issue with restoring databases on a machine that has the time set "in the past". See page 2 of this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177863&SiteID=1. But I'm not sure you're hitting this issue.
Can you post the statement that you execute before getting the error? Also, can you tell me some details about the key: what algorithm are you using and how did you create the key.
Just to make sure I understand the scenario: what you see is that whatever you encrypt on the third machine, you cannot decrypt on the other machines, and what you encrypt on the other machines, you cannot decrypt on this third machines. Otherwise, for the other machines, encryption and decryption works ok when restoring databases from one to another. Is this correct?
Thanks
Laurentiu
Also, can you execute winver on all three machines and post the version line including the build number information for all three of them? I just need the line under Microsoft Windows.
Thanks
Laurentiu
Hi Laurentiu
I have not tried writing encrypting any data on the databases I cannot read encrypted data from, I am more concerned with being able to read the data but I doubt that I would be able to write data as the errors i get indicate that the asymetric keys cannot be opened.
All machines are in the same time zone and domain except the 2003 server which is in Holland whilst the rest are in the UK and we are not using certificates. So I dont think it is a time zone thing
I have created another database build on a 4th machine running 2003 Server and copied a backup of that database onto my XP machines and I cannot read the encrypted data from the 2003 backup on any of the XP machines I have tried this on.
However if I restore the master key from the any machine where the database was created with the force option I can read the data.
The restore of the master key gives me a message saying that it could not open the asymmetric keys
I run the following bit of code to read the data
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Asecretpassword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
This appears to run OK
Open Symmetric Key NoKeySymetricKey
Decryption by ASYMMETRIC KEY NoKey
SELECT top 10
CONVERT(VARCHAR(50),
DecryptbyKey(EncryptedNo)) AS ClearNumber
From MyTable
Close Symmetric Key NoKeySymetricKey
I get the following error
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
(10 row(s) affected)
Msg 15315, Level 16, State 1, Line 8
The key 'NoKeySymetricKey' is not open. Please open the key before using it.
Winver from the machines I have tried this on
(Mine)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)
(Andrews)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)
(Chris's)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
Can backup and restore between above machines OK but not restore from the 2 below
(Build Machine)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
The database built on this machine cannot be read on the first 2 of the 3 above machines;
have not tried it on the server below or on the machine runing Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2) (Chris's)
2003 Server
Version 5.2 (Build 3790.srv03_sp1_rtm.050324-1447 : Service Pack 1)
The database created on this machine can not be read on any of the other machines
I built the keys on all machines/databases with the same bit of code below
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asecretpassword'
CREATE ASYMMETRIC KEY PassKey
WITH ALGORITHM = RSA_512
CREATE ASYMMETRIC KEY NoKey
WITH ALGORITHM = RSA_2048
CREATE SYMMETRIC KEY NoKeySymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY NoKey
CREATE SYMMETRIC KEY PassSymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY PassKey
Hi
Just tried the restore of database from another XP maxhine and it fails. If I force restore the master key it works OK. However if I try to restore without the force option I get the following message
Msg 15320, Level 16, State 2, Line 2
An error occurred while decrypting asymmetric key PassKey that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.
|||
So it looks like the asymmetric keys cannot be decrypted unless you force restore the master key. It's strange that the master key appears to be valid, given that you receive no error when you try to open it.
Can you try encrypting something with the master key immediately after restore? Try creating another asymmetric key, then use that asymmetric key to encrypt something and see if you get any errors.
I'd also like to know if the same problem affects certificates.
Also, note that the timezone issue that I mentioned earlier affects symmetric keys, it's not certificate related. But now I'm pretty positive you're seeing a different issue.
Here's something else I'd like to ask: can you create a simple test database with a master key (password = BugCrypto90, let's say), an asymmetric key, and a symmetric key, on both your machine and the build machine. The databases should not contain any other data and you should see the same issue for them when trying to restore them on the other machine (i.e., opening the symmetric key should fail with a decryption error). Then submit a bug report at http://lab.msdn.microsoft.com/productfeedback/, and attach compressed backups of these two databases to the bug. Add a link to this thread in the description and ask for the bug to be assigned to me. If you use a different password for the keys, mention it in the description as well. This way, I can also try to reproduce this problem on our machines.
Thanks
Laurentiu
Hi Laurentiu
I will submit a bug report as asked.
However in the meantime I tried creating a new asymetric key on my restored database and encrypting some data. Everything went OK I was able to both write and read encrypted data created with my newly created asymetric key even though I couldnt read my old data.
Martin
|||Hi Again
I have logged the problem as a bug report but I dont know if you are going to be able to reproduce it.
I tried as you asked and created a test database on my machine and the build machine and created keys using the same method I used on my live database and I can backup the databases and restore them on different machines and open the symetric keys OK.
However I still have a problem with my database - will continue testing and let you know if I can re-produce it
Martin
|||Can you also look into whether the problem is specific to asymmetric keys or can be reproed with certificates as well?
Thanks for taking the time to file the report.
Laurentiu
|||I've received the bug report, but there are no attachments. Have you attached the test databases when you filed the report?
Thanks
Laurentiu
Hi
I have attached the databases again.
|||I do not see the attachments yet. Have you pressed the "Add Attachment" button to actually attach the files? You need to press that button after you browse-select the file.
Thanks
Laurentiu
Hi
will try again
It may be the security settings on our proxy server; they are pretty tight.
If it doesnt work let me know and I will do it from home
Martin
No comments:
Post a Comment