Problem:
You are deploying a SQL Server Integration Services Package to the SSIS Catalog and keeps on failing with the following error:
The error is succinct and direct to the point: "Please create a master key in the database or open the master key in the session before performing this operation."
Cause:
You have received the error because the master key on the SSISDB database is not open or does not exist. This usually happens when you have restored SSISDB and you did not open the master key afterwards.Fix:
To address this specific error, you'll need to open the master key. To do this, you should know the original password for the master key of the database.open master key decryption by password = 'P@ssword!' --'Password used when creating SSISDB' Alter Master Key Add encryption by Service Master Key
Workaround:
But what if you don't know the master key password? To force this, you can back up the master key to file with a new password, then restore it back. Now you can open it with a new password: Script to back up the master key:backup master key to file = 'C:\powershell\masterkey' --Replace with the location where you can save it. encryption by password = 'REPLACE WITH PASSWORD' --replace with passwordScript to restore it:
Restore master key from file ='C:\powershell\masterkey' --Replace with the location of your masterkey backup Decryption by password = 'REPLACE WITH PASSWORD'-- 'Password used to encrypt the master key' Encryption by password = 'REPLACE WITH PASSWORD' NOTE: Since the master keys are essentially the same, you may receive this warning: The old and new master keys are identical. No data re-encryption is required.Script to open it:
open master key decryption by password = 'REPLACE WITH PASSWORD'-- 'Password used to encrypt the master key'
