question

Paul_Simpson avatar image
Paul_Simpson asked

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.
xp_cmdshell
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Interested to see the error message from " exec sp_xp_cmdshell_proxy_account 'acg\edc', 'Doc@2007' " as that should work. Other than that, are you able to add the service account read permissions to the file share? As everything is executing as NT SERVICE\MSSQLSERVER, it will be the actual windows account running the SQL Engine service that is accessing the fileshare.
0 Likes 0 ·

1 Answer

·
Paul_Simpson avatar image
Paul_Simpson answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.