xp_CmdShell ... ACCESS DENIED !!!

[JayKusch]

Interesting situation here ... Have a dynamic SQL command I have created that has worked in the past on another server BUT does not want to play on its new home.

Here is the code:

declare @SQLCommand VarChar(200)

set @SQLCommand = 
'EXEC master..xp_CmdShell ' + '''' + 
'COPY \\128.1.25.12\TestDir\test.txt C:\delme.txt' + ''''

EXEC (@SQLCommand)

When I run this code, it replies back "Access is denied"

Here is the catch ... I can open the remote share just fine and view the file I am trying to copy.

I just cannot figure where the permissions issue is bitting me. Anyone have any ideas as to where to look. As I said before, it ran fine on the other server. To bad that server is no longer around for me to take a look at possible differences.

Thanks All

Thanks

J. Kusch

 

[MDXer]

Is SQL running under the same account? If not does the account it is currently running under have different permissions.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[JayKusch]

Same account ... same permissions. Thats what is killin' me!!! LOL

Thanks

J. Kusch

 

[JayKusch]

As a side note ... I am able to run the COPY command w/ the same syntax at the command line and it pulls the file over just fine. SO ... I can get there but my command in Query Analyzer w/ xp_cmdshell cannot! GO FIGURE!!

Thanks

J. Kusch

 

[ArtieChoke]

Possibly an obvious question (like "is the computer plugged in" - but of course it has happened that it wasn't!).

Does the account have privs to the master db?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[JayKusch]

Obvious questions are ALWAYS good to ask!!! - LOL

And yes it is plugged in ... as stated above, I am able to pull the file from the remote server by issuing the COPY command from the command line on the source server.

I am even able to created a shared drive to the remote share the file lives on. SO ... I am able to get to the remote share BUT when I try to execute the COPY command in Query Analyzer via xp_CmdShell, it return the "Access is denied" message.

Just as a test, I created another COPY command that copy a txt file from a folder called C:\Test to another folder on the same server called C:\Test2. WORKS FINE!!!

So the code is correct and I am back to figuring out what permissions Query Analyzer, xp_cmdshell or whatever is causing this "Access is denied" issue.

Thanks

J. Kusch

 

[ArtieChoke]

Is it possible that xp_CmdShell can't use the IP address for some unknown reason? What happens if you use an UNK with the share name, like \\<server name>\testdir\...


"I think we're all Bozos on this bus!" - Firesign Theatre

 

[mkal]

Does the NT/SQL account have sysadmin rights. You will need them to execute xp_cmdshell.

From BOL
Permissions
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.



Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

 

[SonOfEmidec1100]

My guess is network permissions for the account that sql server is running under. I dont think you can access networked drives/printers from a service that start under LocalSystem.

 

[aroggeband]

Another reason for "access denied" or "permission denied" occurs when you attempt to manipulate, i.e. overwrite or delete, files that are opened by another process. Is there another process running that accesses one of the files, causing a file sharing lock? If you are executing programs or batch files using xp_cmdshell, it may be that although xp_cmdshell runs syncronously, the program you are running may start something running asyncronously that is holding on to a file.

Try running the copy code in isolation from the rest of your program and see if it is able to do it. If that is the case, you may have to devise some means of testing that a previous step is finished with the file before running the copy, or simply looping until it works(put a timeout counter in the loop, you don't want to wait forever).

 

[SonOfEmidec1100]

The error does not occur when running the copy from the command prompt so it cant be a file permission/access problem.

To confirm my previous post I ran your script on a new default install of sql 2000. In addition I even tried with a unc path and then a mapped drive -

Remote copy gives access denied.
Local copy works.

I changed the sql startup account to my domain account. Works ok....

Any comments