create XML file on other server's disk with xp_cmdshell on same network

Question

I have two servers on my network, one for RDBMS (Sql Server 2005) and the second is Web Server (which runs IIS 7.5).

RDBMS server's local IP is 192.168.1.5 and UNC name is DataOne

Web Server's local IP is 192.168.1.4 and UNC name is FirmWebOne

With following block i can run a query and create a xml file on C: for the RDBMS server machine. It works fine.

DECLARE @FileName VARCHAR(50)
DECLARE @SQLCmd   VARCHAR(500)

SELECT  @FileName = N'\\192.168.1.5\c$\trial_1.xml'

SELECT  @SQLCmd = 'bcp ' +
 '"SELECT I.code,L.name  ' +

' FROM Portal.dbo.inventory I LEFT JOIN Portal.dbo.inventoryLocalization L ON I.code = L.code AND L.language =''en'' ' +

' FOR XML PATH(''Product''), ROOT(''Products''), TYPE "' +

' queryout '  + @FileName +
' -w -T -S' + @@ServerName

SELECT @SQLCmd AS 'Command to execute'

EXECUTE master..xp_cmdshell @SQLCmd

Can i create that xml file on Web Server disk ?

I have tired Web Server's IP as N'\192.168.1.4\c$\trial_1.xml'

and web server's UNC address as N'\FirmWebOne\c$\trial_1.xml'

but it returns following errors.

SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

Any idea ?

Thanks in advance...

PS:by the way, i have checked the account with EXECUTE master..xp_cmdshell 'whoami.exe' it returns nt authority\system and this account have permissions on the network

Answer 1

NT Authority\System is a local user and I guess you didn't explicitly give permissions to him on \FirmWebOne\c$. I would change account under which xp_cmdshell is running by setting proxy account. Then you can grant access to this user on \FirmWebOne\c$

BTW: I'd better avoid using C$ and create a share folder. It's more manageable and secure.