Access is Denied when accessing files on remote server thru XP_CMDSHELL (was working last week)

Access is Denied when accessing files on remote server thru XP_CMDSHELL (was working last week)

Post by Laurence N » Tue, 12 Jun 2007 19:32:46


BACKGROUND
I have an e-commerce site with separate web server and SQL server (SQL 2005
SP2). JPG images for products are saved in a table. Each night a stored
procedure runs that uses XP_CMDSHELL to run a VBS script that extracts the
images to files on the web server. The VBS script is located on the web
server. The stored procedure uses SQL like this:

declare @wincmd varchar(8000)
set @WinCmd = 'cscript.exe \\webserver\wwwroot\Eur_SaveProductImages.vbs'
exec master.dbo.xp_cmdshell @WinCmd

PROBLEM
Up until very recently this worked fine. Now the above code produces the
following error:

Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
CScript Error: Loading script
"\\webserver\wwwroot\Eur_SaveProductImages.vbs" failed (Access is denied. ).

This happens if the above code is run through a job, or directly with Query
Analyzer. If I try running a test VBS script that just Dims a variable, or
do 'dir \\webserver\wwwroot\', I also get the same error. Therefore SQL
seems to have a basic problem just accessing files on a remote server.

I thought access to the remote server was done in the security context of
the SQL Server startup account. This account exists on the web server (with
same username & password) and is in the local Administrators group. As I
said before, this setup was working up until very recently. There are three
things that have happened that might have something to do with the problem
starting:

1. SQL 2005 Express SP2 got installed on the web server using the same
startup account
2. I changed the SQL Server startup account's password (keeping password the
same on both servers)
3. Various Windows Updates have been installed on both servers

Please note, I am not looking for a "workaround" solution, like "run the VBS
script from the web server". I want to keep the same setup and get it
working as it was a week ago.

Thanks