I have an SQL server 2017 ssisdb backup and password that was used when creating the catalog.

I have restored db on a new server (same sql version) by following the steps given here: https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15#to-restore-the-ssis-database

On step 7 I am using the method number 2:

open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'  
       Alter Master Key Add encryption by Service Master Key

The 1st line is working successfully there by confirming that the password I am using is correct. However the 2nd line gives the following error: An error occurred during service master key decryption

What is the way to debug and resolve this issue?

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-master-key-transact-sql?view=sql-server-ver15#remarks suggests that:

ADD ENCRYPTION BY SERVICE MASTER KEY causes a copy of the master key to be encrypted using the service master key and stored in both the current database and in master.

Could the error occur because I have also restored the master db using backup from source server?


Your Answer

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

Browse other questions tagged or ask your own question.