Decrypting a Master Key works well using SQLCMD but not in the script

Question

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?

• S4V1N: Is that TDE encryption? Did you try to restore database using those commands from the sqlcmd, and did it work? Master Key is created on the master DB, and is specified one per instance. Certificate and Database encryption keys are specified per DB.
• John Eisbrener: What is the default database listed for your user account? The difference between the two scripts is the lack of USE [MyDatabase]; in the script.sql file. Try including this statement and see if that solves it for you.
• AndroX: @JohnEisbrener You make my day. In the routine before executing this one I moved the pointer to the master db and after I forgot to refocus to the db. Thank you so much! it works now.
• AndroX: @S4V1N for sake of completeness. Yet it is TDE encryption.

Answer 1

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;';