What changed 'sp_configure' settings

• A3Paper

  SSChasing Mays

  Points: 636

  More actions

  January 17, 2017 at 9:28 am

  #325478

  Hi,

  I'm hoping to find out what or when sp_configure 'default trace enabled' was changed on multiple SQL instances, however I'm having difficulties.

  1. I've searched all logs - no joy.

  2. Configuration Changes History report shows:

  "This report provides a history of all sp_configure and Trace Flag changes recorded by the Default Trace.

  Configuration Changes History (Since ).

  Shows changes in server configuration and flags.

  Currently, the report does not have any data to show, because default trace does not contain relevant information."

  3. Running the following shows (ErrorLog / Audit DBCC Event):

  SELECT name FROM sys.trace_events WHERE trace_event_id IN (22, 116)

  Any help would be appreciated.

  Thanks

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 17, 2017 at 11:45 am

  #1923248

  A3Paper - Tuesday, January 17, 2017 9:28 AM

  Hi,

  I'm hoping to find out what or when sp_configure 'default trace enabled' was changed on multiple SQL instances, however I'm having difficulties.

  1. I've searched all logs - no joy.

  2. Configuration Changes History report shows:

  "This report provides a history of all sp_configure and Trace Flag changes recorded by the Default Trace.

  Configuration Changes History (Since ).

  Shows changes in server configuration and flags.

  Currently, the report does not have any data to show, because default trace does not contain relevant information."

  3. Running the following shows (ErrorLog / Audit DBCC Event):

  SELECT name FROM sys.trace_events WHERE trace_event_id IN (22, 116)

  Any help would be appreciated.

  Thanks

  It wouldn't be in the Configuration History as that pulls from the default trace files and if it was enabled, nothing was running to show who enabled it. It wont be in the list of trace events since that's just a listing of the events that can be used for traces.

  I think the only place it would show up is in the error log, along the lines of:
  Configuration option 'default trace enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
  Followed by:
  Trace ID  NN  started by  login Whereever\Whoever
  If it's not in the current error logs, you could possibly try to get older logs from the OS backups and go through those. Depends on how the backups for the server are done, what's included, what the retention policy is, etc.

  Sue

• A3Paper

  SSChasing Mays

  Points: 636

  More actions

  January 18, 2017 at 8:17 am

  #1923439

  Thank you Sue. Additional information is helpful.
  I've checked error logs for all instances but could not find anything. Backups go back 2 weeks - I'll leave this for now and keep an eye on this config for new instances in future.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 18, 2017 at 10:50 am

  #1923491

  A3Paper - Wednesday, January 18, 2017 8:17 AM

  Thank you Sue. Additional information is helpful.
  I've checked error logs for all instances but could not find anything. Backups go back 2 weeks - I'll leave this for now and keep an eye on this config for new instances in future.

  You may want to rethink some of the security if possible in the environment you have. And if you are having problems like this, I'd keep documenting them. Someone who is a sysadmin, serveradmin or can alter server settings should be more responsible and accountable for things like this. Doesn't sound like that's what you are working with and certainly justifies concern on your part.

  Sue