Using xp_cmdShell: I get 'access denied' copying a file from a server on a domain to the SQL server
I have given my user (acg\\edc) full access to both folders. I have even got desperate enough to give 'everyone' access to the folder on the domain server to no avail. Please find the TransactSQL I have used following... -- Configuring to use xp_cmdshell -- ============================== use master EXEC sp_configure 'show advanced options', 1 go RECONFIGURE go EXEC sp_configure 'xp_cmdshell', 1 go Reconfigure GO -- Configure proxy account -- ======================= --The following did not work exec sp_xp_cmdshell_proxy_account 'acg\edc', 'Doc@2007' go --The following worked create credential ##xp_cmdshell_proxy_account## with identity = 'acg\edc', secret = 'Doc@2007' --Just in case I allowed the same user to use xp_cmdshell. (Possibly not necessary) GRANT exec ON xp_cmdshell TO [acg\edc] -- Finally the code I am using to test the problem -- =============================================== declare @cmd nvarchar(500) declare @Source nvarchar(255) = N'\\assessing-fs\Document Store System\Document Store\17\06\0244\
1387684.PDF' declare @Destination nvarchar(255) = N'C:\Export to CMC Web Page\Assessing\
3PQ22KYP.PDF' set @cmd = 'copy "' + @Source + '", "' + @Destination + '"' exec master.dbo.xp_cmdshell 'whoami.exe' EXECUTE AS LOGIN = 'acg\edc' exec master.dbo.xp_cmdshell 'whoami.exe' exec master.dbo.xp_cmdshell @cmd REVERT exec master.dbo.xp_cmdshell 'whoami.exe' --Finish my output results for the last section ( the whoami.exe and the copy) are... nt service\mssqlserver nt service\mssqlserver access is denied. nt service\mssqlserver I have spent a week on this now and I am at my wits end. I am really hoping their is a way through this.
Thanks Brian for your assistance. You have given me a clue to work with and I have managed to get it to work. NT SERVICE\MSSQLSERVER is a local credential and not a domain credential so I couldn't use it to give permissions on a domain share. So what I did instead was to change the SQLServer credentials to use a domain account that had access to the share, in this case ACG\EDC). I tested again and the file copied.