Sql Server 2008R2 enterprise with data encryption - backup and restore

Question

I have a DB on which I enabled symmetric encryption, to encrypt some columns. I created a SP to enable encryption after I create the DB from script, as below

CREATE PROCEDURE [dbo].[sys_EnableSymmetricEncryption]
AS
BEGIN
--If there is no master key, create one now. 
IF NOT EXISTS 
  (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
  CREATE MASTER KEY ENCRYPTION BY 
  PASSWORD = '9809u0ij989oih9o8yyo98yyo89uyp9p9'

CREATE CERTIFICATE My_Certificate
  WITH SUBJECT = 'My Database';

CREATE SYMMETRIC KEY My_Key_01
  WITH ALGORITHM = AES_256
  ENCRYPTION BY CERTIFICATE My_Certificate;
END 

So after I create db from script, I run this and then I can store data in encrypted columns
with

INSERT INTO [dbo].[Cards]
      ([CardNumber]
      ,[CardSecurityCode]
      ,[CardExpirationDate]
      ,[NameOnCard])
 VALUES
  (EncryptByKey(Key_GUID('My_Key_01'), @CardNumber) ,
  EncryptByKey(Key_GUID('My_Key_01'), @CardSecurityCode) ,
  EncryptByKey(Key_GUID('My_Key_01'), @CardExpirationDate) ,
  EncryptByKey(Key_GUID('My_Key_01'), @NameOnCard))

and retrieve data with

OPEN SYMMETRIC KEY My_Key_01
   DECRYPTION BY CERTIFICATE My_Certificate;

SELECT [CardID]
      ,CONVERT(nvarchar, DecryptByKey([CardNumber])) as 'CardNumber'
      ,CONVERT(nvarchar, DecryptByKey([CardSecurityCode])) as 'CardSecurityCode'
      ,CONVERT(nvarchar, DecryptByKey([CardExpirationDate])) as 'CardExpirationDate'
      ,CONVERT(nvarchar, DecryptByKey([NameOnCard])) as 'NameOnCard'
    FROM [Cards]

All works well till I backup db and try to restore the backup on a different server After I restore, when I try to run Select (as above) I get this error

Please create a master key in the database or open the master key in the session before performing this operation.

So I was trying to recreate the key with

IF NOT EXISTS 
  (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
  CREATE MASTER KEY ENCRYPTION BY 
  PASSWORD = '9809u0ij989oih9o8yyo98yyo89uyp9p9'

But then on select In still get the same error. If I try to run the first Sp shown above, to create master key, cert and symmetric key, I get error

Msg 15581, Level 16, State 3, Procedure sys_EnableSymmetricEncryption, Line 11 Please create a master key in the database or open the master key in the session before performing this operation. Msg 15282, Level 16, State 1, Procedure sys_EnableSymmetricEncryption, Line 14 A key with name 'OneTest_Key_01' or user defined unique identifier already exists or you do not have permissions to create

Maybe I should try to delete the certificate and symmetric key before trying to create them, but I don't know where are they located.

Any idea how to handle this?

Thanks

PS if I restore in same server, even as different database, all works fine

Answer 1

Try to run:

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '9809u0ij989oih9o8yyo98yyo89uyp9p9';