I'm having some issues restoring a backup of database that uses native encryption onto another server. I know there are a couple of articles on this but I seem to be missing something. Any help would be greatly appreciated.
Current Server Windows 2000
Destination Server Windows 2003
The original key setup for the current server was achieved by something like this:
CREATE SYMMETRIC KEY HR01 WITH algorithm=DES encryption BY password = 'HRpassword'
Running the command select * from sys.symmetric_keys on the current server I get the following:
So I restored the database onto the new server and ran this script:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
I don't have a master key... so I go back to the original server and ran this script and did another backup:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
Running select * from sys.symmetric_keys on the current server now looks like this:
I then restored the new backup onto the 2003 server and ran this script:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
The alter script now runs fine but I get the error message: The decryption key is incorrect when trying to open the HR01 key
We have seen other cases of problems using DES on Windows 2000 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1122121&SiteID=1). Unfortunately the DES (DES only, not TRIPLE_DES) implementation on Win2k is not as robust as on later versions of the OS. We typically recommend against using DES, and we strongly recommend against using DES on Windows 2000 platforms (use TRIPLE_DES instead).
My personal recommendation on this case would be to try to restore the DB temporarily on a Windows 2000 machine, recover the data and re-encrypt it using TRIPLE_DES algorithm.
By the way, because you are using protection by password instead of by a certificate, the DB master key steps should not be needed in your case.
I hope this information helps. Please, let us know if there is anything else we can do to help.
-Raul Garcia
SDE/T
SQL Server Engine
