xp_sqlagent_proxy_account

[Dianne Watson]

Hey,

Can someone please let me know how I set up a proxy account for an
application to call a stored procedure using the 'xp_cmdshell' to call
a DTS package.

I am getting this error message:

Microsoft OLE DB Provider for SQL Server error '80004005'

xpsql.c: Error 1314 from CreateProcessAsUser on line 500

The stored procedure xp_sqlagent_proxy_account is not showing up in the master
database????

Thanks for your help.

Dianne

[Juergen Leis]

Perhaps you did not allow the proxy account (SQLAgentCmdExec) during setup?
You Can Set it up via SQLEM:
SQL Server Agent->Properties->Job System[Non-Sysadmin ...]

An alternative way (especially if the package can run asynchroneous):
Create a Logged User defined Message (50001 or higher)
Createn a Job that runs the DTS Package
( and probably enters a completion DateTime stamp
etc. into a synchronazition Table
if ypou need some kind of synchronization
)
Define an Alert on this Error which runs above Job as a Response

Create a stored procedure which raises that error via RAISERRROR
(it should be CREATED by an sa)
( and probably waits for the completion DateTime stamp
to materialize in the synchronization table
after the RAISERROR
WHILE NOT EXISTS( SELECT ... FROM SyncTable WHERE .. ) WAITFOR DELAY ..
)

EXECUTE this procedure from the client

P.S.: From SQL Books Online

SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell and own SQL Server Agent jobs. The administrators can assign appropriate security permissions to the proxy account to control the ability of these jobs to access resources in the network.

When a SQL Server user executes a command prompt command using xp_cmdshell, the command must execute in the security context of a Windows account. If the SQL Server user is a member of the sysadmin fixed server role, SQL Server executes the command prompt command using the Windows account under which the SQL Server service is running. If the SQL Server user executing xp_cmdshell is not a member of the sysadmin fixed server role, SQL Server executes the command using the Windows account specified as the SQL Server Agent proxy account. If no SQL Server Agent proxy account has been set, the user gets an error. SQL Server Agent jobs also must execute in the security context of a Windows account. If the job is owned by a member of the sysadmin fixed server role, the job executes using the Windows account under which the SQL Server service is running. If the job owner is not in sysadmin, the job executes using the SQL Server Agent proxy account, and an error is raised if no proxy account has been set.

xp_sqlagent_proxy_account sets or retrieves the proxy account for the instance on which it is executed. The SQL Server service for that instance must be running under a Windows administrator account to read or set the SQL Server Agent proxy account.


------------
Dianne Watson at 11/15/2001 1:39:32 PM

Hey,

Can someone please let me know how I set up a proxy account for an
application to call a stored procedure using the 'xp_cmdshell' to call
a DTS package.

I am getting this error message:

Microsoft OLE DB Provider for SQL Server error '80004005'

xpsql.c: Error 1314 from CreateProcessAsUser on line 500

The stored procedure xp_sqlagent_proxy_account is not showing up in the master
database????

Thanks for your help.

Dianne