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.
Tuesday, January 10, 2017
SSIS error while project deployment from the local machine to the server.
Labels:
backup key,
restore database,
restore key,
sql,
SSIS error,
SSISDB,
SSISDB master KEY
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment