xp_cmdShell impersonating as a proxy account user

Posted on 2006-05-22
Last Modified: 2010-08-05

We are starting to use SQL Server 2005 and need to be able to execute commands using the xp_cmdShell stored procedure within SQL. The SQL Documentation states two contraditory statements, see the link below.

1 - "The windows process spawned by xp_cmdShell has the same security rights as the SQL Server service account"
2 - "When it is run as a user that is not a memeber of the sysAdmin fixed server role it will use the #xp_cmdShell_proxy_account# credential"

When I execute the following statement as a user which is not a member of the sysadmin role it still returns "SQLAdmin", which is the user that owns the SQL Server service process.

exec master..xp_cmdshell 'set username'

The credential has been setup to use another user's credential, however it does not seem to impersonate the proxy account credential?

Is there a way, or a setting i might have missed, to make SQL Server impersonate the proxy_account credential when executing commands?

Any help would be appreciated.
Question by:JustinBlandford
    LVL 12

    Expert Comment

    i do not have experience with proxy account, but there is also possibility to put xp_cmdshell into DTS and set DTS's credentials.

    Author Comment

    ill, Hi

    Unfortunately the command that needs to be called within the xp_cmdShell stored procedure needs to be dynamic, so i dont think that a DTS wrapper would be possible?

    xp_cmdShell does work, it just appears to run as an incorrect user, based on the microsoft documentation on the proxy account?

    LVL 23

    Assisted Solution

    by:Racim BOUDJAKDJI
    The best thing to do is to create an Active Directory account on which SQL Services on ALL SQL service run then assign the right rights through GPO.  You will need some help on Active Directory concepts rather than SQL...Hope this helps...
    LVL 30

    Accepted Solution

    What do you see when you run this:

    EXEC master.dbo.xp_sqlagent_proxy_account N'GET'

    If you see no recordset then it means the proxy account has not been set.

    Author Comment

    nmcdermaid, hi

    i tried the stored procedure on the server and it does not exist. The documentation states that the stored procedure has been removed in SQL Server 2005.

    The credential for the xp_cmdShell stored procedure to run processes as seems to be created, and contains the correct information to run as the correct domain user.

    Any other ideas?

    LVL 12

    Assisted Solution

    we're sending command( dynamically created) as parameter to DTS
    LVL 30

    Expert Comment

    OK this is where my knowledge runs out as I haven't used SQL 2005 much.

    Just one thing - are you certain that SET USERNAME will always return the processes user correctly? Isn't that a dos environment variable? There could be something going on behind the scenes there. Maybe the process is 'logged on' as the agent (therefore USERNAME is the agensts user) but then there is some trick within the code to use the proxy user.

    I would suggest that the only way to really check what credential is doing it is if you use filemon (third time I've suggested it this week) from sysinternals I think.

    Try copying a file and catch it with filemon and see what user filemon says it is.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    794 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

    16 Experts available now in Live!

    Get 1:1 Help Now