April 27, 2017 at 3:23 pm
I had to move the SSISDB database f rom server1 to Server2
Took a backup of the key
USE
SSISDB
BACKUP MASTER KEY TO FILE = 'C:\MSSQL\Keys\SQLSVER1_key'
ENCRYPTION BY PASSWORD = 'oldpassword'
Copied the key to the new server.
Took a backup of the SSISDB on server1 and moved the backup files to the new server.
Restored the SSISDB on Server2 from the backup copy from the server1
RESTORE
DATABASE ssisdb
FROM DISK = 'c:\MSSQL\Backup\\SSISDB_Server1.bak'
WITH MOVE N'data' TO N'E:\MSSQL\Data\SSISDB.mdf',
MOVE N'log' TO N'L:\MSSQL\log\SSISDB.ldf',
REPLACE
I restored the key in server2
USE SSISDB
RESTORE MASTER KEY FROM FILE = 'V:\MSSQL\Keys\SQLSVER1_key'
DECRYPTION BY PASSWORD = 'oldpassword'
ENCRYPTION BY PASSWORD = 'newpassword'
I get the following 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.
April 28, 2017 at 1:46 am
This has always worked for me.
http://blogs.msdn.com/b/mattm/archive/2012/03/23/ssis-catalog-backup-and-restore.aspx
USE SSISDB
BACKUP MASTER KEY TO FILE = 'c:\DMK\SSISDB\key'
ENCRYPTION BY PASSWORD = 'OLDKEY'
-----------------------------------------------------
USE SSISDB
RESTORE MASTER KEY FROM FILE = 'D:\MasterKeyBackup\SSIS-Server1234\key'
DECRYPTION BY PASSWORD = 'OLDKEY'
ENCRYPTION BY PASSWORD = 'NewKEY'
FORCE
---------------------------------------------------------
USE master
GO
ALTER DATABASE SSISDB SET TRUSTWORTHY ON
Make sure is_trustworthy is set to ON in the restored database otherwise packages will not execute.
April 28, 2017 at 7:45 am
The issue is resolved. Phew!
I had copied the master key to a random folder and realized that the SQL Server service account did not have permission on this folder. Moved the key to the folder where the permission was already set up and ran the script ( change to the new path) the , it worked!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply