January 10, 2017 at 11:11 am
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.
Not sure what is happening here. Any help would be greatly appreciated.
January 10, 2017 at 11:27 am
Sounds to me like the password is incorrect. The database master key is not the same thing as the sa password or anything like that normally unless you changed it to that.
I would do a backup of the DBMK on the production server and restore that onto the test server with the FORCE option. That should correct the issue.
but ignore the "Answer" as the first comment is a much better answer.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 10, 2017 at 1:35 pm
bmg002 (1/10/2017)
Sounds to me like the password is incorrect. The database master key is not the same thing as the sa password or anything like that normally unless you changed it to that.I would do a backup of the DBMK on the production server and restore that onto the test server with the FORCE option. That should correct the issue.
but ignore the "Answer" as the first comment is a much better answer.
That did it! Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply