I had to move the SSISDB database f rom server1 to Server2
On Server 1 I did the following
Backed up the master key
USE SSISDB
BACKUP MASTER KEY TO FILE = 'C:\MSSQL\Keys\SQLSVER1_key'
ENCRYPTION BY PASSWORD = 'oldpassword'
Backed up the SSISDB database .
Copied the key and the back up file to server2
On server2 I did the following
Restored the SSISDB
Restored the master key as follows
USE SSISDB
RESTORE MASTER KEY FROM FILE = 'V:\MSSQL\Keys\SQLSVER1_key'
DECRYPTION BY PASSWORD = 'oldpassword'
ENCRYPTION BY PASSWORD = 'newpassword'
But ran into an issue where it threw the error -
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.
I ran the script again with the FORCE option
USE SSISDB
RESTORE MASTER KEY FROM FILE = 'V:\MSSQL\Keys\SQLSVER1_key'
DECRYPTION BY PASSWORD = 'oldpassword'
ENCRYPTION BY PASSWORD = 'newpassword'
FORCE
That did not work, still got error
Solution: I had copied the keys to a folder where SQL SERVICE account did not have r/w permissions. Copied the key to the folder where the permission was already setup and rant the script with the FORCE option and it worked!
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.
Friday, April 28, 2017
SSISDB Error: The current master key cannot be decrypted
Labels:
backup key,
restore key,
solved,
SSISDB,
SSISDB master KEY,
stress at work
Subscribe to:
Post Comments (Atom)
ReplyDeleteThanks for sharing Good Information
Sql server DBA Online Course Bangalore