While doing a SSIS project deployment on my staging server from my desktop I ran into the following error
"Please create a master key in the database or open the master key in the session before performing this operation, ( Microsoft SQL server, Error:15581)"
The staging SSISDB database was a restore copy of the production .
I tried to alter the master key with the pwd from the prod
USE [SSISDB]
OPEN master Key decryption by password ='password' --my prod password
ALTER Master Key ADD encryption by Service Master Key
But again got the following error -
Msg 15313, Level 16, State 1, Line 2
The key is not encrypted using the specified decryptor.
Msg 15581, Level 16, State 7, Line 3
Please create a master key in the database or open the master key in the session before performing this operation.
Tried to create a master key -
Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.
tried to drop the master key -
use ssisdb
GO
drop master key
Got the following error
Msg 15580, Level 16, State 1, Line 1
Cannot drop master key because certificate 'MS_Cert_Proj_1' is encrypted by it.
Solution :
I backed up the SSIS Key on prod from the following
use ssisdb
GO
BACKUP MASTER KEY TO FILE = 'v:\backupkeys\SSISDBKey1
ENCRYPTION BY PASSWORD = 'password'
Then I copied the key file over to the test server in v:\backupkeys_staging\SSISDBKey1
use ssisdb
GO
RESTORE MASTER KEY FROM FILE = 'v:\backupkeys_staging\SSISDBKey1'
DECRYPTION BY PASSWORD = 'password' --prod password used to backup the key
ENCRYPTION BY PASSWORD = 'newpassword'
FORCE
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.