SQL Server Performance Forum – Threads Archive

DTS with xp_cmdshell

Hi there, Am trying to call a DTS package using xp_cmdshell.
I have set up a new user with execute access on xp_cmdshell. This does not seem to work – seems to be a problem with the xp_cmdshell
– I suspect security related – since I cannot use this s/procedure at all.
However, running as "sa" works fine.
This also does not work running under SQL server agent either…! The error message I get is:
"Msg 50001, Level1, State 50001
xpsql.cpp Error from GetProxyAccount on line 499" Any ideas / suggestions or alternatives?
(am trying to call a DTS as part of a stored procedure) Thanks in advance!

???? You can’t use xp_cmdshell at all, not even to do a directory command for example? EXEC xp_cmdshell ‘dir c:’ If not, you need to talk to your DBA and get the rights needed, although unless it’s for SQL Server maintenance or a batch import, this is normally best done at the app layer. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Did you enable a proxy account? You need to enable an account manually in the SQL Server Agent properties dialog box for Enterprise Manager so that SQL Server knows which Win2K account to use when a non-sa runs xp_cmdshell. When an sa executes xp_cmdshell, the extended stored procedure runs as SQL Server’s service account. http://www.databasejournal.com/features/mssql/article.php/1580041 – article about XP_CMDSHELL after SP3a issues for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Good alternative is to have the stored procedure add a row to a queuing table that describes the DTS job that should be run. Then have a job running under SQL Server Agent that scans this queueing table and runs DTS jobs accordingly. This avoids many people simultaneously running DTS packages and should improve scalability of your application. Dave Hilditch.

I think the problem here is privileges running under SQLAgent account and proxy account. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

xp_cmdshell is applied for users with sysAdmin (System Administrator) rights and/or Windows domain users (for Windows authentication). Note that this extended store procedure is not applied to sa only. Avoid using xp_cmdshell in production server. Most of the time, this extended procedure will be hardened to secure the server from the attacks that grant access into the SQL Server. You can try to use sp_OA object to replace it. For the existing program (in production), you can try to execute the program through another objects with owner that contain sysAdmin access rights, such as using SQL Jobs to execute it (set the user of the job to sa or any other users with sysAdmin rights). Hope this information can help you.
HELP Wanted!

]]>