SQLAgent proxy using xp_cmdShell

Question

Post reply

SQLAgent proxy using xp_cmdShell

DKC

Old Hand

Points: 325
More actions
April 28, 2006 at 3:07 am

#112724

Hi All,
Win2003, sqlserver 2005 sp4.
User cmdService service account for sqlserver.
User cmdProxy is the proxy I want to use for xp_cmdShell.
I've followed suggestions as discussed in the thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=259186&p=1
and successfully setup the cmdProxy as the sql agent proxy using Query Analyser.
However, when non-sysAdmin user userXYZ ( domain user) is running a stub in Query Analyser that uses xp_cmdShell, I get the error:
Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 33
EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
cmdProxy has got exec permission on xp_cmdshell, as well as on xp_sqlagent_proxy_account.
The problem I think is that the when user userXYZ is running the T-SQL stub in Query Analyser, the xp_cmdshell is being executed under the context of userXYZ and NOT in the context of cmdProxy.
To test this, I specifically granted userXYZ exec permission on xp_cmdShell and the command worked - xp_cmdshell ran with userXYZ credentials.
When I removed exec permission on xp_cmdshell for userXYZ, I get this error now:
Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 34
EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
When I connect as cmdProxy and run the same stub, it works fine, but as the userXYZ, it errors.
I then setup a job for userXYZ, and it failed with the error:
The description for Event ID ( 208 ) in Source ( SQLAgent$<***> ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Test, 0xAFCECE11C943944A8C62B3F45D0D5E63, Failed, 2006-04-28 09:28:10, The job failed. Unable to determine if the owner (<DOMAIN>\userXYZ) of job Test has server access (reason: Could not obtain information about Windows NT group/user '<DOMAIN>\userXYZ'. [SQLSTATE 42000] (Error 8198))..
Any ideas?
(I had originally posted this info in the thread above, but apparently it was off-topic?)
Thanks.
Diwakar

--
May 1, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Diwakar,

there are 2 independent issues:

1. You are correct with the first one. The query in a Query Analyzer will execute in the context of the executing user, not in the context of your proxy account. please, see the top of the query window where the name of the query is, it is qualified with the user name.

2. SQL Server or Agent started on the local account may sometimes not be able to query the domain controller for the Windows group membership, so it can not obtain the information about the Windows user. You may want to start Agent on the domain service account and see if your user will be authenticated properly.

Regards,Yelena Varsha

DKC Old Hand Points: 325 More actions · Answer 2

Thank you for your reply.

I have now taken a different approach.

Created an sspCmdShell procedure in the master database. This procedure calls xp_cmdShell with the relevant parameters. I've granted exec to public on this procedure, and it works.

So my non-admin users are now able to run their programs without any issue.

Thanks.

Diwakar

--

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

This is a good solution. Good job!

Regards,Yelena Varsha

SQLIndia Ten Centuries Points: 1073 More actions · Answer 4

Diwakar

I have same problem.....so please could you guide me through or share the approach you taken to create 'sspCmdShell procedure'

Thanks.

sonny

SqlIndia

DKC Old Hand Points: 325 More actions · Answer 5

Originally, I used Master database for my purposes:

use master

go

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'sspcmdShell' AND type = 'P')

DROP PROCEDURE sspcmdShell

GO

CREATE PROCEDURE sspcmdShell

@shellCommand varchar(1000),

@value1 varchar(255),

@value2 varchar(255)

AS

declare @cmd varchar(4000)

BEGIN

select @cmd = @shellCommand + ' parameter1:' + @value1 + ' parameter2:' + @value2

EXEC master..xp_cmdshell @cmd

END

GO

grant exec on sspcmdShell to public

go

With this approach, the users will be running sspCmdShell instead of xp_cmdshell.

I have now taken a completely different approach:

I have sspCmdShell in a different database, NOT master.

sspCmdShell inserts the relevant data in a polling ta ble, and I have a polling routine that reads from the table and processes requests.

I find that this approach gives me more options and it is safer as well.

Regards,

Diwakar

--