xp_cmdshell fails to execute any .exe file after about 2 months - forcing me to reboot server

Question

Hi

I have some servers where xp_cmdshell fails to execute any .exe file after running for about 2 months so I'm forced to do a server reboot.

For example if I run something simple like this :-
EXEC xp_cmdshell 'Dir C:\*.*'

then it outputs NULL.

Other servers are using the same engine version of MS SQL but find that they also fail after a few months which are on version:
SQL 2000
8.00.2039 SP3 Standard Edition

Also the servers are already configured to allow xp_cmdshell :-
Programs > Microsoft SQL Server 2005 > SQL Server Surface Area Configuration >
Surface Area Configuration for Features > Enable xp_cmdshell

I found a Microsoft KB that might be related which I've applied but still find that I have to reboot the servers again after between 2 and 3 months :-
http://support.microsoft.com/kb/824422.

If we reboot the server then run any exe using  xp_cmdshell then it runs fine again. Then another 2 to 3 months later I'm forced to reboot the server again.

My customers are starting to complain that we have to keep rebooting their server and are not starting to refuse to let me do it - I need help!!!

If anyone can help me get to the bottom of this then it will be extremely appreciated.

Cheers
Matthew

Answer 1

Matthew,

Under what account is your SQL Server Service running?

Answer 2

Hi pl80

The MSSQLSERVER uses a Log On as Local System.

Someone has also suggested that it might be that an account password is expiring which the reboot tends to fix (temporily). I'm not sure if this is the answer or how to check if the password has really been expired if you can help further.

cheers

Matthew

Answer 3

See the below link for some info:

http://technet.microsoft.com/en-us/library/bb680595.aspx

Best bet is to create a separate domain account for SQL Server, and grant it appropriate rights.

Answer 4

Hi pl80

The TechNet article is really SQL 2007 but the servers I support are SQL 2000.

But anyway, are you able to give me any tips about how to create a seperate domain account for SQL server and give it the same permissions.

Much appreciated if you can advise.

Thanks

Matthew

Answer 5

User account is not likely to be the cause of your problem. If the account has expired, the service will not start. Besides, you can't change the local system account password - it's not recognized by the security subsystem that allows you to manage regular accounts/groups. That's one of several good reasons why you should not use that for the service.

If you haven't already done so, check the Windows event logs and SQL Server logs to see if there are entries that hint of resource pressure. E.g. objects cannot be created/initialized

The symptom and workaround you described resembles a resource shortage problem. It's similar to a memory leak type of problem where things deteriorate over time or over a series of events. Is the problem new, meaning things were fine for some number of years and has only started recently (past few months or past year)? If so, were there new software installed? Could be management tools, agents, antivirus, monitoring, etc...

---

No great genius has ever existed without some touch of madness. - Aristotle

Answer 6

Hi jmatty2000,

For this issue, I would like to give you some workarounds:

1.  Stopped SQL Server Agent see if it has been resolved, if you could.

2. Changed SQL Server Agent service account to different account from SQL Server Service account.

3. If you have antivirus software installed in your machine, disabled the antivirus, the xp_cmdshell will start working fine. The xp_cmdshell remains working fine, if you restart the antivirus.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

Hi Joe

As far a I know, this problem has been going on for about 5 year or more across at least 1000 plus servers. A colleague of mine has checked the Event Logs and the SQL log file but that does not reveal anything but I'll double check now before I forget.

A little pattern is emerging so far is that about 5 servers I've checked are all running MS SQL 2000 Engine. I will check the versions on more of them as the sites keep logging these issues every day.

So its a little knight-mare!! I this problem can be worked out then it just goes to show how Forums are!!! but thanks for all your help so far everyone.

Thanks

Matthew

Answer 8

Hi Matthew,

Have you tried the workarounds above?

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 9

Hi Iric

I've just tried restarting the SQL Server Agent but it still outputs NULL when running an EXE eg

EXEC xp_cmdshell 'Dir C:\*.*'

This service is Logged on as SQLServerService - if I were to try a different account which would you recommend to try eg would user sa be best? ...sorry I don't know much about user logins.

Thanks

Matthew

Answer 10

Matthew,

Is there any antivirus software installed in your machine?

Answer 11

Hi Yaphets

A few others suggested that but the server that I honed in on did not even have AV installed on.

So its a little knight-mare!!

Answer 12

To resolve it, you need to trace the related error message(s). Look in server error log, sql agent log and event viewer.

---

Kalman Toth SQL SERVER & BI TRAINING