Firewall errors stateful anomaly on database

let monitoredStatementsThreshold = 1;           // Minimal number of monitored statements in the slice to trigger an anomaly.
let trainingSlicesThreshold = 5;                // The maximal amount of slices with monitored statements in the training window before anomaly detection is throttled.
let timeSliceSize = 1h;                         // The size of the single timeSlice for individual aggregation.
let detectionWindow = 1h;                       // The size of the recent detection window for detecting anomalies.  
let trainingWindow = detectionWindow + 14d;     // The size of the training window before the detection window for learning the normal state.
let monitoredErrors = pack_array(40615);        // List of sql error codes relevant for this detection.
let processedData = materialize (
    AzureDiagnostics
    | where TimeGenerated >= ago(trainingWindow)
    | where Category == 'SQLSecurityAuditEvents' and action_id_s has_any ("RCM", "BCM") // Keep only SQL affected rows
    | project TimeGenerated, PrincipalName = server_principal_name_s, ClientIp = client_ip_s, HostName = host_name_s, ResourceId,
              ApplicationName = application_name_s, ActionName = action_name_s, Database = strcat(LogicalServerName_s, '/', database_name_s),
              IsSuccess = succeeded_s, AffectedRows = affected_rows_d,
              ResponseRows = response_rows_d, Statement = statement_s,
              Error = case( additional_information_s has 'error_code', toint(extract("<error_code>([0-9.]+)", 1, additional_information_s))
                    , additional_information_s has 'failure_reason', toint(extract("<failure_reason>Err ([0-9.]+)", 1, additional_information_s))
                    , 0),
              State = case( additional_information_s has 'error_state', toint(extract("<error_state>([0-9.]+)", 1, additional_information_s))
                    , additional_information_s has 'failure_reason', toint(extract("<failure_reason>Err ([0-9.]+), Level ([0-9.]+)", 2, additional_information_s))
                    , 0),
              AdditionalInfo = additional_information_s, timeSlice = floor(TimeGenerated, timeSliceSize)
    | summarize countEvents = count(), countStatements = dcount(Statement), countStatementsWithError = dcountif(Statement, Error in (monitoredErrors))
        , anyMonitoredStatement = anyif(Statement, Error in (monitoredErrors)), anyInfo = anyif(AdditionalInfo, Error in (monitoredErrors))
        by Database, ClientIp, ApplicationName, PrincipalName, timeSlice,HostName,ResourceId
    | extend WindowType = case( timeSlice >= ago(detectionWindow), 'detection',
                                           (ago(trainingWindow) <= timeSlice and timeSlice < ago(detectionWindow)), 'training', 'other')
    | where WindowType in ('detection', 'training'));
let trainingSet =
    processedData
    | where WindowType == 'training'
    | summarize countSlicesWithErrors = dcountif(timeSlice, countStatementsWithError >= monitoredStatementsThreshold)
        by Database;
processedData
| where WindowType == 'detection' 
| join kind = inner (trainingSet) on Database
| extend IsErrorAnomalyOnStatement = iff(((countStatementsWithError >= monitoredStatementsThreshold) and (countSlicesWithErrors <= trainingSlicesThreshold)), true, false)
    , anomalyScore = round(countStatementsWithError/monitoredStatementsThreshold, 0)
| where IsErrorAnomalyOnStatement == 'true'
| sort by anomalyScore desc, timeSlice desc
| extend Name = tostring(split(PrincipalName,'@',0)[0]), UPNSuffix = tostring(split(PrincipalName,'@',1)[0])    

status: Available
requiredDataConnectors:
- connectorId: AzureSql
  dataTypes:
  - AzureDiagnostics
triggerThreshold: 0
kind: Scheduled
version: 1.1.1
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
- entityType: IP
  fieldMappings:
  - identifier: Address
    columnName: ClientIp
- entityType: Host
  fieldMappings:
  - identifier: HostName
    columnName: HostName
- entityType: CloudApplication
  fieldMappings:
  - identifier: Name
    columnName: ApplicationName
- entityType: AzureResource
  fieldMappings:
  - identifier: ResourceId
    columnName: ResourceId
