• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1968
  • Last Modified:

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

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
dwoliver
Asked:
dwoliver
  • 3
  • 3
1 Solution
 
rickchildCommented:
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
 
dwoliverAuthor Commented:
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
 
rickchildCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
dwoliverAuthor Commented:
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
 
rickchildCommented:
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
 
dwoliverAuthor Commented:
Thanks for your guidance!
0
 
rmartin15Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now