Credential errors stateful anomaly on database
| Id | daa32afa-b5b6-427d-93e9-e32f3f359dd7 |
| Rulename | Credential errors stateful anomaly on database |
| 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 Brute Force attacks are attempted, majority of logins will use wrong credentials, thus will fail with error code 18456. Thus, if we see a large number of logins with such error codes, this could indicate Brute Force attack. |
| Severity | Medium |
| Tactics | InitialAccess |
| Techniques | T1190 |
| Required data connectors | AzureSql |
| Kind | Scheduled |
| Query frequency | 1h |
| Query period | 14d |
| Trigger threshold | 0 |
| Trigger operator | gt |
| Source Uri | https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure SQL Database solution for sentinel/Analytic Rules/Detection-ErrorsCredentialStatefulAnomalyOnDatabase.yaml |
| Version | 1.1.1 |
| Arm template | daa32afa-b5b6-427d-93e9-e32f3f359dd7.json |
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(18456); // 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])
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 Brute Force attacks are attempted, majority of logins will use wrong credentials, thus will fail with error code 18456. Thus, if we see a large number of logins with such error codes, this could indicate Brute Force attack.'
triggerOperator: gt
requiredDataConnectors:
- dataTypes:
- AzureDiagnostics
connectorId: AzureSql
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(18456); // 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])
name: Credential errors stateful anomaly on database
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
kind: Scheduled
severity: Medium
relevantTechniques:
- T1190
tactics:
- InitialAccess
queryFrequency: 1h
tags:
- SQL
version: 1.1.1
queryPeriod: 14d
triggerThreshold: 0
status: Available
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure SQL Database solution for sentinel/Analytic Rules/Detection-ErrorsCredentialStatefulAnomalyOnDatabase.yaml
id: daa32afa-b5b6-427d-93e9-e32f3f359dd7
{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"workspace": {
"type": "String"
}
},
"resources": [
{
"apiVersion": "2024-01-01-preview",
"id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/daa32afa-b5b6-427d-93e9-e32f3f359dd7')]",
"kind": "Scheduled",
"name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/daa32afa-b5b6-427d-93e9-e32f3f359dd7')]",
"properties": {
"alertRuleTemplateName": "daa32afa-b5b6-427d-93e9-e32f3f359dd7",
"customDetails": null,
"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 Brute Force attacks are attempted, majority of logins will use wrong credentials, thus will fail with error code 18456. Thus, if we see a large number of logins with such error codes, this could indicate Brute Force attack.'\n",
"displayName": "Credential errors stateful anomaly on database",
"enabled": true,
"entityMappings": [
{
"entityType": "Account",
"fieldMappings": [
{
"columnName": "Name",
"identifier": "Name"
},
{
"columnName": "UPNSuffix",
"identifier": "UPNSuffix"
}
]
},
{
"entityType": "IP",
"fieldMappings": [
{
"columnName": "ClientIp",
"identifier": "Address"
}
]
},
{
"entityType": "Host",
"fieldMappings": [
{
"columnName": "HostName",
"identifier": "HostName"
}
]
},
{
"entityType": "CloudApplication",
"fieldMappings": [
{
"columnName": "ApplicationName",
"identifier": "Name"
}
]
},
{
"entityType": "AzureResource",
"fieldMappings": [
{
"columnName": "ResourceId",
"identifier": "ResourceId"
}
]
}
],
"OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure SQL Database solution for sentinel/Analytic Rules/Detection-ErrorsCredentialStatefulAnomalyOnDatabase.yaml",
"query": "let monitoredStatementsThreshold = 1; // Minimal number of monitored statements in the slice to trigger an anomaly.\nlet trainingSlicesThreshold = 5; // The maximal amount of slices with monitored statements in the training window before anomaly detection is throttled.\nlet timeSliceSize = 1h; // The size of the single timeSlice for individual aggregation.\nlet detectionWindow = 1h; // The size of the recent detection window for detecting anomalies. \nlet trainingWindow = detectionWindow + 14d; // The size of the training window before the detection window for learning the normal state.\nlet monitoredErrors = pack_array(18456); // List of sql error codes relevant for this detection.\nlet processedData = materialize (\n AzureDiagnostics\n | where TimeGenerated >= ago(trainingWindow)\n | where Category == 'SQLSecurityAuditEvents' and action_id_s has_any (\"RCM\", \"BCM\") // Keep only SQL affected rows\n | project TimeGenerated, PrincipalName = server_principal_name_s, ClientIp = client_ip_s, HostName = host_name_s, ResourceId,\n ApplicationName = application_name_s, ActionName = action_name_s, Database = strcat(LogicalServerName_s, '/', database_name_s),\n IsSuccess = succeeded_s, AffectedRows = affected_rows_d,\n ResponseRows = response_rows_d, Statement = statement_s,\n Error = case( additional_information_s has 'error_code', toint(extract(\"<error_code>([0-9.]+)\", 1, additional_information_s))\n , additional_information_s has 'failure_reason', toint(extract(\"<failure_reason>Err ([0-9.]+)\", 1, additional_information_s))\n , 0),\n State = case( additional_information_s has 'error_state', toint(extract(\"<error_state>([0-9.]+)\", 1, additional_information_s))\n , additional_information_s has 'failure_reason', toint(extract(\"<failure_reason>Err ([0-9.]+), Level ([0-9.]+)\", 2, additional_information_s))\n , 0),\n AdditionalInfo = additional_information_s, timeSlice = floor(TimeGenerated, timeSliceSize)\n | summarize countEvents = count(), countStatements = dcount(Statement), countStatementsWithError = dcountif(Statement, Error in (monitoredErrors))\n , anyMonitoredStatement = anyif(Statement, Error in (monitoredErrors)), anyInfo = anyif(AdditionalInfo, Error in (monitoredErrors))\n by Database, ClientIp, ApplicationName, PrincipalName, timeSlice,HostName,ResourceId\n | extend WindowType = case( timeSlice >= ago(detectionWindow), 'detection',\n (ago(trainingWindow) <= timeSlice and timeSlice < ago(detectionWindow)), 'training', 'other')\n | where WindowType in ('detection', 'training'));\nlet trainingSet =\n processedData\n | where WindowType == 'training'\n | summarize countSlicesWithErrors = dcountif(timeSlice, countStatementsWithError >= monitoredStatementsThreshold)\n by Database;\nprocessedData\n| where WindowType == 'detection' \n| join kind = inner (trainingSet) on Database\n| extend IsErrorAnomalyOnStatement = iff(((countStatementsWithError >= monitoredStatementsThreshold) and (countSlicesWithErrors <= trainingSlicesThreshold)), true, false)\n , anomalyScore = round(countStatementsWithError/monitoredStatementsThreshold, 0)\n| where IsErrorAnomalyOnStatement == 'true'\n| sort by anomalyScore desc, timeSlice desc\n| extend Name = tostring(split(PrincipalName,'@',0)[0]), UPNSuffix = tostring(split(PrincipalName,'@',1)[0])\n",
"queryFrequency": "PT1H",
"queryPeriod": "P14D",
"severity": "Medium",
"status": "Available",
"subTechniques": [],
"suppressionDuration": "PT1H",
"suppressionEnabled": false,
"tactics": [
"InitialAccess"
],
"tags": [
"SQL"
],
"techniques": [
"T1190"
],
"templateVersion": "1.1.1",
"triggerOperator": "GreaterThan",
"triggerThreshold": 0
},
"type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
}
]
}