SSISDB master key

Question

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?

Answer 1

I did some reading, and opening the master key was only part of what I had to do. I had to completely configure the new server for SSIS. I found the following blog post helpful, https://andyleonard.blog/2017/07/deploying-ssis-projects-to-a-restored-ssis-catalog-ssisdb/

The following microsoft documentation was also a good second source of information, as a check against the blog posting.

This is the portion of Andy Leonard's script that I used to fix my issue, and open the ssisdb,

-- Another method for restoring the master key from the file.
-- NOTE: You must have the original SSISDB encryption password!
print 'Opening the master key'
Open master key decryption by password = 'SuperSecretPassword' --'Password used when creating SSISDB'   -- *** check this, please – Andy
Alter Master Key
Add encryption by Service Master Key
go
print 'Master key opened'

Answer 2

I would go with this Microsoft example that closes the master key after you are done. https://msdn.microsoft.com/en-us/library/ms174433.aspx

USE master;  
OPEN MASTER KEY DECRYPTION BY PASSWORD = '43987hkhj4325tsku7';  
GO  
CLOSE MASTER KEY;  
GO