query: |
  let monitoredStatementsThreshold = 1;           // Minimal number of monitored statements in the slice to trigger an anomaly.
  let trainingSlicesThreshold = 5;                // The maximal amount of slices with monitored statements in the training window before anomaly detection is throttled.
  let timeSliceSize = 1h;                         // The size of the single timeSlice for individual aggregation.
  let detectionWindow = 1h;                       // The size of the recent detection window for detecting anomalies.  
  let trainingWindow = detectionWindow + 14d;     // The size of the training window before the detection window for learning the normal state.
  let monitoredErrors = pack_array(40615);        // List of sql error codes relevant for this detection.
  let processedData = materialize (
      AzureDiagnostics
      | where TimeGenerated >= ago(trainingWindow)
      | where Category == 'SQLSecurityAuditEvents' and action_id_s has_any ("RCM", "BCM") // Keep only SQL affected rows
      | project TimeGenerated, PrincipalName = server_principal_name_s, ClientIp = client_ip_s, HostName = host_name_s, ResourceId,
                ApplicationName = application_name_s, ActionName = action_name_s, Database = strcat(LogicalServerName_s, '/', database_name_s),
                IsSuccess = succeeded_s, AffectedRows = affected_rows_d,
                ResponseRows = response_rows_d, Statement = statement_s,
                Error = case( additional_information_s has 'error_code', toint(extract("<error_code>([0-9.]+)", 1, additional_information_s))
                      , additional_information_s has 'failure_reason', toint(extract("<failure_reason>Err ([0-9.]+)", 1, additional_information_s))
                      , 0),
                State = case( additional_information_s has 'error_state', toint(extract("<error_state>([0-9.]+)", 1, additional_information_s))
                      , additional_information_s has 'failure_reason', toint(extract("<failure_reason>Err ([0-9.]+), Level ([0-9.]+)", 2, additional_information_s))
                      , 0),
                AdditionalInfo = additional_information_s, timeSlice = floor(TimeGenerated, timeSliceSize)
      | summarize countEvents = count(), countStatements = dcount(Statement), countStatementsWithError = dcountif(Statement, Error in (monitoredErrors))
          , anyMonitoredStatement = anyif(Statement, Error in (monitoredErrors)), anyInfo = anyif(AdditionalInfo, Error in (monitoredErrors))
          by Database, ClientIp, ApplicationName, PrincipalName, timeSlice,HostName,ResourceId
      | extend WindowType = case( timeSlice >= ago(detectionWindow), 'detection',
                                             (ago(trainingWindow) <= timeSlice and timeSlice < ago(detectionWindow)), 'training', 'other')
      | where WindowType in ('detection', 'training'));
  let trainingSet =
      processedData
      | where WindowType == 'training'
      | summarize countSlicesWithErrors = dcountif(timeSlice, countStatementsWithError >= monitoredStatementsThreshold)
          by Database;
  processedData
  | where WindowType == 'detection' 
  | join kind = inner (trainingSet) on Database
  | extend IsErrorAnomalyOnStatement = iff(((countStatementsWithError >= monitoredStatementsThreshold) and (countSlicesWithErrors <= trainingSlicesThreshold)), true, false)
      , anomalyScore = round(countStatementsWithError/monitoredStatementsThreshold, 0)
  | where IsErrorAnomalyOnStatement == 'true'
  | sort by anomalyScore desc, timeSlice desc
  | extend Name = tostring(split(PrincipalName,'@',0)[0]), UPNSuffix = tostring(split(PrincipalName,'@',1)[0])      
relevantTechniques:
- T1190
name: Firewall errors stateful anomaly on database
tactics:
- InitialAccess
triggerOperator: gt
description: |
    'This query batches of distinct SQL queries that failed with error codes that might indicate malicious attempts to gain illegitimate access to the data. When attacker attempts to scan or gain access to server protected by firewall, he will be blocked by firewall and fail with error code 40615. Thus, if we see a large number of logins with such error codes, this could indicate attempts to gain access.'
queryPeriod: 14d
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure SQL Database solution for sentinel/Analytic Rules/Detection-ErrorsFirewallStatefulAnomalyOnDatabase.yaml
tags:
- SQL
queryFrequency: 1h
severity: Medium
id: 20f87813-3de0-4a9f-a8c0-6aaa3187be08