SQL Log Messages - FILESTREAM and xp_cmdshell

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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%'