Solved

I just added a proxy to a Agent Job and got the following error.

Posted on 2010-11-12
23
632 Views
Last Modified: 2012-05-10
I just added a proxy to a Agent Job and got the following error.

 Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy

Does anybody know fix?

thanks
0
Comment
Question by:Mr_Shaw
  • 12
  • 10
23 Comments
 
LVL 6

Expert Comment

by:subhashpunia
Comment Utility
In which group you created the proxy and what type of command youa re using in Agent job?
Do the credential (used for proxy) have the permission for the resource used by job?
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Have you granted the relevant subsystem permissions to the proxy user. Ie if you are runnning an SSIS package then you will have to grant the user access to the SSIS subsystem.
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
The order in which you should set up a proxy is to add the user to Security > Credentials and then under "SQL Server Agent" > Proxies (Right Click) New Proxy and assign the subsystem permissions.
0
 

Author Comment

by:Mr_Shaw
Comment Utility
Ok past that error now... the new error is

Unable to start execution of step 3 (reason: Could not get proxy data for proxy_id = 41).  The step failed.

This is tricky...
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Have you entered DOMAIN\USER in the credential?
0
 

Author Comment

by:Mr_Shaw
Comment Utility
No I am using a sql account.
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
And what subsystem are you accessing? T-SQL?
0
 

Author Comment

by:Mr_Shaw
Comment Utility
i am not sure what subsystem I am using....

I am using SQL Server 2005.
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
In the drop down in the job you have created you will see as per the screen shot. This is the subsystem. SUBSYSTEM
0
 

Author Comment

by:Mr_Shaw
Comment Utility
Oh Operation System (CmdExec)
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Is your SQL Service running under "Local System" or is a user specified?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Mr_Shaw
Comment Utility
I also tried it using SSIS
0
 

Author Comment

by:Mr_Shaw
Comment Utility
>>Is your SQL Service running under "Local System" or is a user specified?

Everything is under local system.

0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
What command are you running?
0
 

Author Comment

by:Mr_Shaw
Comment Utility
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /SQL "\BReports\Invoicing" /SERVER AD1/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

This command work fine if i run the Job under the SA account.

When I use the proxy it does not work.

I have included sysadmin and SQLAgentUserRole in the principals of the proxy.
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Sorry thinking about that is the SQL Server agent running under local system?
0
 

Author Comment

by:Mr_Shaw
Comment Utility
Yes the agent is also running under local system.
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Interesting that it worked with SA... I take it you dont want to use a windows account?
0
 

Author Comment

by:Mr_Shaw
Comment Utility
I don't know which windows account to use... unless I ask for a new one to be created.
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
Comment Utility
Probably a good idea to get a Jew one created with the specific filesystem permissions that are needed.
0
 

Author Comment

by:Mr_Shaw
Comment Utility
thank you so much for helping me through this issue.

A new domain account was created which I used in the proxy.

It all works now.
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
No probs.
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Just saw the "Jew one" bit in my reply. Sorry was supposed to be "New one". Damn iPhone autocorrect.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now