Moving symmetric keys between servers

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:

name principal_id symmetric_key_id key_length key_algorithm algorithm_desc create_date modify_date key_guid HR01 1 256 56 D DES 2006-11-22 16:36:01.883 2006-11-22 16:36:01.883 BBD80500-338F-47D7-B336-85D46E00F2F0

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:

name principal_id symmetric_key_id key_length key_algorithm algorithm_desc create_date modify_date key_guid ##MS_DatabaseMasterKey## 1 101 128 D3 TRIPLE_DES 2007-04-23 16:03:09.183 2007-04-23 17:02:46.630 1CB0D800-0173-4A1E-B841-362B454E60AC HR01 1 256 56 D DES 2006-11-22 16:36:01.883 2006-11-22 16:36:01.883 BBD80500-338F-47D7-B336-85D46E00F2F0

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