For years Microsoft SQL Server has served as a backbone of critical applications for enterprises.
Due to the nature of critical data stored on the SQL Server databases, it has always been a point of Interest for internal or external adversaries and one of the primary targets for exploitation.
It is important to monitor all your SQL database instances and servers for any sign of threats.
If you are running Azure SQL or Azure SQL Managed Instances or Azure Synapse Analytics, Azure Data Security (ADS) provides comprehensive database security in relation to Data Discovery and Classification, Vulnerability assessment and Advanced Threat Protection.
We recently released a public preview of ADS for SQL Machines to extend all the capabilities to SQL Server running on machines.
This post compliments the capabilities of ADS by enabling monitoring of SQL Server databases running on Windows Server VMs on premises or on Cloud IaaS by ingesting SQL Server Audit events into Azure Sentinel, build various custom threat hunting queries, correlate events and create alerts.
Let us get started.
Ingesting Logs from SQL Server
Step 1 – Enable audit on SQL server and create a policy Audit:
As a first step we need to enable auditing to track and log various types of events on the Server and Database levels. For this post, I have enabled these following specifications on my database.
- Database Role Member Change Group.
- Database Permission Change Group.
- Schema Object Permission Change Group.
- Database Principal Change Group.
- Schema Object Change Group.
- Schema Object Access Group
More information on enabling audit can be found here.
Step 2 – Write SQL Server Audit Events to the Security Log
One of the easiest ways of getting logs from SQL servers to your Azure Sentinel workspace is to write SQL Audit Events into Windows Security Events or Application Events. For this blog post, I am configuring my SQL Server to write audit events into Application logs of Windows events.
Step 3 – Sending logs from SQL Server to Azure Sentinel using Microsoft Monitoring Agent.
If you are writing SQL Audit events to Windows Security Events, you may use the Azure Sentinel Security Event Connector to collect the logs from the SQL Server system using the MMA Agent.
In this post, I am writing the SQL Audit events to the Windows Application log and hence it requires an additional step of collecting Application Log from the systems in the advanced Settings of your Log Analytics workspace.
PS: It is recommended to apply this configuration only to the systems from where we need these logs as MMA agent will collect all Application logs.
Once the MMA agent starts sending the events logs to the Azure Sentinel, you should see all the logs in the “Event” table.
| where Source has “MSSQL”
| project TimeGenerated, Source, Computer, EventID, RenderedDescription
All the critical information about these audit events is available in the RendererdDescription column including Action ID, Client IP, Current Logged on User, Database Name, Object Name and SQL Statement.
Action ID is a critical item which illustrates a specific activity on the SQL Server which you could choose to monitor.
Here are some of the Critical Action IDs recommend to review:
AL | ALTER |
CR | CREATE |
APRL | ADD MEMBER |
DPRL | DROP MEMBER |
DL | DELETE |
DR | DROP |
IMP | IMPERSONATE |
LGIF | LOGIN FAILED |
LGIS | LOGIN SUCCEEDED |
PWC | CHANGE PASSWORD |
PWPL | PASSWORD POLICY |
TO | TAKE OWNERSHIP |
Parsing the data
Now that we have our logs coming into Azure Sentinel, we need to parse the “RenderedDescription” field to provide relevant information for us to create and test threat hunting queries against.
// SQL Server
//
//
// Parser Notes:
// This parser works against the SQL Audit events being written to Application Log of Windows Events.
//
// Usage Instruction:
// Paste below query in log analytics, click on Save button and select as Function from drop down by specifying function name and alias (e.g. SQLEvent).
// Function usually takes 10-15 minutes to activate. You can then use function alias from any other queries (e.g. SQLEvent | take 10).
// References:
// Using functions in Azure monitor log queries :queries: https://docs.microsoft.com/azure/azure-monitor/log-query/functions
// Tech Community Blog on KQL Functions : https://techcommunity.microsoft.com/t5/Azure-Sentinel/Using-KQL-functions-to-speed-up-analysis-in-Azure-Sentinel/ba-p/712381
//
//
let SQlData = Event
| where Source has “MSSQL”
;
let Sqlactivity = SQlData
| where RenderedDescription !has “LGIS” and RenderedDescription !has “LGIF”
| parse RenderedDescription with * “action_id:” Action:string
” ” *
| parse RenderedDescription with * “client_ip:” ClientIP:string
” permission” *
| parse RenderedDescription with * “session_server_principal_name:” CurrentUser:string
” ” *
| parse RenderedDescription with * “database_name:” DatabaseName:string
“schema_name:” Temp:string
“object_name:” ObjectName:string
“statement:” Statement:string
“.” *
;
let FailedLogon = SQlData
| where EventLevelName has “error”
| where RenderedDescription startswith “Login”
| parse kind=regex RenderedDescription with “Login” LogonResult:string
“for user ‘” CurrentUser:string
“‘. Reason:” Reason:string
“provided” *
| parse kind=regex RenderedDescription with * “CLIENT” * “:” ClientIP:string
“]” *
;
let dbfailedLogon = SQlData
| where RenderedDescription has ” Failed to open the explicitly specified database”
| parse kind=regex RenderedDescription with “Login” LogonResult:string
“for user ‘” CurrentUser:string
“‘. Reason:” Reason:string
” ‘” DatabaseName:string
“‘” *
| parse kind=regex RenderedDescription with * “CLIENT” * “:” ClientIP:string
“]” *
;
let successLogon = SQlData
| where RenderedDescription has “LGIS”
| parse RenderedDescription with * “action_id:” Action:string
” ” LogonResult:string
“:” Temp2:string
“session_server_principal_name:” CurrentUser:string
” ” *
| parse RenderedDescription with * “client_ip:” ClientIP:string
” ” *
;
(union isfuzzy=true
Sqlactivity, FailedLogon, dbfailedLogon, successLogon )
| project TimeGenerated, Computer, EventID, Action, ClientIP, LogonResult, CurrentUser, Reason, DatabaseName, ObjectName, Statement
Save this as a KQL Function with the name SQLEvent(). More Information on using KQL functions can be found here
We can now call this function [ SQLevent() ] and project our relevant information extracted like Action, ClientIP, DatabaseName, Statement and more.
Hunting Queries
Next, we go hunting based on the information that we have parsed from the logs.
Here are a few examples of hunting queries which can also be used to create analytics rules for alert creation after further tuning.
All Failed Logons
| where LogonResult has “failed”
| summarize count() by CurrentUser, Reason
Notice the use of the parser where we have extracted the SQL statement into a separate column “Statement”. All of the actions made to the SQL server or the database are listed here. Now your hunting capabilities are enormous by simply parsing this field at query time. There is no need to tax the system by parsing it in the primary parser when there is no use case for it. This will allow you to get results more efficiently.
Here are some of examples of hunting for various anomalies based on the text in the “Statement” column.
New User Created
New user creation on SQL Server should be monitored for any suspicious patterns like, New User created by unauthorized accounts.
//
SQLEvent
| where Statement has “Create Login”
| parse Statement with “CREATE LOGIN [” TargetUser:string
“]” *
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
Here the CurrentUser is the current logged-on user who has created the new user account i.e TargetUser. We can easily extend this query to see if CurrentUser != “[Authorised User Name]” to find if the new user creation was done by non-authorized accounts.
User Role Altered in SQL
Also, monitoring changes made to user roles on SQL server can help with detecting anomalous privilege escalations.
//
SQLEvent
| where Statement contains “Alter role” and Statement has “add member”
| parse Statement with * “ADD MEMBER [” TargetUser:string
“]” *
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
User added in SecurityAdmin Role
Users being added to some of the critical groups of SQL server should be monitored for anomalous persistence and privilege escalation.
| where Statement has “Alter Server role” and Statement has “add member”
| parse Statement with * “ADD MEMBER [” TargetUser:string
“]” *
| where ObjectName has “securityadmin”
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
Removed User from Database
Large numbers of users being removed from the databases correlated with other changes like ALTER ROLE can help detect ongoing attacks on critical databases.
//
SQLEvent
| where Statement has “Alter role” and Statement has “drop member”
| parse Statement with * “DROP MEMBER [” TargetUser:string
“]” *
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
User Removed from ServerRole
| where Statement has “Alter Server role” and Statement has “drop member”
| parse Statement with * “DROP MEMBER [” TargetUser:string
“]” *
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
User removed from SecurityAdmin Role
| where Statement has “Alter Server role” and Statement has “drop member”
| parse Statement with * “DROP MEMBER [” TargetUser:string
“]” *
| where ObjectName has “securityadmin”
| project TimeGenerated, Computer, Action, ClientIP, CurrentUser, DatabaseName, TargetUser, ObjectName, Statement
You can now create similar hunting queries based on the information available in the Statement column with the combination of Action Ids like:
- Deletion of database
- Database Ownership changes
- New users becoming owners of large number of databases etc.
Incident Creation
Now, go ahead and create some Analytics rule for Incident creation.
Multiple Failed Logons in short span of time
// the timeframe and threshold can be changed below as per requirement
//
let TimeFrame = 10m;
let failedThreshold = 3;
SQLEvent
| where TimeGenerated > ago(TimeFrame)
| where LogonResult has “failed”
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), TotalFailedLogons = count() by CurrentUser
| where TotalFailedLogons >= failedThreshold
| project CurrentUser, TotalFailedLogons
Multiple Failed Logons by multiple accounts from same IP
// the timeframe and threshold can be changed below as per requirement.
//
let TimeFrame = 10m;
let failedThreshold = 3;
SQLEvent
| where TimeGenerated > ago(TimeFrame)
| where LogonResult has “failed”
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), TotalFailedLogons = count() by ClientIP, CurrentUser
| where TotalFailedLogons >= failedThreshold
| project ClientIP, TotalFailedLogons, CurrentUser
With this blog post, we are just scratching the surface with the kind of custom threat hunting possible on SQL Server environments with Azure Sentinel, hope this helps you to get started.
References
Hunting queries and detection queries are available on our GitHub community page.
Enable audit on SQL server and create a policy Audit:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15
Write Audit SQL log to Windows Event
Advanced data security for SQL machines (Preview)