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?