xp_cmdshell access denied to desktop file when logged as administrator - why ?

Question

xp_cmdshell access denied to desktop file when logged as administrator - why ?

isuckatsql

SSChampion

Points: 10381
More actions
March 23, 2010 at 9:22 am

#213091

I am logged in as an administrator and each time i run the following query
exec master.dbo.xp_cmdshell 'dir c:\users\administrator.mydomainname\desktop\resumes\*.*'
I get an 'Access is denied' error!
This worked fine on my other server, what am i missing?
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

isuckatsql (3/23/2010)
I am logged in as an administrator and each time i run the following query
exec master.dbo.xp_cmdshell 'dir c:\users\administrator.mydomainname\desktop\resumes\*.*'
I get an 'Access is denied' error!
This worked fine on my other server, what am i missing?
Thanks

Are the account has sysadmin privilege ?

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

Can you post the exact error message.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

Check the SQL service account permissions on the box that is working, versus the box that is not working?

http://msdn.microsoft.com/en-us/library/aa260689(SQL.80).aspx

When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Lowell SSC Guru Points: 323518 More actions · Answer 4

this is a common security issue. The problem is that when you access any resource OUTSIDE of SQL server, like network shares, local hard drives,sp_OA type functions etc, it doesn't matter what YOUR credentials are, like Domain Admin,Local Admin etc, because SQL will not carry those credentials to the "outside of SQL" security context.

SQL Server uses the account it starts with to try and access the resource:

That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.

As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.

Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.

you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the linked server works when SQL is run your credentials, so you'd know you need a domain account to access the resource.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

isuckatsql SSChampion Points: 10381 More actions · Answer 5

I am logged into SSMS as 'sa'.

The exact error message is 'Access is Denied'.

isuckatsql SSChampion Points: 10381 More actions · Answer 6

isuckatsql

SSChampion

Points: 10381

March 23, 2010 at 10:22 am

#1138373

Lowell,

That work great!

Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 7

glad i could help!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ntingab SSC Rookie Points: 46 More actions · Answer 8

ntingab

SSC Rookie

Points: 46

November 15, 2012 at 2:55 am

#1559247

Thank you Lowell

Lowell SSC Guru Points: 323518 More actions · Answer 9

ntingab (11/15/2012)
Thank you Lowell

glad this post helped you, ntingab!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ohpenot SSChasing Mays Points: 622 More actions · Answer 10

Hi Lowel,

I have the same problem.."Access denied". Except that I am on windows seven. My PC is on the local network and the company installed sql server express on it for me to start creating the database. I tried Services->SQL Server express->"open a seesion" as "local system" but here again access denied (i asked IT to give me admin rights on my machine and they should do it soon enough hopefully)

What is the solution ?

PS : i am definitely a newbie and don't know much about anything....trying to learn on-the-job

ohpenot SSChasing Mays Points: 622 More actions · Answer 11

Hi Lowel,

Have the same problem "access denied" when trying to exec "xp_cmd". Except that i am on windows seven. The company isntalled sql express on my machine to start constructing the DB.

If i set connexion->"local system" will it work ? (for the moment i need administration rights on my PC and i m waiting for IT to provide )

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 12

ohpenot (2/19/2013)
Hi Lowel,
Have the same problem "access denied" when trying to exec "xp_cmd". Except that i am on windows seven. The company isntalled sql express on my machine to start constructing the DB.
If i set connexion->"local system" will it work ? (for the moment i need administration rights on my PC and i m waiting for IT to provide )
Thanks

ohpenot run the command below for xp_cmdshell. then reread my bigger post with the screenshots. I'm very sure that the user you will see in xp_cmdshell's whoami is going to be something like nt authority\system

so it doesn't matter if you are the domain admin, logged in as sa, or anything else. that login has no access to any shares on any service, and would result in the classic access denied.

when you go to the command line, it's very common that the user is not who you think it should be.

you will need to change the user that is the service is using.

DECLARE @Results table(

ID int identity(1,1) NOT NULL,

TheOutput varchar(1000))

insert into @Results (TheOutput)

exec master..xp_cmdshell 'whoami' --nt authority\system for example

insert into @Results (TheOutput)

exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.

select * from @Results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ohpenot SSChasing Mays Points: 622 More actions · Answer 13

IDTheOutput

1nt service\mssql$sqlexpress

2NULL

3NULL

Indeed! That's what i have. I had checked actually when i first red your post (in services, right-click on the instance name..). The point is that for the moment i can't create any domain account in active directory (i can't do anything...)

if tried to set open session as "local system" jut to try but for some other reason "access denied"

error 0x80070005

Thank you very much Lowell

Lowell SSC Guru Points: 323518 More actions · Answer 14

ohpenot (2/20/2013)
IDTheOutput
1nt service\mssql$sqlexpress
2NULL
3NULL
Indeed! That's what i have. I had checked actually when i first red your post (in services, right-click on the instance name..). The point is that for the moment i can't create any domain account in active directory (i can't do anything...)
if tried to set open session as "local system" jut to try but for some other reason "access denied"
error 0x80070005
Thank you very much Lowell

at least for now, since it's your own express instance, just substitute your own domain login/password instead; then when the IT team gets around to giving you your login specific for SQL, you can change it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!