August 10, 2017 at 9:33 am
We have fail-over cluster for one of our production environment. It has two node with SSISDB configured. Someone has configured the SSISDB with master key on one node but he doesn't remember password and other require details. We have all important packages configured through SSISDB. Now, whenever fail-over happens, all the jobs are running through SSISDB is failing on second node with following error:
Failed to execute IS server package because of error 0x80131904. Server: Clustered1, Package path: \SSISDB\Import.dtsx, Environment reference Id: NULL. Description: Please create a master key in the database or open the master key in the session before performing this operation.
Any suggestions regarding to this matter would be greatly appreciated.
August 10, 2017 at 10:22 am
You could try an alter master key regenerate - not sure if it will work. It takes awhile, can be resource intensive so you would want to do it during off hours, maintenance window.
ALTER MASTER KEY (Transact-SQL)
Sue
August 10, 2017 at 10:42 am
The problem is that for security, the master key of any database is encrypted by the server master key. You can synchronize the Server Master Keys by backing up the server master key of the server where the whole thing works, and restore it on the other SQL Server. You may want to back up the server master key on the standby server, as you may have had things encrypted using that key as well. CAUTION: If you lose the service master key, you lose everything that it has encrypted.
See here for the commands:
Commands
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-service-master-key-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-service-master-key-transact-sql
Encrytion hierarchy:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encryption-hierarchy
August 11, 2017 at 2:36 am
EasyBoy - Thursday, August 10, 2017 9:33 AMWe have fail-over cluster for one of our production environment. It has two node with SSISDB configured. Someone has configured the SSISDB with master key on one node but he doesn't remember password and other require details. We have all important packages configured through SSISDB. Now, whenever fail-over happens, all the jobs are running through SSISDB is failing on second node with following error:Failed to execute IS server package because of error 0x80131904. Server: Clustered1, Package path: \SSISDB\Import.dtsx, Environment reference Id: NULL. Description: Please create a master key in the database or open the master key in the session before performing this operation.
Any suggestions regarding to this matter would be greatly appreciated.
Firstly, do not restore SMKs across instances.
Are you referring to the encryption key password set when initially enabling the SSISDB catalog?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 11, 2017 at 3:16 am
Can you check the is_trustworthy_on setting on the PRIMARY and then on the secondary. This needs to be set to ON. It is off by default on the secondary from memory. It can only be set after failover. Not very well documented by MS and doesn't exist in their failover steps.
select Name, is_trustworthy_on from sys.databases
where name ='SSISDB'
August 11, 2017 at 7:50 am
Perry Whittle - Friday, August 11, 2017 2:36 AMEasyBoy - Thursday, August 10, 2017 9:33 AMWe have fail-over cluster for one of our production environment. It has two node with SSISDB configured. Someone has configured the SSISDB with master key on one node but he doesn't remember password and other require details. We have all important packages configured through SSISDB. Now, whenever fail-over happens, all the jobs are running through SSISDB is failing on second node with following error:Failed to execute IS server package because of error 0x80131904. Server: Clustered1, Package path: \SSISDB\Import.dtsx, Environment reference Id: NULL. Description: Please create a master key in the database or open the master key in the session before performing this operation.
Any suggestions regarding to this matter would be greatly appreciated.
Firstly, do not restore SMKs across instances.
Are you referring to the encryption key password set when initially enabling the SSISDB catalog?
Yes. That's what i am referring too.
August 11, 2017 at 7:52 am
Talib123 - Friday, August 11, 2017 3:16 AMCan you check the is_trustworthy_on setting on the PRIMARY and then on the secondary. This needs to be set to ON. It is off by default on the secondary from memory. It can only be set after failover. Not very well documented by MS and doesn't exist in their failover steps.select Name, is_trustworthy_on from sys.databases
where name ='SSISDB'
Its is off on both server.
August 11, 2017 at 9:50 am
EasyBoy - Friday, August 11, 2017 7:50 AMPerry Whittle - Friday, August 11, 2017 2:36 AMEasyBoy - Thursday, August 10, 2017 9:33 AMWe have fail-over cluster for one of our production environment. It has two node with SSISDB configured. Someone has configured the SSISDB with master key on one node but he doesn't remember password and other require details. We have all important packages configured through SSISDB. Now, whenever fail-over happens, all the jobs are running through SSISDB is failing on second node with following error:Failed to execute IS server package because of error 0x80131904. Server: Clustered1, Package path: \SSISDB\Import.dtsx, Environment reference Id: NULL. Description: Please create a master key in the database or open the master key in the session before performing this operation.
Any suggestions regarding to this matter would be greatly appreciated.
Firstly, do not restore SMKs across instances.
Are you referring to the encryption key password set when initially enabling the SSISDB catalog?Yes. That's what i am referring too.
OK, to be clear is this a failover cluster instance or alwayson availability group?
The DMK and the certificate are created in the SSISDB, so on a clustered instance there shouldnt be an issue, since if the instance fails over to a partner node, the instance level data is available.
There is no primary and secondary concept as such, each node receives the instance in the same state as any partner.
If you cannot remember the DMK (or encryption password), use the following, but you will need to re enter any sensitive data as this will be lost if the original master key is unavailable. However, you should be able to regenerate without the force option, or at least try this first
ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 11, 2017 at 11:04 am
Perry Whittle - Friday, August 11, 2017 9:50 AMEasyBoy - Friday, August 11, 2017 7:50 AMPerry Whittle - Friday, August 11, 2017 2:36 AMEasyBoy - Thursday, August 10, 2017 9:33 AMWe have fail-over cluster for one of our production environment. It has two node with SSISDB configured. Someone has configured the SSISDB with master key on one node but he doesn't remember password and other require details. We have all important packages configured through SSISDB. Now, whenever fail-over happens, all the jobs are running through SSISDB is failing on second node with following error:Failed to execute IS server package because of error 0x80131904. Server: Clustered1, Package path: \SSISDB\Import.dtsx, Environment reference Id: NULL. Description: Please create a master key in the database or open the master key in the session before performing this operation.
Any suggestions regarding to this matter would be greatly appreciated.
Firstly, do not restore SMKs across instances.
Are you referring to the encryption key password set when initially enabling the SSISDB catalog?Yes. That's what i am referring too.
OK, to be clear is this a failover cluster instance or alwayson availability group?
The DMK and the certificate are created in the SSISDB, so on a clustered instance there shouldnt be an issue, since if the instance fails over to a partner node, the instance level data is available.
There is no primary and secondary concept as such, each node receives the instance in the same state as any partner.If you cannot remember the DMK (or encryption password), use the following
ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'
This is two node availability group.
Yes. There is no primary and secondary, it was jut for reference purpose only.
So the steps would be, on primary server
1)
Alter Master key
2) Add DMK
use ssisdb
go
create master key encryption by password = '<password>'
3) Do i need to create the same master key and DMK at second node too?
August 15, 2017 at 8:43 am
as its an availability group, you will need to backup the DMK in the primary SSISDB and restore to the secondfary SSISDB
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply