Showing posts with label backup key. Show all posts
Showing posts with label backup key. Show all posts

Friday, April 28, 2017

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!
 
 

Tuesday, January 10, 2017

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