Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lneville
Starting Member
12 Posts |
 Posted - 2007-06-20 : 05:41:20
|
| I am using XP_CMDSHELL to run 2 Windows commands (from a SQL 2005 SP2 server) that access files on a remote server (called webserver). The two commands are:1. cscript.exe \\webserver\wwwroot\Eur_SaveProductImages.vbs2. dir \\webserver\wwwrootUp until very recently this worked fine. Now I am getting Access Is Denied errors:For #1:Microsoft (R) Windows Script Host Version 5.6Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.CScript Error: Loading script "\\webserver\wwwroot\Eur_SaveProductImages.vbs" failed (Access is denied. ).For #2:Access is denied.This happens if the above commands are run through a job, or directly with Query Analyzer as SA.I understand that XP_CMDSHELL commands are run in the security context of the SQL Server startup account. The SQL Server startup account is called SQLServer and this account exists on both webserver and the database server. On both servers the username & password are identical and the user is in the local Administrators group. The two servers are in a workgroup.Things I have tried (with no effect):1. Restarted the servers2. Deleted and recreated the SQLServer login on both servers3. Changed the startup account for SQL Server through the SQL Server Configuration Manager to another account and back to SQLServer.The most revealing tests are these:1. If I log into the database server myself using the SQLServer account I can run the above commands in a Command Prompt with no problem2. I installed SQL 2005 Express SP2 on webserver using the same startup account (SQLServer). If I run the same XP_CMDSHELL commands as above but accessing files on the database server, the commands work!So, an XP_CMDSHELL command run under SQL Express using the local SQLServer account to access a remote server works, but an XP_CMDSHELL command run under SQL Server using a local SQLServer account (that is supposedly identical to the same named account on the other server) does not work !!?!?!?It is almost as if XP_CMDSHELL commands run under SQL Server are NOT using the SQLServer startup account, but using some other weaker account. How can I tell if this is true?How can I get this back working (as it was a week ago). The only thing I can think that happened on the servers to cause this is the installation of Windows Updates.Thanks |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2007-06-25 : 12:53:25
|
| Silly question, but: Did you enable XP_CMDSHELL in SQL Server Surface Area Configuration, under Surface Area Configuration for Features ? By default it is disabled, but maybe not disabled for Express?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
lneville
Starting Member
12 Posts |
Posted - 2007-06-25 : 14:01:29
|
| Yes it is enabled. XP_CMDSHELL is definately running - what I am seeing is the Access is denied message that Windows is returning. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-25 : 14:05:51
|
| Log into the database server using your SQLServer account. When I say log into the db server, I mean physically go to its console, Remote Desktop to it, etc... Then go to Start..Run..cmd. Typie in your commands there. What do you get? Same error?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
lneville
Starting Member
12 Posts |
Posted - 2007-06-25 : 15:20:48
|
| As I explained above, when I do that the commands work! Using the same account (SQLServer)!! Very mysterious!!! |
|
|
|
lneville
Starting Member
12 Posts |
Posted - 2007-06-25 : 15:24:37
|
| So I am thinking it has something to do with the difference between logging in interactively and logging in as a "batch job". However I have checked that SQLServer has the "Log on as a Batch Job" right (in Local Security Policy>Local Polices>User Rights Assignment) on both servers. I have also compared every single one of the rights between the two servers and found no differences. So why it works in one direction and not in the other is a mystery! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-25 : 22:20:09
|
| Who is job owner? Is it member of sysadmin? Tried set proxy account for xp_cmdshell? |
|
|
|
lneville
Starting Member
12 Posts |
Posted - 2007-06-26 : 02:50:41
|
| The problem occurs when the commands are run as a job AND when run manually thru Query Analyzer. The proxy account is only used when a non-SA user runs XP_CMDSHELL so I don't think it applies here. |
|
|
|
|
|
|
|
|
XP_CMDSHELL- Access Denied accessing remote server