?
Solved

xp_cmdShell impersonating as a proxy account user

Posted on 2006-05-22
7
Medium Priority
?
2,501 Views
Last Modified: 2010-08-05
Hi,

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.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

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.
0
Comment
Question by:JustinBlandford
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Expert Comment

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

Author Comment

by:JustinBlandford
ID: 16733175
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?

Regards
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 240 total points
ID: 16733937
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...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Accepted Solution

by:
nmcdermaid earned 270 total points
ID: 16738139
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.
0
 

Author Comment

by:JustinBlandford
ID: 16749548
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?

Regards
0
 
LVL 12

Assisted Solution

by:ill
ill earned 240 total points
ID: 16749580
we're sending command( dynamically created) as parameter to DTS
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16768230
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

579 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