xp_cmdshell

Question

xp_cmdshell

fahd malik

SSC Enthusiast

Points: 143
More actions
January 9, 2008 at 8:42 am

#377902

I have a proxy account set up in SQL2005. This account is a regular user on the machine that has windows 2003 operating system. When I run the command
exec xp_cmdshell 'dir C:\1.txt' under the proxy account it runs fine
but when I run exec xp_cmdshell 'dir C:\1.txt | findstr 1.txt' or exec xp_cmdshell 'dir C:\1.txt | dir C:\1.txt' it throws the error "Access is Denied"
I have done the above on Windows XP and everthing works as it should. Is there something peculair in Windows 2003 server that I may be missing?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Bhavnesh Kakkar Ten Centuries Points: 1023 More actions · Answer 1

When a SQL Server user executes a command prompt command using xp_cmdshell, the command must execute in the security context of a Windows account. If the SQL Server user is a member of the sysadmin fixed server role, SQL Server executes the command prompt command using the Windows account under which the SQL Server service is running. If the SQL Server user executing xp_cmdshell is not a member of the sysadmin fixed server role, SQL Server executes the command using the Windows account specified as the SQL Server Agent proxy account.

Giri Duddu SSCommitted Points: 1638 More actions · Answer 2

if u are connecting using sa login it will not give any error regarding access. If u are connecting using windows authentication, that login id should have permission on the server, then only u could run xp_cmdshell on the server.

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 3

I thought that if you were not using the sysadmin privilege that the windows access was defined by the proxy account designated by the sp_xp_cmdshell_proxy_account stored procedure -- or is that what you mean?

Rosen Rosenov SSC Enthusiast Points: 102 More actions · Answer 4

Greetings,

I am experiencing the same exact issue:

proxy account pointing to a regular user account in Windows.

When i try to run a batch or command containing pipe '|' with xp_cmdshell i get "access is denied".

when i put the user in the local admins account all is fine.

So the problem is:

Why do i have to make the user admin, when the initial idea was to LOWER the security risk by running xp_cmdshell as a restricted user.

I read a lot of posts on this one in google and still no answer.

Does anyone have a solution to this???

Bhavnesh Kakkar Ten Centuries Points: 1023 More actions · Answer 5

When a login that's in the sysadmin role executes xp_cmdshell, it runs under the windows account that SQL Server is running under. When you grant the right to run xp_cmdshell to a login that is not in the sysadmin role, you must set the account that is used to run xp_cmdshell and any programs that it invokes. This is done with the extended stored procedure xp_sqlagent_proxy_account.

Suppose that you want to grant the right to execute xp_cmdshell to the SQL login LimitedUser. You'll need an NT account to execute the program. Here's the script:

use master

go

xp_sqlagent_proxy_account N'SET'

, N' '

go

-- retrieve the proxy account to check that it's correct.

xp_sqlagent_proxy_account N'GET'

go

-- grant database access in master

sp_grantdbaccess 'LimitedUser'

go

grant exec on xp_cmdshell to LimitedUser

go

You will have to put in your information for before you run the script.

Bhavnesh Kakkar Ten Centuries Points: 1023 More actions · Answer 6

posting again the sytax, somehow parameters went missing in previous post.

xp_sqlagent_proxy_account N'SET'

, N'(mydomain)'

, N'(ntuser)'

, N'(ntuser's password)'

EdVassie SSC Guru Points: 60312 More actions · Answer 7

Another thing to remember...

The command environment that SQL uses to run commands from xp_cmdshell is not the full CMD environment you get on your desktop.

There is no input stream in the xp_cmdshell command environment. This could be the cause of your problem with pipes. If there is no input stream, then the results you are piping have nowhere to go.

You need to treat the xp_cmdshell environmet as a rather dumb black box I have looked for the MS article describing how the xp_cmdshell command environment works, but cannot find it. As far as I can remember, the principle is:

a) Anything that would need keyboard input will not work via xp_cmdshell. (There is no-where you can plug in a keyboard...)

b) Anything that needs to display a GUI will not work. (There is no desktop environment...)

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Rosen Rosenov SSC Enthusiast Points: 102 More actions · Answer 8

OK, case solved!!!

I also thought of the problem with named pipes and special permissions.

That's why used the FileMon utility (hail Mark Russinovich) and filtered only pipes, but the problem was not there.

It was far more trivial:

IN Win 2k3 if one goes to the permissions of the cmd.exe in windows\system32 he will find that the only users that can at least read are the following groups: admins, service, system, interactive, telnetclients.

So, if we are using a regular user as an SQL proxy, when calling xp_cmdshell the user does not fall into any of these groups, but when running the commands manually, everything is OK, because the user is automatically put in the INTERACTIVE group.

This leads us to the fix - add the proxy user with read/execute rights for CMD.EXE in the Windows system32 folder.

Voila!

😎

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 9

Very interesting and nice fix. Thanks for the update!

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com