Azure Sentinel News
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
  • Security and Compliance
  • SOC
  • Threat Intelligence
  • Security Ochestration & Automated Response
  • SOAR
  • Security Operations
  • Artificial Intelligence
No Result
View All Result
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
  • Security and Compliance
  • SOC
  • Threat Intelligence
  • Security Ochestration & Automated Response
  • SOAR
  • Security Operations
  • Artificial Intelligence
No Result
View All Result
Azure Sentinel News
No Result
View All Result
Home Security Operations

Monitoring SQL Server with Azure Sentinel

Azure Sentinel News Editor by Azure Sentinel News Editor
December 24, 2020
in Security Operations
0
Microsoft Acquires CyberX to Improve Azure IoT Security
2.4kViews
127 Shares Share on Facebook Share on Twitter

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.

Reference:https://techcommunity.microsoft.com/t5/azure-sentinel/monitoring-sql-server-with-azure-sentinel/ba-p/1502960

Tags: Azure SentinelDatabase MonitoringDetections.SQL SeverSQL ThreatsThreat hunting
Azure Sentinel News Editor

Azure Sentinel News Editor

Related Posts

What’s new: Microsoft Teams connector in Public Preview
Security Operations

AMA for Azure Sentinel on the Microsoft Security Insights Podcast and Twitch Stream

January 25, 2021
What’s new: Microsoft Teams connector in Public Preview
Security Operations

How to Setup a Managed Identity for the Azure Sentinel Logic App Connector

January 21, 2021
Microsoft suspends 18 Azure accounts tied to China-based hackers
Security Operations

Azure Sentinel Daily Task: Hunting Queries and Bookmarks

January 1, 2021
Next Post
Open Systems Augments its Cybersecurity Capabilities With Acquisition of Leading Microsoft Azure Sentinel Expert

New Azure Sentinel notebook experience and the retirement of the Azure Notebooks service preview

Azure Stack and Azure Arc for data services from Blog Posts – SQLServerCentral

How to Protect Office 365 with Azure Sentinel

Vectra AI and Microsoft partner on security integration

Azure Sentinel Learning Path Now Available

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Follow Us

  • 21.8M Fans
  • 81 Followers

Recommended

Microsoft acquires CyberX to accelerate and secure customers’ IoT deployments

Microsoft acquires CyberX to accelerate and secure customers’ IoT deployments

4 months ago
Microsoft is quietly becoming a cybersecurity powerhouse

What’s New: 80 out of the box hunting queries!

3 months ago
RiskIQ Joins Microsoft Intelligent Security Association

RiskIQ Joins Microsoft Intelligent Security Association

3 months ago
CRITICALSTART Adds Support for Microsoft Azure Sentinel to MDR Services

Azure Sentinel Sigma & SOC Prime Integration (Part 2): Directly deploy to Azure Sentinel

3 months ago

Instagram

    Please install/update and activate JNews Instagram plugin.

Categories

  • AI & ML
  • Artificial Intelligence
  • Incident Response
  • IR
  • KQL
  • Security and Compliance
  • Security Ochestration & Automated Response
  • Security Operations
  • SIEM
  • SOAR
  • SOC
  • Threat Intelligence
  • Uncategorized

Topics

anomaly automation Azure Azure DevOps Azure Security Center Azure Sentinel Azure Sentinel API Azure Sentinel Connector BlueVoyant Call cybersecurity Detection file GitHub Hunting Huntingy IAC incident response Incident Triage infrastructure as code Investigation jupyter LAQueryLogs MDR Microsoft microsoft 365 mssp Multitenancy Notebooks Pester Playbooks PowerShell python Records Security Sentinel Sharing SIEM signin Supply Chain teams Threat hunting Watchlists Workbooks XDR
No Result
View All Result

Highlights

The Holy Grail of Azure Sentinel Data Connections: The Azure Service Diagnostic Setting

New Items of Note on the Azure Sentinel GitHub Repo

Tuning the MCAS Analytics Rule for Azure Sentinel: System Alerts and Feature Deprecation

New Search Capability for Azure Sentinel Incidents

Follow-up: Microsoft Tech Talks Practical Sentinel : A Day in the Life of a Sentinel Analyst

Changes in How Running Hunting Queries Works in Azure Sentinel

Trending

What’s new: Microsoft Teams connector in Public Preview
AI & ML

Azure Sentinel Weekly Newsletter

by Azure Sentinel News Editor
March 1, 2021
0

I’ve sensed this for a while now, but a few days ago it really hit me —...

What’s new: Microsoft Teams connector in Public Preview

How to Generate Azure Sentinel Incidents for Testing

February 26, 2021
What’s new: Microsoft Teams connector in Public Preview

Azure Sentinel Notebooks Loses It’s Preview Tag

February 25, 2021
Microsoft’s newest sustainable datacenter region coming to Arizona in 2021

The Holy Grail of Azure Sentinel Data Connections: The Azure Service Diagnostic Setting

February 22, 2021
Microsoft’s newest sustainable datacenter region coming to Arizona in 2021

New Items of Note on the Azure Sentinel GitHub Repo

February 18, 2021

We bring you the best, latest and perfect Azure Sentinel News, Magazine, Personal Blogs, etc. Visit our landing page to see all features & demos.
LEARN MORE »

Recent News

  • Azure Sentinel Weekly Newsletter March 1, 2021
  • How to Generate Azure Sentinel Incidents for Testing February 26, 2021
  • Azure Sentinel Notebooks Loses It’s Preview Tag February 25, 2021

Categories

  • AI & ML
  • Artificial Intelligence
  • Incident Response
  • IR
  • KQL
  • Security and Compliance
  • Security Ochestration & Automated Response
  • Security Operations
  • SIEM
  • SOAR
  • SOC
  • Threat Intelligence
  • Uncategorized

[mc4wp_form]

Copyright © 2020 - Azure Sentinel News

No Result
View All Result
  • Home
  • Security and Compliance
  • SOC
  • Threat Intelligence
  • Security Ochestration & Automated Response
  • SOAR
  • Security Operations
  • Artificial Intelligence

Copyright © 2020 Azure Sentinel News