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

  • 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

  • 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/

  • Can you post the exact error message.

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

  • 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

  • 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!

  • I am logged into SSMS as 'sa'.

    The exact error message is 'Access is Denied'.

  • Lowell,

    That work great!

    Thanks.

  • 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!

  • Thank you Lowell

  • 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!

  • 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

  • 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 (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!

  • 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

  • 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!

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

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