Hi there,
to follow on my question in the other forum, I have a database with triple des encrypted columns
Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)
Can select etc, all works fine.
Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.
Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?
Thanks
When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.
If you opened the master key using a password, things should also work with no change.
But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.
Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.
Thanks
Laurentiu
Hi,
Thanks Laurentiu.
I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)
|||This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.
Thanks
Laurentiu
Hi,
Ok source database -
create master key with password
create certificate
create symmetric key with encryption by certificate created above
open key
insert values into table (using encryptbykey)
create view that that uses decryptbykey, all works fine.
Backup database
Restore on 2nd machine
Open the master key
Alter master key add encryption by service master key
After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.
Will run through this again and see if I missed anything.
|||
The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.
One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).
Thanks
Laurentiu
Hi,
Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,
Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.
Will leave the server "as is" for now and check it again later to see if it's successfull then.
This made me think of some extra security questions, will post in a seperate thread :)
|||You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.
The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.
Thanks
Laurentiu
This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.
This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)
If you need any other info, please let me know.
open symmetric key sk_player decryption by certificate cert_sk_admin;
select * from sys.openkeys
select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees
select * from v_employees
select * from sys.openkeys
This was done before and only once, as it couldnt open the symmetric key without the master key being open first.
open master key decryption by password = 'xxxkickasspasswordxxxx';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||Hi
Problem still occuring.
Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?
Thanks
|||There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?
Thanks
Laurentiu
Hi there,
I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)
create certificate cert_sk_td3 with subject = 'Certificate'
create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;
Let me know if you need any more info
|||We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.
You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?
I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.
In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)
Thanks
Laurentiu
Hi Laurentiu,
In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)
What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.
I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).
I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.
I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.
This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.
Is there any trace or debugging that I can enable that might give some more info?
No comments:
Post a Comment