SQL 2005 cluster job SSIS The process could not be created for step 1 of job (reason: 1314). The step failed.

Question

SQL 2005 cluster job SSIS The process could not be created for step 1 of job (reason: 1314). The step failed.

egpotus

SSCommitted

Points: 1654
More actions
April 22, 2011 at 8:34 am

#235208

Hi,
I cannot run a SSIS package from a job in SQL server 2005 cluster using a proxy
My SQl server service is running under DOMAIN\SQLSERVICE and sql server agent is running under DOMAIN\SQLAGENT account
I was expecting to give my proxy account the following permissions but SQLServer2005MSSQLUser$<server>$<instance> & SQLServer2005DTSLUser$<server>$<instance> do not exist on a cluster....
1) The proxy account have the "Act as part of the operating system" privilege.
2) The SQL Server Account is in the group SQLServer2005MSSQLUser$<server>$<instance>
3) The Proxy account is in the group SQLServer2005DTSLUser$<server>$<instance>
the script I use to create my proxy is :
--Drop the credential if it is already existing
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'SQLAdminCredentialToRunSSIS')
BEGIN
DROP CREDENTIAL [SQLAdminCredentialToRunSSIS]
END
GO
-- Create a credential containing the domain account PowerDomain\SQLAdmin and its password
CREATE CREDENTIAL SQLAdminCredentialToRunSSIS WITH IDENTITY = N'DOMAIN\SQLAGENT', SECRET = N'*****'
GO
--Script #2 - Creating a proxy account
USE msdb
GO
--Drop the proxy if it is already existing
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'SSISProxyForSQLAdmin')
BEGIN
EXEC dbo.sp_delete_proxy @proxy_name = N'SSISProxyForSQLAdmin'
END
GO
--Create a proxy and use the same credential as created above
EXEC msdb.dbo.sp_add_proxy
@proxy_name = N'SSISProxyForSQLAdmin',
@credential_name=N'SQLAdminCredentialToRunSSIS',
@enabled=1
GO
--To enable or disable you can use this command
EXEC msdb.dbo.sp_update_proxy
@proxy_name = N'SSISProxyForSQLAdmin',
@enabled = 1 --@enabled = 0
GO
--Grant created proxy to SQL Agent subsystem
--You can grant created proxy to as many as available subsystems
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@proxy_name=N'SSISProxyForSQLAdmin',
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image
GO
--View all the proxies granted to all the subsystems
EXEC dbo.sp_enum_proxy_for_subsystem
--Script #4 - Granting proxy access to security principals
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy
EXEC msdb.dbo.sp_grant_login_to_proxy
@proxy_name=N'SSISProxyForSQLAdmin'
,@login_name=N'SQLAdmin'
--,@fixed_server_role=N'<fixed_server_role name>'
--,@msdb_role=N'<msdb_role name>'
GO
--View logins provided access to proxies
EXEC dbo.sp_enum_login_for_proxy
GO
but still i have the following error
what am I missing ?
Message
Executed as user: MPADGLOBAL\EUA_SVC_SQLCMD. The process could not be created for step 1 of job 0xC4ABEEDC631DA3428D13EF281CEF6D2B (reason: 1314). The step failed.
Kindest Regards,
egpotus DBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

egpotus SSCommitted Points: 1654 More actions · Answer 1

Now it is working, I needed to add my AD account used by SQL Agent in Replace a process-level token , user Rights Assignment

Kindest Regards,

egpotus DBA

indianstranger.100 Valued Member Points: 61 More actions · Answer 2

Hi,

I too got this error now. could you please elaborate more on the resolution of this issue. Its very urgent. kindly consider it..

Thanks

Venu

egpotus SSCommitted Points: 1654 More actions · Answer 3

hi,

you need to remotely connect to your SQL server, run "Local Security Policy" and add you SQL Agent Account to

user Rights Assignment in "Replace a process-level token"

hope this is more clear

Kindest Regards,

egpotus DBA