xp_cmdShell impersonating as a proxy account user

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.
JustinBlandfordAsked:
Who is Participating?
 
nmcdermaidCommented:
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
 
illCommented:
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
 
JustinBlandfordAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
JustinBlandfordAuthor Commented:
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
 
illCommented:
we're sending command( dynamically created) as parameter to DTS
0
 
nmcdermaidCommented:
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
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.

All Courses

From novice to tech pro — start learning today.