SQL- Encryption Database

Question

I have taken backup from the live environment and trying to use it on the local box. The database in the live is encrypted with a key.

I have restored the database to localbox and cannot run any stored procedures as I have the following issue

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

could anybody have faced this issue and how did you managed to solve. could you provide the answer step by step ?

I know the passowrd for the encrption on the live DB.

Answer 1

The database in the live is encrypted with a key.

I assume by that that you mean the database is using Transparent Database Encryption. The process of moving a TDE database to another server is described at Moving a TDE Protected Database to Another SQL Server, and it involves explicitly moving the database encryption key by doing a backup of the certificate used to encrypt the TDE key, which was previously backed up from the live server:

On the live server:

BACKUP CERTIFICATE ...
TO FILE = '...'
WITH PRIVATE KEY 
(
    FILE = '...',
    ENCRYPTION BY PASSWORD = ...'
);
GO

On the local server:

CREATE CERTIFICATE ...
FROM FILE = '...'
WITH PRIVATE KEY 
(
    FILE = '...',
    DECRYPTION BY PASSWORD = '...'
);
GO

With the certificate in place the local SQL Server will be able to decrypt the TDE database.

If you are not talking about TDE, then you need to explain what do you mean by 'the database is encrypted with a key'.