0

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?

0

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.