XP_CMDSHELL executes successfully on SQL 2000 but fails on MSDE 2000 for Sharepoint DB

[b***@spamhole.com]

We have two instances of MS-SQL Server 2000 on the same Win2003
server.

Both instances use a Startup Service Account (domain\sql) instead of
LocalSystem.

In both instances we need to use XP_CmdShell to map to a share on a
NAS for backup purposes. The NAS is in a server farm and is not part
of our domain.

The first instance is running SQL 2000 Standard Edition version
8.00.760.

On this instance XP_CmdShell works fine.

The second instance is running SQL 2000 Desktop Engine (MSDE) version
8.00.2039 and is used as the DB for our Sharepoint server.

On this instance XP_CmdShell doesn't work at all.

For example, we try to issue the following command:
declare @result int
exec @result = xp_cmdshell 'echo hello world'
select @result

On the first instance we get these results:
output
-----------
hello world
NULL

(2 row(s) affected)

Result
-----------
0

(1 row(s) affected)

On the second instance we get these results:
Result
-----------
1.00

No error message is raised on the second instance, but xp_cmdshell
does absolutely nothing and returns a status of 1.

To try and resolve this issue we have tried a number of things to no
avail:
1. Tried adding domain\sql to the Administrators group
2. Tried adding domain\sql as a proxy account using
xp_sqlagent_proxy_account
3. Tried giving doman\sql additional rights via domain security
policy:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
4. Tried removing and re-adding domain\sql as the Startup Service
Account.

One interesting point is that if we just install MSDE version
8.00.2039 WITHOUT sharepoint, xp_cmdshell works fine. But if MSDE
version 8.00.2039 is installed as part of sharepoint, xp_cmdshell does
not work at all.

I suspect Sharepoint is doing something to the SQL instance, but I
don't know what.

How do we get xp_cmdshell to work on our Sharepoint MSDE db?

Many thanks.

[b***@spamhole.com]

The problem is with the version of XP_CmdShell that is released with
the version of MSDE that comes with Sharepoint.

I deleted XP_CmdShell from the MSDE server, and replaced it with a
copy from the working server, and it fixed the problem.

I.e.:

-- drop the proc
exec sp_dropextendedproc xp_cmdshell

follwed by:

-- add the proc
exec sp_addextendedproc xp_cmdshell, 'C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\xplog70.dll'

This assumes that the working version of SQL is at the path 'C:
\Program Files\Microsoft SQL Server\MSSQL\Binn\xplog70.dll'.

I don't like this solutiion since it's replacing a newer version of a
DLL with an older one. But as I mentioned above, I think the
XPLOG70.DLL that comes with MSDE / Sharepoint is disabled or buggy.

[Andrea Montanari]

hi,

Post by b***@spamhole.com
The problem is with the version of XP_CmdShell that is released with
the version of MSDE that comes with Sharepoint.
I deleted XP_CmdShell from the MSDE server, and replaced it with a
copy from the working server, and it fixed the problem.
-- drop the proc
exec sp_dropextendedproc xp_cmdshell
-- add the proc
exec sp_addextendedproc xp_cmdshell, 'C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\xplog70.dll'
\Program Files\Microsoft SQL Server\MSSQL\Binn\xplog70.dll'.
I don't like this solutiion since it's replacing a newer version of a
DLL with an older one. But as I mentioned above, I think the
XPLOG70.DLL that comes with MSDE / Sharepoint is disabled or buggy.

WMSDE, the MSDE version included with Sharepoint, is a modified version of
MSDE, so some differences are "expected".. I do not know if this "behaviour"
is exptected as well, but I do think you have broken the license agreement
doing the way you did.. you should eventually consider contacting your
Microsoft local office to solve the problem and eventually "get" a
notification by MS employees you are "allowed" to auto-patch the instance
the way you did..