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

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

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

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.