3

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?

2

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'
  • you can mark your own answer as accepted since you solved it! Also, if you have some time to add some of the content from the link, it'd help in case that website goes down or that page becomes unavailable in the future! – LowlyDBA Apr 23 '18 at 22:26
0

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  

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.