Decrypting Returns A NULL Value

I find it weird when decrypting a column from a baked up database and restoring it to another database. Here's the scenario:

Server1 has Database1.
Database1 has Table1 with two columns encyrpted -- Card Number and SS Number
Encryption and decryption in this Database1 is perfectly fine. Records are encrypted and can be decrypted too.
Now, I tried to backup this Database1 and restore it to another server with SQL 2005 instance called Server2. Of course the columns Card and SS Numbers were encrypted. I tried decrypting the columns using the same command to decrypt in Database1, however, it returns a NULL value


Here's exactly what I did to create the encyprtion and decryption keys on the restored database:
-- Create the master key encryption
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myMasterPassword'

-- Create a symetric key
CREATE SYMMETRIC KEY myKey WITH ALGORITHM = DES
ENCRYPTION BY Password='myPassword';
Go

-- Create Card Certificate
CREATE CERTIFICATE myCert WITH SUBJECT = 'My Certificate on this Server';
GO

-- Change symmetric key
OPEN SYMMETRIC KEY myKey DECRYPTION BY PASSWORD = 'myPassword';

-- I then verified if the key is opened
SELECT * FROM sys.openkeys




If I create a new database, say Database2 from that Server2, create table, master key, certificate, and symmetric key. Encrpytion and decryption on Database2 will work!




Any suggestions gurus? I tried all searches and help for almost 2 weeks regarding this issue but nobody could resolve this.

Thanks in advance!



faiga16



3 Posts