0

I was perusing the SQL logs today and found the following:

FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

Configuration option 'xp_cmdshell' changed from 1 to 0. Run the RECONFIGURE statement to install.

These messages are peppered throughout every day during the past week, sometimes in blocks of 30+. Also, when they happen, they appear in blocks 10-20 seconds. I don't really see this as many times in the other servers I comb. Is this anything to be alarmed about?

Improve this question
4
  • That doesn't seem right, seems like some kind of policy framework thing perhaps. Are you running Policy-Based Management?
    – Aaron Bertrand
    May 2, 2014 at 17:07
  • I'm 99.9% positive that we're not running PBM. Is there an easy way to determine if I am or not? I'd ask the other DBAs on staff, but I'm riding solo today.
    – AKDiscer
    May 2, 2014 at 17:51
  • Well it can be running from just about anywhere in your environment, so I don't know of an easy way to check except to look at each instance individually and see if they have any PBM-related jobs or evidence of this framework from CodePlex being installed. I mean I am sure there are ways to check this using PowerShell or other tools but I don't have any type of scripts handy.
    – Aaron Bertrand
    May 2, 2014 at 17:54
  • Okay, I've perused all my instances and found nothing running or configured from a PBM standpoint.
    – AKDiscer
    May 2, 2014 at 18:16

3 Answers 3

Reset to default
2

Really not sure what else aside from PBM might be set up to try so forcibly to disable xp_cmdshell, but it definitely sounds like something set up by IT in some way. I don't think this is really causing any problems, so you could just wait until the rest of your team is available and bring it up to them. If you want to get more information about it, you could set up a server-side trace that captures all calls to sp_configure - this will at least tell you the host name, application name, user name etc. 

declare @rc int, @TraceID int, @maxsize BIGINT = 5;

exec @rc = sp_trace_create @TraceID output, 0, N'C:\myfolder\mytrace', @maxsize, NULL 
if (@rc != 0) goto error

declare @on bit = 1;
exec sp_trace_setevent @TraceID, 42, 1, @on;
exec sp_trace_setevent @TraceID, 42, 6, @on;
exec sp_trace_setevent @TraceID, 42, 7, @on;
exec sp_trace_setevent @TraceID, 42, 8, @on;
exec sp_trace_setevent @TraceID, 42, 10, @on;
exec sp_trace_setevent @TraceID, 42, 11, @on;
exec sp_trace_setevent @TraceID, 42, 14, @on;
exec sp_trace_setevent @TraceID, 42, 34, @on;
exec sp_trace_setevent @TraceID, 42, 41, @on;
exec sp_trace_setevent @TraceID, 42, 64, @on;

exec sp_trace_setfilter @TraceID, 34, 0, 6, N'sp_configure';
exec sp_trace_setstatus @TraceID, 1;
select TraceID=@TraceID;
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Probably more event columns there than necessary, but it's not like you're going to be running this for a long time. You should be able to turn it off as soon as you see another message pop up in the log (because I doubt this is coming from multiple sources).

Improve this answer
4
  • Great! Thanks for the input, I'll run the trace and readdress on Monday and post any findings, appreciate your help Aaron.
    – AKDiscer
    May 2, 2014 at 19:03
  • I tried running your code in SSMS and I received the error: "Msg 214, Level 16, State 3, Procedure sp_trace_create, Line 1 Procedure expects parameter '@maxfilesize' of type 'bigint'." I'm new to running traces and have only done them via the GUI, never via code. After looking through the GUI, I don't see anything that seems to match up with you've provided, so I'm guessing there is a LOT more granularity via code.
    – AKDiscer
    May 2, 2014 at 21:15
  • @Sean try now, please
    – Aaron Bertrand
    May 2, 2014 at 21:17
  • That did it, tyvm sir! I'll post my findings.
    – AKDiscer
    May 2, 2014 at 21:20
0

Something must be trying to use xp_cmdshell to execute a batch file outside of sql server. It looks like xp_cmdshell has been switched on but you need to run RECONFIGURE for it to be active. Then the messages will disappear as what ever is trying to use it will be able to.

Improve this answer
4
  • Actually the log message makes it look like something is repeatedly trying to turn xp_cmdshell off.
    – Aaron Bertrand
    May 2, 2014 at 17:52
  • I thought the prevailing wisdom was to have xp_cmdshell disabled. Would there be any harm in doing so now? Or is there a way for me to find out what is invoking the use of xp_cmdshell?
    – AKDiscer
    May 2, 2014 at 19:00
  • 1
    @SeanPerkins well, you could add xp_cmdshell to the filter for the trace. Or, it should already tell you who is turning it on, too. Since - if turning it off is actually working - they have to be turning it on in order to use it. I'm surprised that turning it on isn't also showing up in the log. Perhaps the reason is that it's on, this sp_configure call to turn it off keeps being attempted, but they never bother to call RECONFIGURE - which means the setting stays at 1.
    – Aaron Bertrand
    May 2, 2014 at 19:23
  • Just wanted to drop a line and say this isn't a "problem" it is intentional. It is something being run by another department, so I don't know if this question would be classified as "answered"
    – AKDiscer
    May 7, 2014 at 15:08
0

You may have a procedure or function executing xp_cmdshell and possible sp_configure to enable it before execution and then disable it after execution. Run the following t-sql to check what's in your database's code base:

Use DatabaseName;
GO
Select object_name(id), text
From   syscomments
Where  text like '%create%procedure%xp_cmdshell%'
Or     text like '%create%procedure%sp_configure%'
Or     text like '%create%function%xp_cmdshell%'
Or     text like '%create%function%sp_configure%'
Improve this answer
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.