xp_cmdshell works and then doesn't work

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » xp_cmdshell works and then doesn't work

11 posts, Page 1 of 2

1 2 »»

xp_cmdshell works and then doesn't work

Rate Topic

Display Mode

Topic Options

Author

Message

mike_macairan

Posted Tuesday, December 04, 2012 11:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 11:47 AM
Points: 5, Visits: 12

A stored procedure intermittently fails, and it appears to be because xp_cmdshell stops working.

If I try it manually, here is what I get.

* Open Management Studio
* exec xp_cmdshell 'Dir c:\' (I get valid results in the form of a list of the directory contents for c:\)
* Do that a few more times and get the same results.
* Eventually, I will try it and rather than getting the contents of the directory, I get NULL and will keep getting NULL no matter how many times I try
* I have never SEEN it start working again, but I have come back a day or two later and it was working

Please note that this happens with ANYTHING I try to do using xp_cmdshell. Using 'Dir c:\' is just an example.
I can try anything with xp_cmdshell, have it work for a while, and then I will see it just stop working.
I don't get an error saying xp_cmdshell isn't configured, and I don't think it's permissions or it wouldn't just start working again

When exec xp_cmdshell is working, so does the stored procedure.
When exec xp_cmdshell is not working, neither does the stored procedure
This is because the stored procedure calls xp_cmdshell when it's running

I am just really frustrated trying to figure out what could cause xp_cmdshell to just suddenly stop working and to then start working again later.

Post #1392636

jeetsingh.cs

Posted Tuesday, December 04, 2012 10:54 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:06 PM
Points: 179, Visits: 380

—- To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

GO

—- To update the currently configured value for this feature.

RECONFIGURE

GO

Check if this works for you.

Post #1392794

Hemant.R

Posted Wednesday, December 05, 2012 4:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 45, Visits: 175

please check .

it will work only if you are exec using sysadmin, normal user it won't work.

Post #1392921

Jeff Moden

Posted Wednesday, December 05, 2012 6:44 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

Also check other people's jobs and stored procs which may be intentionally turning off xp_CmdShell. People do this thinking they make its use more secure. That's false, of course, but they do it anyway.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1392983

mike_macairan

Posted Wednesday, December 05, 2012 8:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 11:47 AM
Points: 5, Visits: 12

Thanks for the suggestion. I have tried enabling xp_cmdshell like this before and it didn't make any difference. If you try to run xp_cmdshell and it is not enabled, I believe you get an error message telling you so. I never get that error. I get results, then results, then results, then NULL and once I get NULL, I continue getting it for a long time. If I come back later, it may work again, but will eventually go back to returning NULL.

Post #1393036

mike_macairan

Posted Wednesday, December 05, 2012 8:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 11:47 AM
Points: 5, Visits: 12

Checking other people's jobs is a good suggestion, too, but this server was built for a specific purpose and should contain only the jobs that are part of the standard build--which would match all the other servers that were put out by this company, and which are working just fine.

I am in agreement that there may be something else happening, though, that is blocking xp_cmdshell--simply based on the way that this happens intermittenly. If it was a consistant failure, maybe it's a permissions or configuration problem. If it works and then stops and then works again, there must be something else going on that blocks xp_cmdshell. I just can't find whatever it is.

Post #1393040

Jeff Moden

Posted Wednesday, December 05, 2012 10:25 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

Could it be that the files are moved out of the directory, at some point, and that the DIR is reporting correctly?

--Jeff Moden

Post #1393118

Hemant.R

Posted Thursday, December 06, 2012 4:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 45, Visits: 175

hi,

you can also find reason in sql server job log.
for that ,you have to give output file in your job .
it will written log and you will exact reason over there.

Post #1393431

mike_macairan

Posted Thursday, December 06, 2012 8:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 11:47 AM
Points: 5, Visits: 12

It's not that files are being moved. The way this happens is, I open Management Studio, open a query window, type:

exec xp_cmdshell 'Dir C:\'

and execute it.

I get the results (which is a listing of everything in the root of C:\)

I can hit execute a dozen times and, at some point, the result set will change from being the contents of C:\ to NULL, but the files are exactly where they were. I can open a command prompt and do it and get the correct results every time. Once the results become NULL, I can try it a hundred more times and it won't go back to giving me the contents of C:\. If I come back, say, the next day, it may or may not start giving me the contents of C:\ again.

Once it is in that state (returning NULL for everything), the stored procedure will fail, because one of the steps uses xp_cmdshell.

I am thinking that something else is going on within SQL that blocks xp_cmdshell or that hogs it resources or something. And it's not JUST when I do a Dir; it's with anything I try to do using xp_cmdshell.

I know this is a difficult question because I did extensive online searches before posting my question. A lot of people have had this problem, most of the answers are off-track because it's kind of hard to explain the symptoms--and the only one of those posts where the solution was found was one where the Antivirus program was preventing xp_cmdshell. I don't have an antivirus program on here (I took it off for troubleshooting) and the firewall is turned off.

Post #1393580

mike_macairan

Posted Thursday, December 06, 2012 9:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 11:47 AM
Points: 5, Visits: 12

I have looked in the output log for the job. It just says that zipping up the directory failed, but doesn't give a reason for the failure. Zipping the directory is what's being done through xp_cmdshell; the job (and stored procedure) work fine at many other sites. I am sure that, once I find out what makes xp_cmdshell suddenly start returning NULL, the job and stored procedure will have no more problems.

Post #1393583

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions