Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

xp_cmdshell works and then doesn't work Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 11:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, December 4, 2012 10:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 27, 2014 11:02 PM
Points: 251, Visits: 618
—- 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
Posted Wednesday, December 5, 2012 4:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
please check .

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

Post #1392921
Posted Wednesday, December 5, 2012 6:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1392983
Posted Wednesday, December 5, 2012 8:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, December 5, 2012 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, December 5, 2012 10:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
Could it be that the files are moved out of the directory, at some point, and that the DIR is reporting correctly?

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1393118
Posted Thursday, December 6, 2012 4:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
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
Posted Thursday, December 6, 2012 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, December 6, 2012 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase 12»»

Permissions Expand / Collapse