I recently had to recover my ssisdb databases, along with all other user databases to a new server because of a hardware failure.
I am now seeing the following errors when trying to run ssis jobs on the new machine Please create a master key in the database or open the master key in the session before performing this operation
I believe I have found the correct way to correct the problem, but I am hoping someone with more experience can confirm my plan.
Since the key was not backed up separately, my understanding is that I need to decrypt the existing database master key with the password used during the creation of the catalog. Thankfully I do have that password.
I could then alter the master key and add encryption with the new service master key, so that the key can be decrypted automatically.
use SSISDB go open master key decryption by password= '<password from catalog creation>' alter master key add encryption by service master key
Does this appear to be the correct path, or am I way off base?