How to fix SSIS deployment error "please create a master key"

by Pio Balistoy on Apr 24, 2019 12:00:00 AM

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 password

Script 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'

Applies to:

SSIS Catalog has been around since SQL Server 2012.

Note:

If you're receiving this after you have restored SSISDB from backup, especially if that backup is from another server, you might encounter more issues. There are a number of additional steps that need to be done when restoring an SSISDB database backup from another server. You can refer here, for the proper steps to take for restoring SSISDB to another server.

Topics: Microsoft SQL Server Technical Track Cloud Line 1 Sql Backup To Url Error Azure Blob Storage Url Backup Error

Comments (1)

See all

Share this