Sql-server – Decrypting a Master Key works well using SQLCMD but not in the script

sql serversql server 2014sqlcmdt-sql

I would like to use a script to automatize the process of migration of a Db in another instance. The DB is encrypted, so after restore it I need to decrypt it and encrypt again using the new Service Key.

Quite easy from SQLCMD line after restore (SQLCMD -S.\myinstance )

USE [MyDatabase];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '...';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

And everything goes flawlessy

If instead I put it in a script (I would like to store the password and the dbname in variables) and I run it I receive the error message

Cannot find the symmetric key 'master key', because it does not exist or you do
not have permission.

Of course I'm running the script within the same user (SQL -S.\myistance -i script.sql)

declare @dbMKey nvarchar(100);
DECLARE @exec_sql nvarchar(max);

    SET @exec_sql = 'OPEN MASTER KEY DECRYPTION BY PASSWORD = N' + quotename(@dbMKey,'''') + '; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; CLOSE MASTER KEY;';  
    print @exec_sql;
    EXEC sp_executesql @exec_sql

I add a print for debugging purpose to be sure that the sql command was exactly the same I wrote by hand.

Anyone has some idea?

Best Answer

As @JohnEisbrener make me noticed in the comment, the issue is due to the fact that the script is missing of the USE [mydatabase] statement.

So the right way to write the stored procedure variable is

SET @exec_sql = 'USE MYDATABASE; OPEN MASTER KEY DECRYPTION BY PASSWORD = N' + quotename(@dbMKey,'''') + '; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; CLOSE MASTER KEY;';
Related Question