Access denied for enabled xp_cmdshell for the admin user

Question

I'm using xp_cmdshell within a database trigger to launch a exe file.

xp_cmdshell is enabled(it can execute simple cmd command like 'echo'). But when I try to launch the exe through xp_cmdshell, the access is denied.

I am the database administrator. And I can launch the exe through cmd directly. Anyone know why I get denied and how to fix it?

The message has no more details than that? It doesn't indicate whether it is talking about access to the stored procedure or the file system? — Martin Smith, May 30 '12 at 20:26
Just that. The stored procedure works and output the error message of the file system I think. — Joshua H., May 30 '12 at 20:37

score 14 · Answer 1 · edited Mar 09 '15 at 05:49

14

Use xp_cmdshell to run "whoami", then check effective permissions for the stated user on the exe and any resources it accesses. Odds are that an account like localsystem is being used to run processes via xp_cmdshell.

EXEC xp_cmdshell 'whoami'

edited Mar 09 '15 at 05:49

answered May 30 '12 at 19:36

Peter Wishart

11,600
1
26
45

I am getting this error. "whoami is not recognized as an internal or external command, operable program or batch file. – Joshua H. May 30 '12 at 20:03
1

Sorry, you might have to install it first, link here: http://serverfault.com/a/9984 – Peter Wishart May 30 '12 at 23:23
I have installed the tool. whoami works fine when i executed it in cmd. But it failed in xp_cmdshell('whoami' is not recognized as an internal or external command). Do you know why this happend? – Joshua H. May 31 '12 at 13:22
Weird.. you could try comparing the results of: "set comspec" & "set path" between cmd and xp_cmdshell, and make doubly sure you're connecting to a local server (turn off your local server and try to connect, should fail). – Peter Wishart May 31 '12 at 21:33
It tells it's nt service \ mssqlserver. Who should it be? That's the one that doesn't let me do anything. – soulblazer Apr 11 '17 at 05:35
@soulblazer if you have an account that you know works you can [set xp_cmdshell to run using it](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql) just be aware of any security impact – Peter Wishart Apr 11 '17 at 10:56
@PeterWishart I set it to local system, and it finally allowed me to access my username folder. Thank you. – soulblazer Apr 11 '17 at 19:22

score 10 · Accepted Answer · answered May 30 '12 at 19:01

10

Likely insufficient NTFS permissions. Make sure the 'user account' that the SQL Server is running as, has permission (Read+Execute) to the *.EXE (and any dependent files)

answered May 30 '12 at 19:01

JMC

910
7
11

I am testing on my own db. And the exe is created by myself. I do have the permission to read and execute. – Joshua H. May 30 '12 at 19:04
@JoshuaH. You have, but does the user that runs SQL Server (e.g. LocalSystem or NetworkService)? – Christian.K May 30 '12 at 19:40
3

+1 JMC. I agree that it sounds like the service account that the MSSQLServer service is running as doesn't have permission. Open services and check the login property for the SQL Server service account. – brian May 30 '12 at 20:12
Login property: Server type:Data Engine, Authentication: Windows Authentication. How do i check whether user account has permission for the exe? – Joshua H. May 30 '12 at 20:22
I've also have a question, as my SQL Server is running under Local System and still can't overdo this error. – Johnny_D Oct 10 '13 at 11:08

score 0 · Answer 3 · answered Sep 17 '12 at 17:20

Not sure, but I believe that the trigger is run by the user running the SQL command that "triggered" the trigger.

Is the user issuing the SQL command a Windows user or a SQL user ? If it's a SQL user, you need to set an "SQL Proxy". The SQL Proxy is used to tell SQL which Windows user will be used to access the file system.

Hope this helps,

Yves

score 0 · Answer 4 · answered Feb 09 '15 at 19:52

I was getting ACCESS DENIED when trying to run BCP and then COPY.

What I found is that xp_cmdshell starts in c:\windows\system32

I modified my CMD file to change to my working folder

L: cd L:\myworkingfolder

This solved my problem, Event though my sqlagent was a local administrator and I had full pathed my copy command.

score 0 · Answer 5 · answered Aug 23 '19 at 03:07

Time to contribute now. I am sysadmin role and worked on getting two public access users to execute xp_cmdshell. I am able to execute xp_cmdshell but not the two users.

I did the following steps:

create new role:

use master CREATE ROLE [CmdShell_Executor] AUTHORIZATION [dbo] GRANT EXEC ON xp_cmdshell TO [CmdShell_Executor]
add users in master database: Security --> Users. Membership checks only [CmdShell_Executor] that is just created
set up proxy account:

EXEC sp_xp_cmdshell_proxy_account 'domain\user1','users1 Windows password' EXEC sp_xp_cmdshell_proxy_account 'domain\user2','users2 Windows password'

Then both users can execute the stored procedure that contains xp_cmdshell invoking a R script run. I let the users to type in the password, execute the one line code, then delete the password. All in my pc.

score 0 · Answer 6 · answered Jan 02 '20 at 22:36

0

You can also get Access is denied. when you don't specify path to executable correctly. Note if your path contains spaces, you need to enclose the executable into double quotes:

EXEC xp_cmdshell '"D:\My path\With spaces\runme.exe"'

answered Jan 02 '20 at 22:36

vasek

2,759
1
26
30

score 0 · Answer 7 · edited Jan 04 '21 at 17:15

0

I had the same problem and I solved it like this:

Open SQL Server Configuration Manager
Select your instance and right-click -> properties
Select Log on tab
And select authorized account

edited Jan 04 '21 at 17:15

shreyasm-dev

2,711
5
16
34

answered Jan 04 '21 at 16:51

Fazlı Keleş

11
2

Access denied for enabled xp_cmdshell for the admin user

7 Answers7

Linked