master key error when migrating SSISDB

• lmarkum

  SSCertifiable

  Points: 7989

  More actions

  August 26, 2017 at 3:44 pm

  #338818

  I have a production SQL Server down for migration to a new SQL instance. I'm attempting to migrate SSISDB to a new server. I do not have the initial password used to create/encrypt the key. I'm using the processes in the links below. I have used this process successfully several times involving these same servers during testing and not had an issue.

  https://msdn.microsoft.com/en-us/library/hh213291(v=sql.120).aspx

  http://sqlblog.com/blogs/andy_leonard/archive/2016/11/27/deploying-ssis-projects-to-a-restored-ssis-catalog-ssisdb.aspx

  Backing up Master Key of SSISDB on Source Server
  BACKUP master key to file = 'C:\masterkeyDWSQL01'
  encryption by password = 'Z1glM!gl2017';
  GO

  Backup SSISDBdatabase to new server.

  Ensure CLR is enabled

  Create Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey

  Create a login for the key and grant unsafe assembly to the user

  RESTORE SSISDB WITH REPLACE

  Create the CleanupJobLogin ssis_startup proc and SSIS Server Maintenance job

  Restore the master key on target server

  RESTORE MASTER KEY FROM FILE = 'C:\masterkeyDWSQL01'
  DECRYPTION BY PASSWORD = 'Z1glM!gl2017'
  ENCRYPTION BY PASSWORD = 'Z1glM!gl2017'

  Error:
  Msg 15329, Level 16, State 30, Line 208
  The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.