Post reply

SQLAgent proxy using xp_cmdShell

  • 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://qa.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

  • May 1, 2006 at 1:53 pm

    #635204

    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

  • May 3, 2006 at 1:47 am

    #635506

    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

     

    --

  • May 3, 2006 at 4:04 pm

    #635808

    This is a good solution. Good job!

    Regards,Yelena Varsha

  • April 5, 2007 at 7:28 am

    #698782

    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

  • April 10, 2007 at 3:21 am

    #699469

    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

     

    --

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

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