Blog Post

Microsoft Sentinel Blog
8 MIN READ

Monitoring SQL Server with Azure Sentinel

Iftekhar Hussain's avatar
Jul 02, 2020

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.

 

  1. Database Role Member Change Group.
  2. Database Permission Change Group.
  3. Schema Object Permission Change Group.
  4. Database Principal Change Group.
  5. Schema Object Change Group.
  6. 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.

 

 

Event
| 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.

 

 

// KQL SQL Audit Event Parser
// 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

 

 

SQLEvent
| 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.

 

 

//This query checks for new user account created on SQL Server using the SQLEvent() parser
//
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.

 

 

// This query looking for Alter role commands and extracts username which was altered and target objectName
//
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.

 

 

SQLEvent
| 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.

 

 

// This query checks for user removed from a database by parsing the statement field at the query time.
//
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

 

 

SQLEvent
| 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

 

 

SQLEvent
| 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:

  1. Deletion of database
  2. Database Ownership changes
  3. 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

 

 

//This detection rules checks for multiple failed logon attempts within 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

 

 

//This detection rules checks for multiple failed logon attempts from same IP within 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 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

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/write-sql-server-audit-events-to-the-security-log?view=sql-server-ver15

 

Advanced data security for SQL machines (Preview)

https://docs.microsoft.com/en-us/azure/security-center/security-center-iaas-advanced-data#set-up-ads-for-iaas

 

 

 

Updated Nov 03, 2021
Version 7.0
  • rengaray's avatar
    rengaray
    Brass Contributor

    Great, thanks for the heads up on use of Azure Sentinel along with SQL Server. Appreciate it. Will explore and revert.

  • Magnus123's avatar
    Magnus123
    Copper Contributor

    Shouln´t SQL audit´s be sent to Security log instead of Application log?

  • Ciyaresh's avatar
    Ciyaresh
    Brass Contributor

    Hello,

     

    I'm trying to apply this function to SQL logs that arrive in WindowsEvent table because we use WEF. I have made some changes to reflect this but having struggles with parsing the fields. If you look at the screenshot below, you can see we should use Provider contains "MSSQL" instead of Source contains "MSSQL". Also actual event details land in EventData instead of RenderedDescription. However, there is a problem here. EventData contains "01" row?column? and I'm not able to escape this but also starts with "audit_scheme_version" rather than "audit event" like in the preparsed log example you showed. Any ideas how I can get this work? 🙂