SQL 2005 Agent job step calling ActiveX subsystem fails
I’m having problems configuring an SQL 2005 agent job step, with the new Credential and proxy setup stuff.I followed the set up as per BOL and “What’s new in SQL Agent” (I think) but it’s not working.
The job can be called correctly and does start and execute but the ActiveX job step fails with:
“Error creating security descriptor for shared objects (reason: No mapping between account names and security IDs was done).”
I tried adding user permissions to the agent account per Q283811 (Replace a Process Level Token, etc.)but it still didn’t work.
It DOES work if I set up the SQL Agent Service to run under a local admin account (PSystem referenced below) instead of Local System though so it definitely seems to be a permissions issue.
Since the "What's new" technet article says “If you upgrade to SQL Server 2005, the existing proxy account is created and all subsystems are assigned to this single proxy account. This enables your SQL Server Agent jobs to continue to function as they did in SQL Server 2000 after the upgrade.”, I even built a whole new system at SQL 2000 and verified that the agent job step functions fine, upgraded SQL to 2005 and it still fails with the same error.
Here’s the generated script of the setup steps I’m doing:
CREATE CREDENTIAL [PSystem] WITH IDENTITY = N'DOLPHIN\PSystem', SECRET = N'...'
GO
msdb..sp_add_proxy @proxy_name = 'PProxy', @credential_name = 'PSystem'
GO
CREATE USER [DOLPHIN\PUsers] FOR LOGIN [DOLPHIN\PUsers]
GO
EXEC msdb..sp_addrolemember N'SQLAgentUserRole', N'DOLPHIN\PUsers'
GO
msdb..sp_grant_login_to_proxy @login_name = 'DOLPHIN\PUsers', @proxy_name = 'PProxy'
GO
msdb..sp_grant_proxy_to_subsystem @proxy_name = 'PProxy', @subsystem_name = 'ActiveScripting'
GO
msdb..sp_grant_proxy_to_subsystem @proxy_name = 'PProxy', @subsystem_name = 'CmdExec'
GO
The PSystem account is a local windows admin that I need the job step to run under for network permissions, the PUsers group is a general windows users group for access control of the job. The database access and Transact-SQL job steps run fine for that setup, it's just the ActiveX and CmdExec ones that fail.
I can’t find any info on that error message and can’t figure out what setup step I’m missing or what I’m doing wrong.
Thanks!
