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?

  • That doesn't seem right, seems like some kind of policy framework thing perhaps. Are you running Policy-Based Management? – Aaron Bertrand May 2 '14 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. – Sean Perkins May 2 '14 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 '14 at 17:54
  • Okay, I've perused all my instances and found nothing running or configured from a PBM standpoint. – Sean Perkins May 2 '14 at 18:16
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).

  • Great! Thanks for the input, I'll run the trace and readdress on Monday and post any findings, appreciate your help Aaron. – Sean Perkins May 2 '14 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. – Sean Perkins May 2 '14 at 21:15
  • @Sean try now, please – Aaron Bertrand May 2 '14 at 21:17
  • That did it, tyvm sir! I'll post my findings. – Sean Perkins May 2 '14 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.

  • Actually the log message makes it look like something is repeatedly trying to turn xp_cmdshell off. – Aaron Bertrand May 2 '14 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? – Sean Perkins May 2 '14 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 '14 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" – Sean Perkins May 7 '14 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%'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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