2

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?

  • 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. – S4V1N Jul 13 '17 at 10:31
  • 2
    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. – John Eisbrener Jul 13 '17 at 12:17
  • @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 Jul 13 '17 at 12:58
  • @S4V1N for sake of completeness. Yet it is TDE encryption. – AndroX Jul 13 '17 at 12:59
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.