How to restore a database with SMK and Cerificate to another computer

Question

On ComputerA, I have a database with SMK and a certificate. I've created this with the following code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO

CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO

CREATE SYMMETRIC KEY SymmetricKey1 
WITH ALGORITHM = AES_128 
ENCRYPTION BY CERTIFICATE Certificate1;
GO

Now I have created a backup file for this to restore it on ComputerB. The restore works fine but when I try to execute the following code on ComputerB:

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID 
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;

 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;

It returns the error:

Please create a master key in the database or open the master key in the session before performing this operation.
The key 'SymmetricKey1' is not open. Please open the key before using it.

I've tried deleting the SMK and Cert. under the Security Folder it still returns the same error. Any idea how to solve this?

score 1 · Accepted Answer · answered Jun 27 '18 at 11:32

I think you might be encountering the same problem as How to restore symmetric key on the new database?. Excerpted from the accepted answer on that post:

A symmetric key that was created without specifying the KEY_SOURCE and IDENTITY_VALUE can never be scripted or copied.

If you can recreate the symmetric key on the source database and specify the KEY_SOURCE and IDENTITY_VALUE, I think you will be able to open it on the target server.

Referencing Create Identical Symmetric Keys on Two Servers:

In order to decrypt ciphertext, you need the key that was used to encrypt it. When both encryption and decryption occur in a single database, the key is stored in the database and it is available, depending on permissions, for both encryption and decryption. But when encryption and decryption occur in separate databases or on separate servers, the key stored in one database is not available for use on the second database

From that post, here is an example of creating a symmetric key using KEY_SOURCE and IDENTITY_VALUE:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My p@55w0Rd';  
GO  
CREATE CERTIFICATE [cert_keyProtection] WITH SUBJECT = 'Key Protection';  
GO  
CREATE SYMMETRIC KEY [key_DataShare] WITH  
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',  
    ALGORITHM = AES_256,   
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'  
    ENCRYPTION BY CERTIFICATE [cert_keyProtection];  
GO

On the target server, after your restore, you might need to run the following:

-------------------------------------------------------------------------------------
-- When a backup is restored to a different server, the following script must be run
-- so the encrypted data will be accessible with the standard stored procedures.
-- Run this in the database containing the encrypted data.
-------------------------------------------------------------------------------------
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<original password>';
GO
-------------------------------------------------------------------------------------
-- This will alter the master key to drop service master key encryption
-- The service master key is server-specific so it's tied
-- to the server the backup came from.
-------------------------------------------------------------------------------------
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY; 
GO
-------------------------------------------------------------------------------------
-- This will alter the master key to add new service master key 
-- encryption based on the current server.
-- This is done so you don't have to open the master key
-- with a password each time you use the certificate and symmetric key.
-------------------------------------------------------------------------------------
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
CLOSE MASTER KEY;

How to restore a database with SMK and Cerificate to another computer

1 Answers1