Using xp_cmdshell to access files on a network server
-
We have a problem on our shared file server. Files are badly organized and our current directory structure is rather unstructured. But we didn't just want to move files. We ant to keep a record of where they had been located, where they were moved to, who moved them and when they were moved. The plan was to build a database where each person could be assigned a set of files, they would indicate where they thought the files should be moved, and their choice would be reviewed. Once approved, the record would be marked as such. Then each night we wanted to run a process that would go through the approved but not yet moved records in this dataabse, move each file from its original location to its designated location (creating the directory structure if needed), and then update the record with the time when the file was moved.
I thought I could use the xp_cmdshell command within a query to perform the file moving. But in tests, where I entered this command:
EXEC xp_cmdshell 'move "\\server\origpath\filename" "\\server\newpath\"'
all I get back is a table with a column labelled output that states "Access is denied."
I believe that the Windows login I'm using to connect to SQL server is not being used to execute the xp_cmdshell command in Windows, and that whatever permissions the shell command is getting is not sufficient for it to use the network drive. The identical command works when I move a file from one directory to another on my local C:\ drive.
Is there an easy fix for this?
-
Ed,
if you're running your script within a SQL server job there are usually two possibilities. It can either run under the security context of the job owner or the SQL agent service> If sa is the job owner the latter will be used. A third option would a SQL Agent proxy account, which you can define in the SQL Agent properties.
In your case it sounds like the account which is used doesn't have access to the fileshare(s). This would typically happen if the SQL Agent runs under the local system account. If that's the case change it to a domain account which has change pernmissionson both shares.
Markus
[font="Verdana"]Markus Bohse[/font]
-
As this is posted under SQL Server 2005, you could also use SSIS and the filesystem task to move files around based on information in your database.
-
You may also find that if the login you are using to run this is a SQL login - it may need to be added to the sysadmin role.
Catherine
Kindest Regards,Catherine Eibner
cybner.com.au -
Access to network resources needs the SQL Server service accounts to be a domain account. And also you should use a windows account with proper permissions to access the network resource.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply