SSISDB Error: The current master key cannot be decrypted

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!   

Keeping the size of the SSISDB to minimum

The  SSISDB database size was growing large in the SQL server 2012 machine.  I had to clean the SSISDB to  bring the size to minimum.

The SSIS catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the SSIS server. When the SSIS catalog is implemented, the SSISDB database is automatically created.
SSIS projects, packages, parameters, environments, and operational history are all stored in the SSISDB database so if you have hundreds of SSIS packages or packages that run every few minutes you could see how the database storing all the historical information would grow exponentially.

Steps I took to reduce the size of SSISDB

Checked that the SQL maintenance job ( created by SSIS Installation) was running, the job name is SSIS Server Maintenance Job

Looking at the first step, SSIS Server Operations Records Maintenance, I notices that it executes a stored procedure named internal.cleanup_server_retention_window.  This was the stored procedure that cleans up history

I could also   see that the stored procedure queries catalog.catalog_properties to find these values.

use SSISDB




GO
  

 

I changed the property value of the property name Retention_window to 10 days from 365 ( I could update to this short time frame as this was a dev server)

update c

set property_value = 100

from catalog.catalog_properties c

where property_name = 'RETENTION_WINDOW'

 

Then I ran the maintenance job again and the database size reduced.

 

I found all this information  in the following link-

 

https://www.mssqltips.com/sqlservertip/3307/managing-the-size-of-the-sql-server-ssis-catalog-database/ 

SSIS error while project deployment from the local machine to the server.

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  

Backup SSISDB

I came across a very good instructions on MSDN for this .

https://blogs.msdn.microsoft.com/mattm/2012/03/23/ssis-catalog-backup-and-restore/

SSIS Catalog – Backup and Restore

 

March 23, 2012By Kaarthik Sivashanmugam

 

 

 

 

Integration Services catalog (SSISDB) uses the encryption mechanism available in SQL Server to protect its sensitive data. So backup and restore of SSIS catalog across machines need some extra steps in addition to the usual backup and restore steps. This post describes all the steps involved in backup and restore of SSIS catalog.

Backup
1. Backup the master key used for encryption in SSISDB database and protect the backup file using a password. This is done using BACKUP MASTER KEY statement.

 USE SSISDB
 BACKUP MASTER KEY TO FILE = 'c:\DMK\SSISDB\key'
 ENCRYPTION BY PASSWORD = 'SS1SC@talogMKBKUP'

 This step is not necessary every time you do backup unless you have lost the file or the password or if you have changed the master key of the SSISDB database.
2. Backup SSISDB database using SQL Server Management Studio or BACKUP DATABASE statement in TSQL.
3. If SSISDB database will be restored in a SQL Server instance that never had SSIS catalog, follow steps 3.1. & 3.2. listed below in additional backup steps section.

Restore
4. If SSISDB database will be restored in a SQL Server instance that never had any SSIS catalog, follow preparatory steps 4.1. & 4.2. listed below in additional restore steps section.
5. Restore SSISDB database using SQL Server Management Studio or RESTORE DATABASE statement in TSQL.
6. If SSISDB is restored in a SQL Server instance that never had any SSIS catalog, follow steps 6.1. thru 6.3. listed below in additional restore steps section.
7. Restore backup of the master key from the source server using the backup file created in step 1 in Backup section above.

 USE SSISDB
 RESTORE MASTER KEY FROM FILE = 'D:\MasterKeyBackup\SSIS-Server1234\key'
 DECRYPTION BY PASSWORD = 'SS1SC@talogMKBKUP'
 ENCRYPTION BY PASSWORD = 'NewC@talogPassw0rd'
 FORCE
 

“SS1SC@talogMKBKUP” is the password used to protect the file containing backup of the master key and “NewC@talogPassw0rd” is the new password to encrypt database master key.
The warning reported when carrying out this step “The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.” can be ignored.
Decrypting all keys using the currently active master key and re-encrypting using the restored master key is a resource-intensive operation should be scheduled when server usage is low.
SSIS catalog should be fully functional in the restored server now.

Additional Backup Steps
Following steps are necessary during backup if you will be restoring SSISDB database to a SQL Server instance where the catalog was never created.
3.1. Generate create script for sp_ssis_startup stored procedure which is created when SSIS catalog is created in a SQL Server. The script is generated using SQL Server Management Studio. Right-click on the stored procedure in master database in SSMS Object Explorer, select “Script Stored Procedure as” -> “CREATE TO” -> “File” options to save the script to a file (for example, sp_ssis_startup.sql).
3.2. Generate create script for Agent job [SSIS Server Maintenance Job] which is created in SQL Server Agent automatically when SSIS catalog is created in a SQL Server. The script is generated using SQL Server Management Studio. Right-click on the Agent job in SSMS Object Explorer, select “Script Job as” -> “CREATE TO” -> “File” options to save the job to a file (for example, ssis_server_maintenance_job.sql).

Additional Restore Steps
Following steps are necessary during restore if you are restoring SSISDB database to a SQL Server instance where the catalog was never created.
4.1. Enable CLR functionality in SQL Server that SSISDB database depends on.

 USE MASTER
 EXEC sp_configure 'clr enabled', 1
 RECONFIGURE

4.2. Create asymmetric key and unsafe assembly loading principal that SSISDB database depends on. The login is used only for granting permission and hence does not have to be mapped to a database user.

 USE MASTER
 CREATE ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey FROM EXECUTABLE FILE = 'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ISServerExec.exe'
 CREATE LOGIN MS_SQLEnableSystemAssemblyLoadingUser FROM ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey
 GRANT UNSAFE ASSEMBLY TO MS_SQLEnableSystemAssemblyLoadingUser

You may need to change the file system path to ISServerExec.exe depending on your install location.

6.1. Create login [##MS_SSISServerCleanupJobLogin##] using CREATE LOGIN TSQL statement. This login is for internal use in SSISDB maintenance.
6.2. Map SSISDB user ##MS_SSISServerCleanupJobUser## to server login ##MS_SSISServerCleanupJobLogin##

 USE SSISDB
 ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]

6.3. Create startup stored procedure and agent job by executing scripts (sp_ssis_startup.sql  & ssis_server_maintenance_job.sql) created in steps 3.1 & 3.2 under additional backup steps section above in the master database in the SQL Server instance in which SSISDB database is restored. You need to update @servername parameter for sp_add_jobserver step in ssis_server_maintenance_job.sql with the name of the new server replacing old server name.