Problem running sql job with a proxy account

Last Post 16 Mar 2011 02:14 PM by trans53. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
14 Mar 2011 08:16 PM

Hi guys,

i have this problem where i am completly stuck.

i need to create a proxy account to run a powershell and it seems like all created but it fails with the following message and i can't find an answer.

Executed as user: domain\sqldbpurge. The process could not be created for step 1 of job 0xA344D33E0C443347ABD25298474E653E (reason: A required privilege is not held by the client).  The step failed.

At this point i have no idea what i am missing. Is this error from an operating system or this problem is coming from sql?

These are the steps i did:

1) Created domain\sqldbpurge account and added following permissions:

Log on as a service
Log on as a batch job
Replace a process-level token
Bypass traverse checking
Adjust memory quotas for a process

2) Creating a credential to be used by PowerShell proxy

USE master
GO

--Drop the credential if it is already existing

IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'PowerShellProxyCredentials')
BEGIN
 DROP CREDENTIAL [PowerShellProxyCredentials]
END
GO

CREATE CREDENTIAL [PowerShellProxyCredentials]
 WITH IDENTITY = N'domain\sqldbpurge',
SECRET = N'password'
GO

USE [msdb]
GO

-- Drop the proxy if it's already existing

IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'PowerShellProxy')
BEGIN
  EXEC dbo.sp_delete_proxy @proxy_name = N'PowerShellProxy'
END
GO

-- Add the proxy

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'PowerShellProxy',
       @credential_name=N'PowerShellProxyCredentials',
       @enabled=1,
       @description=N'PowerShellProxy - Access to the PowerShell'
GO

-- Grant proxy account to SQL Server Agent Sub-systems (In this case subsystem_id = 12 is PowerShell).
-- You can find the subsystem_id by running this (use msdb go EXEC sp_enum_sqlagent_subsystems)


EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'PowerShellProxy', @subsystem_id=12
GO

--View all the proxies granted to all the subsystems
EXEC dbo.sp_enum_proxy_for_subsystem

-- Grant proxy access to security prin***ls

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'PowerShellProxy', @login_name=N'domain\sqldbpurge'
GO

-- View all the logins/roles who have access to the proxies

exec sp_enum_login_for_proxy

rm
New Member
New Member

--
15 Mar 2011 04:51 AM
Do you run powershell script in sql job? Who's job owner? Did you specify run as powershellproxy in job step?
rm
New Member
New Member

--
15 Mar 2011 04:52 AM
Does powershellproxy have proper sql permission?
trans53
New Member
New Member

--
15 Mar 2011 05:33 AM

rm, yes, i am trying to run powershell step as a sql job and the owner of the job is domain\sqldbpurge account which is non-sysadmin and has only execute permission to one stored procedure.

This is requirement to run this sql job as a non sysadmin account with a minimum permissions.

This is what i don't know, what sql permissions does PowerShellProxy need?

i am sure  missing something.

rm
New Member
New Member

--
15 Mar 2011 07:59 AM
Ok, job owner is powershell proxy itself. Did you specify run as powershellproxy in job step? What kind of sql process involved in powershell script? Proxy account need proper sql permission to do those sql related processes.
trans53
New Member
New Member

--
15 Mar 2011 08:33 AM
Yes, it runs as PowerShellProxy  and execute simple Write-Output 'Powershell working' statement.
For now nothing serious, this statment is there just to see if it's working or not. But it still failing.
rm
New Member
New Member

--
15 Mar 2011 09:24 AM
Can you logon windows as domain\sqldbpurge to run same script in dos prompt?
trans53
New Member
New Member

--
15 Mar 2011 02:42 PM
i think i am missing

1.  Act as part of the Operating System
2.  Increase Quotas
3.  Log on as a Service
4.  Replace a process level token

for SQL Agent service account.

i will this tomorrow and see if it works

trans53
New Member
New Member

--
16 Mar 2011 02:14 PM
By adding these permissions to the SQL Agent service account fixed the problem.
You are not authorized to post a reply.

Acceptable Use Policy