SQL Server – Restore Database Encrypted by Master Key Using Backup

Without a doubt, a new design is needed for step 2. I would suggest that, instead of passing the Database Master Key password, which typically remains fairly constant and is not changed frequently, step 2 would back up the Database Master Key using a randomly generated password of sufficient length. The password can then be encrypted at the source, passed to the destination server encrypted, then decrypted and used to restore the Database Master Key. For this design, you will need to add a procedure, a certificate and a view to the source server msdb database and a procedure and a certificate to the desitination server msdb database. You will also need to create a share folder on the destination server with write permissions granted to the prod server's SQL Agent service account. The objects involved are:

• ProdServer.msdb.vwGetRandomPass - view that generates random long passwords
• ProdServer.msdb.DMKEncryptionCertificate - certificate to encrypt DMK password
• ProdServer.msdb.usp_BackupDMK - retreieves a random password from the view vwGetRandomPass, uses it to back up the DMK to the share, encrypt the password using the certificate DMKEncryptionCertificate, return the encrypted password as an output varbinary variable
• TargetServer.msdb.DMKEncryptionCertificate - certificate to decrypt DMK password restored from a backup of the cert and private key on the prod server
• TargetServer.msdb.usp_restoreDMK - accepts varbinary parameter password, decrypts password using DMKEncryptionCertificate, restore DMK from share using decrypted password, upon successful restore delete the DMK file

Step 2 would consist of calling the prod server procedure, followed by a call to the TargetServer procedure to complete the restore. You can use a linked server, osql call or other method to call the procedure on the target server.

For even further security, you can drop the private key on the Production Server after backing it up. That way only the destination server can decrypt the password. The password will also be different and unpredictable every day. The other benefit is that the DMK would be deleted every time and would only exist on the share for the duration of step 2, which should be a matter of seconds.

This can be done successfully, however, I would also ask if it should be done. If this data is so sensitive that it needs to be encrypted, then should it be available outside of your production system? If you decide against it, then you could just drop the certificate and symmetric key in the target database and create new ones with the same name to avoid exceptions. Any call using these would return null. I've included the view below:

/*
generates a random 128 character string from all 
valid password characters except single quote

*/
CREATE VIEW dbo.vwGetRandomPass
AS
WITH s1
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 48
            AND 57 -- number characters
    ORDER BY newid()
    )
    ,s2
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 65
            AND 90 -- Upper letters
    ORDER BY newid()
    )
    ,s3
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 97
            AND 122 -- Lower letters
    ORDER BY newid()
    )
    ,s4
AS (
    SELECT TOP 32 CHAR(number) AS chr
    FROM master..spt_values
    WHERE number BETWEEN 33
            AND 38
        OR number BETWEEN 40
            AND 47 -- sign characters
        OR number BETWEEN 58
            AND 64
        OR number BETWEEN 91
            AND 96
        OR number BETWEEN 123
            AND 126
    ORDER BY newid()
    )
    ,final
AS (
    SELECT chr
    FROM s1

    UNION ALL

    SELECT chr
    FROM s2

    UNION ALL

    SELECT chr
    FROM s3

    UNION ALL

    SELECT chr
    FROM s4
    )
SELECT pass = (
        SELECT chr AS [text()]
        FROM final
        ORDER BY newid()
        FOR XML path('')
        )
GO

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available.

I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key.

What you need to do is to restore the database, open the database master key using the DBMK password and then add the Server 2 SMK encryption to the DBMK:

RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;