February 2, 2017 at 9:07 pm
Hi,
at our customer side, database is SQL 2012 clustered having 2 physical nodes. to achieve high availability with SSIS, SSIS is installed standalone on both the nodes and service runs on both irrespective of which node is active.
When failover of database node happens, the SSIS packages which are scheduled as SQL jobs, failed with below error
"Executed as user: GLOBAL\SVC-RUGMESSQLAgent. Microsoft(R) SQL Server Execute Package Utility Version 11.0.2100.60 for32-bit Copyright (C) Microsoft Corporation. All rightsreserved. Started: 2:10:01 PM Failed to executeIS server package because of error 0x80131904. Server: Z2T3RUSPVLD06, Packagepath: \SSISDB\QPLUS_4703\QuantumPlusToApriso2016\QuantumPlus.dtsx, Environmentreference Id: NULL. Description: Please create a master key in thedatabase or open the master key in the session before performing thisoperation. Source: .Net SqlClient Data Provider Started: 2:10:01 PM Finished: 2:10:01 PM Elapsed: 0.266 seconds. The package execution failed. The step failed."
Once node is switched back to original node, they start to run fine. I have read other sites and they suggest to set master key.
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘thepassword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Would it solve the problem? Is this one time activity? I don't know the password, so what to set. Or there is someother solution
One more point SSIS was installed after SQL installation.As we cannot add SSIS to an existing SQL Server cluster, to run setup and skip the installation rules to install SQL Server Reporting Services in an existing clustered instance. The following command was run at the Windows command prompt to start SQL Server setup on the active node.
| Setup.exe /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck /Action=Install |
and select SSIS and install on node
We are struggling to achieve this and right now high availability setup is stalled. Any suggestion would really be appreciated. Thanks !
Regards
Tripti
February 3, 2017 at 9:08 am
mandal.tripti - Thursday, February 2, 2017 9:07 PMHi,
at our customer side, database is SQL 2012 clustered having 2 physical nodes. to achieve high availability with SSIS, SSIS is installed standalone on both the nodes and service runs on both irrespective of which node is active.When failover of database node happens, the SSIS packages which are scheduled as SQL jobs, failed with below error
"Executed as user: GLOBAL\SVC-RUGMESSQLAgent. Microsoft(R) SQL Server Execute Package Utility Version 11.0.2100.60 for32-bit Copyright (C) Microsoft Corporation. All rightsreserved. Started: 2:10:01 PM Failed to executeIS server package because of error 0x80131904. Server: Z2T3RUSPVLD06, Packagepath: \SSISDB\QPLUS_4703\QuantumPlusToApriso2016\QuantumPlus.dtsx, Environmentreference Id: NULL. Description: Please create a master key in thedatabase or open the master key in the session before performing thisoperation. Source: .Net SqlClient Data Provider Started: 2:10:01 PM Finished: 2:10:01 PM Elapsed: 0.266 seconds. The package execution failed. The step failed."Once node is switched back to original node, they start to run fine. I have read other sites and they suggest to set master key.
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘thepassword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYWould it solve the problem? Is this one time activity? I don't know the password, so what to set. Or there is someother solution
Yes it's one time and it seems fix the issue for most people. I saw once or twice where people would hit another subsequent issue but that's just because the process got farther and there was another issue to resolve.
The password is the password used when you created SSISDB. If you don't have that password, you might be able to get it from the job on the node where the package works.
On the node where it works, you can try to look at the command for the job step and see if there is a decrypt switch followed by a password.
SELECT s.command
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
WHERE j.name = 'SSISJobName'
Other than that, not sure. I have seen some posts where it's been suggested to regenerate a new key but never saw anyone say that approached worked. So it could just mess you up more. If you have somewhere safe to try it, you would just do:
ALTER MASTER KEY REGENERATE WITH 'YourNewPassword'
But as I said, I have no idea if it works so it could make things worse. You should try it in a non-production environment if you need to try that. Ideally a VM that you can blow away but we usually don't have our ideals when we need them. But you could try it on a spare PC if one of those are available.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply