Solved

Running DTS package via stored procedure using xp_cmdshell does not work for some  users, MS SQL Server

Posted on 2008-06-26
7
1,940 Views
Last Modified: 2013-11-30
I have a DTS package that imports a text file into a table.  I have a stored procedure that uses xp_cmdshell to run the DTS package using a dtsrun command line.  My user is the creator of the DTS package and can run the stored procedure successfully.  Other users can run  the stored procedure with no error but the import does not succeed.  These same users can succuessfully run the import by running the same  command line that is passed to xp_cmdshell from a command prompt on their local machine.  I am attaching the code from the stored procedure.
AS

	DECLARE @CmdText varchar(500)
 

	BEGIN

		DELETE FROM dbo.tblHPNRosterImport
 

	END
 
 

	BEGIN

		SET  @CmdText = 'DTSRun /S "(local)" /N "ImportStateRosterData" /G "{10F3789C-9F95-4C40-9F8A-C91ADB256313}" /W "0" /E' 
 

		EXEC master.dbo.xp_cmdshell  @CmdText

	END
 

	RETURN

Open in new window

0
Comment
Question by:dwoliver
  • 3
  • 3
7 Comments
 
LVL 13

Expert Comment

by:rickchild
ID: 21877619
Been a while since I used SQL2000, but aren't you able to give permissions to a stored procedure in SQL2000, rather than just using the permissions of a user?
0
 

Author Comment

by:dwoliver
ID: 21877943
I am not aware of a way to give permissions to a stored procedure.  The users in question have permission to run the stored procedure that appears in my question and they have been given permission to run xp_cmdshell.   Since they can run the DTS package from their own command line, I don't think that the DTS package is the problem.  Also, the users in question can  successfully complete the import if they are in the sysadmin role, which is obviously not a solution.  Any suggestions about how to test this situation, get some error information while running in the users context.
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21879043
My first step would be to check that the users do indeed have permissions to run xp_cmdshell, particularly as making the users sysadmin fixes the issue.
Just make it run something simple, like copying a file, as a test, and I think it will probably fail if you read the below.  You probably need to set up the proxy account.

Is your server running Win98, or Windows XP/2000/Server?

By default, only the sysadmin can execute xp_cmdshell.
I would assume that the creator of the SP is probably a sysadmin.

- When xp_cmdshell is run by a sysadmine, xp_cmdshell will be executed under the security context in which the SQL Server service is running.

- When the user is not sysadmin (and server running Windows NT/2000/Server, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account.
If the proxy account is not set up, xp_cmdshell will fail.

On Windows 98, xp_cmdshell is executed as the user.


Anyway, please test a simple xp_cmdshell and we can rule this out one way or the other
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:dwoliver
ID: 21883757
I followed your suggestions and tested xp_cmdshell.  It did fail for non sysadmin users.  So I set the sqlagent_proxy_account to an account with sysadmin privileges.  But that was not sufficient to solve the problem.  I discovered that you have to enable Non-SysAdmin job step proxy account, which I did via Enterpirse Manager.  After that non sysadmin users are able to execute xp_cmdshell and my DTS package import can be executed by nonsysdmin users that have execute permission on xp_cmdshell.

Two followup questions.  From  a security point of view, is it best to have a distinct account to be the sqlagent_proxy_account, or is it OK to use an existing administrator account?  
0
 
LVL 13

Accepted Solution

by:
rickchild earned 250 total points
ID: 21884760
I see, yes you had to allow SQLAgentCmdExec shell actions in job steps.  OK well good that its working.

It should be fine to use existing account as the proxy, as it's going to have to be a sysadmin anyway for the xp_cmdshell.

I think the most important security consideration is that you ideally want to deny the users permissions to the xp_cmdshell directly as this can be very powerful.  Because those users can now exec any xp_cmdshell on the server.

You may be able to grant xp_cmdshell permission to the Stored Procedure (Or the owner of ther stored procedure), but I think that would be another question for someone else as I'm not sure on that side of it.
0
 

Author Closing Comment

by:dwoliver
ID: 31471103
Thanks for your guidance!
0
 

Expert Comment

by:rmartin15
ID: 22105512
Would it make sense to put this process in a stored_proc and give execute permissions to those users for those procs, rather than giving them xp_cmdshell permissions?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

757 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

19 Experts available now in Live!

Get 1:1 Help Now