10 September 2018

Detect Suspicious Errors Typical of a SQL Injection Attack

by Redgate Monitor Team

8,107 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.

Detects errors characteristic of a possible SQL Injection attack, or other attempts to gain illicit access to a database server. Returns the number of errors detected in the last 20 minutes.

First, create an Extended Events session on the monitored SQL Server instance:

IF EXISTS
    (SELECT *
       FROM sys.server_event_sessions
       WHERE name = 'MonitorSuspiciousErrors')
  DROP EVENT SESSION MonitorSuspiciousErrors ON SERVER;
  GO

CREATE EVENT SESSION MonitorSuspiciousErrors
    ON SERVER
    ADD EVENT sqlserver.error_reported --the event we are interested in
      (ACTION --the general global fields ('actions') we want to receive
         (sqlserver.client_app_name,
          sqlserver.client_connection_id,
          sqlserver.database_name,
          sqlserver.nt_username,
          sqlserver.sql_text,
          sqlserver.username)
       WHERE --the filters that we want to use so and to get just the relevant errors
       error_number = (102)
       OR error_number = (105)
       OR error_number = (205)
       OR
         (error_number = (207)
          OR error_number = (208)
          OR error_number = (245)
          OR error_number = (2812)
          OR error_number = (18456)
          OR error_number = (15281)
          OR sqlserver.like_i_sql_unicode_string(message, N'%permission%')
          OR sqlserver.like_i_sql_unicode_string(message, N'%denied%')))
    ADD TARGET package0.ring_buffer --define our data storage target
    WITH --all the optional parameters.
      (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

IF EXISTS

(SELECT *

FROM sys.server_event_sessions

WHERE name = 'MonitorSuspiciousErrors')

DROP EVENT SESSION MonitorSuspiciousErrors ON SERVER;

CREATE EVENT SESSION MonitorSuspiciousErrors

ON SERVER

ADD EVENT sqlserver.error_reported --the event we are interested in

(ACTION --the general global fields ('actions') we want to receive

(sqlserver.client_app_name,

sqlserver.client_connection_id,

sqlserver.database_name,

sqlserver.nt_username,

sqlserver.sql_text,

sqlserver.username)

WHERE --the filters that we want to use so and to get just the relevant errors

error_number = (102)

OR error_number = (105)

OR error_number = (205)

(error_number = (207)

OR error_number = (208)

OR error_number = (245)

OR error_number = (2812)

OR error_number = (18456)

OR error_number = (15281)

OR sqlserver.like_i_sql_unicode_string(message, N'%permission%')

OR sqlserver.like_i_sql_unicode_string(message, N'%denied%')))

ADD TARGET package0.ring_buffer --define our data storage target

WITH --all the optional parameters.

(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);

Start the event session, like this:

ALTER EVENT SESSION MonitorSuspiciousErrors ON SERVER STATE = START;

1	ALTER EVENT SESSION MonitorSuspiciousErrors ON SERVER STATE = START;

Metric definition

Name

SuspiciousErrors

Description

Detects errors characteristic of a possible SQL Injection attack, or other attempts to gain illicit access to a database server. Returns the number of errors detected in the last 20 minutes.

The T-SQL query that will collect data


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

Instances to collect from

Select all

Databases to collect from

master

Collection frequency

5 mins

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

SuspiciousErrors

Description

Alert fires if the number of suspicious errors detected exceeds the defined threshold.

Raise an alert when the metric value goes

above the defined threshholds

Default threshold values

High:
Medium:	30
Low:

Raise an alert when the threshold is passed for

collections

Alert is

Enabled

8,107 Rate

Click to rate this post!

[Total: 5 Average: 4.8]

Detect Suspicious Errors Typical of a SQL Injection Attack

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