0

I am trying to copy a file from a remote path to my local machine using xp_cmdshell in SQL management studio.

The following are the steps i use:

1. Establish a connection

exec xp_cmdshell 'net use L: \\remoteServer\folder /user:domain\username ImPWD'

2. Re-connect

When i execute exec xp_cmdshell 'net use', the status of L is unavailable. So I ran exec xp_cmdshell 'net use L:' This doesn't help anyway. It still is unavailable!

3. Copy file

EXEC xp_cmdshell 'COPY L:\Fol1\SubFol2\File.xlsx C:\work\file.xlsx'

This is the error i get:

The system cannot find the file specified.

I have tried the following but nothing seems to work yet:

  • Found the user credentials that is used by xp_cmdshell and tried connecting to the shared path manually through windows UI and it seems to work. Therefore i concluded that there are no permission issues.EXEC xp_cmdshell 'whoami.exe'

  • Ran the queries as a normal user and an administrator. Both resulted in the same output.

Here are my questions:

  1. Am I missing some step?
  2. Why is the connection getting unavailable after the first connect?
  3. How to delete connection? i.e When i run exec xp_cmdshell 'net use * /delete' it asks

Do you want to continue this operation? (Y/N) [N]: I am not sure how to say 'Y' using management studio query.

Thanks for your help in advance! :)

Linda
  • 147
  • 2
  • 20
  • 1
    For the 3rd question: `exec xp_cmdshell 'net use * /delete /y' ` It will select Yes silently – Lukasz Szozda Aug 02 '17 at 08:38
  • 1
    @lad2025 thanks! that works perfectly! When i delete and add the connection again, the connection status become 'OK' which was previously 'unavailable'. Now the only issue remaining is, it is still not copying. Getting the same error. – Linda Aug 02 '17 at 08:47
  • Guess i have found the answer after two days of crazy debugging! LOL! It was just the folder name issue. Folder names with spaces!!! It works like a charm if i wrap the path in double quotes! – Linda Aug 02 '17 at 09:06
  • Great to hear that it works :) – Lukasz Szozda Aug 02 '17 at 09:07

1 Answers1

1

How to delete connection?

To delete connection use: 

exec xp_cmdshell 'net use * /delete /y'

(yes) will be silently passed

As for other questions: you mentioned in comment that your folders in path have spaces so you could:

  • rename folders
  • wrap with ""
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275