August 8, 2007 at 5:31 am
I've created a DTS job that runs perfectly fine when I run it manually. I'm moving data from one server to a table on another server. However when I schedule it to run I receive error messages. The first time I researched the error and it said to create a proxy account and credentials. I have done all of that and now I'm receiving another error. Can someone please let me know what this error means?
Message
Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 2). The step failed.
September 4, 2007 at 12:57 pm
I'm getting the same error as well. Any idea on how to make this work?
September 5, 2007 at 4:10 am
What type of proxy did you create? SQL 2005 has lots of different proxies and you need to create the right one. Also, you only need to create proxies if the account running the process is NOT a sysadmin.
If you are running a SQL 2000-style DTS package under the CmdExec subsystem in SQL Agent, you need to set up a proxy for CmdExec.
In order to provide a container for all authorities needed by a non-sysadmin job, I recommend that each job owner account that is not a sysadmin is set up as its own proxy. This allows you to have both SQL permissions and file permissions owned by the same account.
1) You need to set up a credential for the job owner account (see BOL for details). I suggest you use the job owner account name as the credential name.
2) You need to define a CmdExec proxy in SQL Agent, using the credential you just created. Again, use the job owner account name as the proxy name.
3) You need to set up rights for the proxy. Set the Principal Type to SQL Login and map it to the job owner account.
4) You now grant the job owner account the ability to manage jobs. This is also needed to allow the proxy account to work. Grant access to the SQLAgentUserRole in msdb to the login. See BOL to see if you wantto grant other rights.
5) You can now associate the proxy with the required job steps. Edit the job step and 'Run As' to your proxy.
Everything should now run OK. If you want to add other Agent subsystems to the proxy (e.g. SSIS) then do so. However, be aware that any code in your job that runs xp_cmdshell will run using the authority of the global xp_cmdshell proxy - see BOL on details of how to set this up. All other SQL code and file accesses will run using the authority of the job owner account.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply