28 November 2018

Monitor Unauthorized Changes to SQL Server Configuration Settings

by Redgate Monitor Team

4,754 0 Security

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

Reports the number of server or database configuration option changes in the past five minutes.

First, create the following Extended Event session on the monitored SQL Server instance:

IF EXISTS --if the session already exists, then delete it. We are assuming you've changed something
    (
    SELECT * FROM sys.server_event_sessions
      WHERE server_event_sessions.name = 'ConfigurationItemsChanged'
    )
    DROP EVENT SESSION ConfigurationItemsChanged ON SERVER;
  GO
  CREATE EVENT SESSION ConfigurationItemsChanged -- the name of the session 
  ON SERVER
    ADD EVENT sqlserver.error_reported --just the one event
      (ACTION
         (
         sqlserver.client_app_name, sqlserver.client_connection_id,
         sqlserver.database_name, sqlserver.nt_username, sqlserver.sql_text,
         sqlserver.username --all these are useful for tracking an error
         )
    WHERE (([error_number]=(15457)) OR ([error_number]=(5084)))
      )
    ADD TARGET package0.ring_buffer --we will rwrite it to a ring buffer targwet only
      (SET max_memory = (4096))
  WITH
    (
    MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = on
    );
  GO

  --and now we start the session
  ALTER EVENT SESSION ConfigurationItemsChanged ON SERVER STATE = START;
  GO

IF EXISTS --if the session already exists, then delete it. We are assuming you've changed something

(

SELECT * FROM sys.server_event_sessions

WHERE server_event_sessions.name = 'ConfigurationItemsChanged'

)

DROP EVENT SESSION ConfigurationItemsChanged ON SERVER;

CREATE EVENT SESSION ConfigurationItemsChanged -- the name of the session

ON SERVER

ADD EVENT sqlserver.error_reported --just the one event

(ACTION

(

sqlserver.client_app_name, sqlserver.client_connection_id,

sqlserver.database_name, sqlserver.nt_username, sqlserver.sql_text,

sqlserver.username --all these are useful for tracking an error

)

WHERE (([error_number]=(15457)) OR ([error_number]=(5084)))

)

ADD TARGET package0.ring_buffer --we will rwrite it to a ring buffer targwet only

(SET max_memory = (4096))

WITH

(

MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB,

MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = on

);

--and now we start the session

ALTER EVENT SESSION ConfigurationItemsChanged ON SERVER STATE = START;

Metric definition

Name

ConfigurationItemsChanged

Description

Reports the number of server or database configuration option changes in the past five minutes.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			SELECT Count(*) AS ErrorCount
  FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xes
      ON xes.address = xet.event_session_address
    CROSS APPLY
    (SELECT Cast(xet.target_data AS XML)) AS target_data_xml(xml)
    CROSS APPLY target_data_xml.xml.nodes('//RingBufferTarget/event') AS xed(event_data)
  WHERE xes.name = 'ConfigurationItemsChanged'
    AND xet.target_name = 'ring_buffer'
    AND DateDiff
         (MINUTE, 
         Convert(DATETIME2,
            SwitchOffset(
            Convert(DATETIMEOFFSET, xed.event_data.value('(@timestamp)[1]', 'datetime2')),
            DateName(TzOffset, SysDateTimeOffset())
                        )
            ), 
         GetDate()) < 5;
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
				
						SELECT Count(*) AS ErrorCount
  FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xes
      ON xes.address = xet.event_session_address
    CROSS APPLY
    (SELECT Cast(xet.target_data AS XML)) AS target_data_xml(xml)
    CROSS APPLY target_data_xml.xml.nodes('//RingBufferTarget/event') AS xed(event_data)
  WHERE xes.name = 'ConfigurationItemsChanged'
    AND xet.target_name = 'ring_buffer'
    AND DateDiff
         (MINUTE, 
         Convert(DATETIME2,
            SwitchOffset(
            Convert(DATETIMEOFFSET, xed.event_data.value('(@timestamp)[1]', 'datetime2')),
            DateName(TzOffset, SysDateTimeOffset())
                        )
            ), 
         GetDate()) < 5;

Instances to collect from

Default

Databases to collect from

master

Collection frequency

1 min

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Someone Changed a Server or Database Configuration Setting

Description

There have been more than 5 configuration changes in the last 5 minutes

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:
Medium:
Low:	5

Raise an alert when the threshold is passed for

collections

Alert is

Enabled

4,754 Rate

Click to rate this post!

[Total: 1 Average: 5]

Monitor Unauthorized Changes to SQL Server Configuration Settings

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics