14. 03. 2025 Daniel Degasperi Blue Team, Log-SIEM, SEC4U

A Practical Approach to Detect Suspicious Activity in MS SQL Server

This article gives an overview and offers a practical tips to detecting some suspicious activities in Microsoft SQL Server, from configuring audit policies to leveraging Elastic for effective monitoring and threat detection.

Introduction

Microsoft SQL Server is one of the most widely used relational databases in the enterprise landscape, managing critical data and supporting essential business applications. However, like any exposed system, SQL Server can be a target for cyber-attacks, including unauthorized access, data exfiltration, and malicious manipulation.

To ensure data security and promptly detect suspicious activities, it is essential to properly configure auditing policies at both the operating system (Windows) and SQL Server levels. Additionally, integrating monitoring solutions like Elastic allows for real-time log analysis, anomaly detection, and rapid threat response.

Configuring Security and Audit Policies

To collect SQL Server Audit events from the Windows Event Log Security channel, SQL Server must be configured to send audit events to that channel. This process requires configuring a SQL Server audit with a target set to the Windows Security registry.

SQL Server Audit Components

The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance.
When you define an audit, you specify the location for the output of the results. This is the audit destination. The audit is created in a disabled state, and doesn’t automatically audit any actions. After the audit is enabled, the audit destination receives data from the audit.

The first step is therefore to create and configure a SQL Server Audit with the destination set to the Windows Security log.

Security > Audit

Audit name: Provide a name for the audit, e.g., AuditToSecurityLog.
Queue Delay: Set the queue delay (e.g., 1000 ms).
Audit Destination: Select Security log (this will send events to the Windows Security Event Log).

Info: Authentication events (such as failed logins or logins) are typically captured at the Server Audit Specification level, not at the Database Audit Specification level

Then we have to configure the Server Audit Specification via Security > Server Audit Specifications

Name: Provide a name for the specification, e.g., DatabaseLoginAudit.
Audit: Select the audit created in the previous step (e.g., AuditToSecurityLog).
Action Types: Specify the actions to monitor.
1. SUCCESSFUL_LOGIN_GROUP
2. FAILED_LOGIN_GROUP
3. SERVER_ROLE_MEMBER_CHANGE_GROUP
4. SERVER_OPERATION_GROUP
5. SERVER_ROLE_MEMBER_CHANGE_GROUP
6. AUDIT_CHANGE_GROUP
7. SCHEMA_OBJECT_EXECUTE_GROUP
8. SCHEMA_OBJECT_ACCESS_GROUP
9. BACKUP_RESTORE_GROUP

Database Audit

The Database Audit Specification object also belongs to a SQL Server audit. You can create one database audit specification per SQL Server database per audit.

Security > Database Audit Specification

Name: Provide a name for the specification, e.g., DatabaseLoginAudit.
Audit: Select the audit created in the previous step (e.g., AuditToSecurityLog).
Action Types: Specify the actions to monitor.
1. DATABASE_LOGOUT_GROUP
2. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
3. FAILED_DATABASE_AUTHENTICATION_GROUP
4. AUDIT_CHANGE_GROUP
5. DATABASE_OPERATION_GROUP
6. DATABASE_CHANGE_GROUP
7. SCHEMA_OBJECT_CHANGE_GROUP

Target – Windows Security Events Log

SQL Server needs the necessary permissions to write to the Security log. So let’s proceed with the configuration of these permissions.

We have to modify the Local Security Policy and enable (for both Success and Failure) the Audit object access security policy.

SQL Server sends audit events to the security channel with event ID 33205, so it is this event ID that we are interested in.

Detection with Elastic

For this analysis I decided to consider some different actions but potentially related to the same scenario.

Deleting a table and a database, therefore in a malicious context an Impact and Data Destruction event (TA0040 – T1485), then the dump of the table and database itself (Exfiltration TA0010), and finally the execution of operating system commands directly from within the database (Command and Scripting Interpreter: Windows Command Shell – T1059.003).

To detect audit events and therefore suspicious transactions of the MS SQL SERVER, we use the Elastic Microsoft SQL Server Integration, capable of recovering Windows event 33205, precisely relating to the SQL audit.

Data Destruction – T1485

Detecting suspicious transaction events is quite simple using simple custom rules with a query like this:

event.code: "33205" 
AND winlog.provider_name : MSSQL*
AND NOT sqlserver.audit.session_server_principal_name: *$
AND sqlserver.audit.class_type: ("TABLE" OR "DATABASE")
AND sqlserver.audit.statement:(TRUNCATE* OR DROP* OR BACKUP*)

The query proposed above actually also covers the case of transactions related to backup execution, but since the logic is the same it makes sense to group the cases into a single query.

Clearly, a detection of this type must be contextualized and tuned to the target. Sys admins could perform transactions of this type regularly.
But it is also clear that these are potentially extremely dangerous and damaging activities.

Exfiltration – TA0010

As for the dump collection (both of single tables and entire databases) and then the data exfiltration, I simulated the activity directly through SQL queries and through PowerShell, considering in this case the logs provided by the Microsoft-Windows-PowerShell/Operational channel.

Table dump

Via PowerShell you could use for example this command :

$bcpCommand = "bcp db_test_1.dbo.tst1_Table_3 out 'C:\Users\[...]\tmp\users_dump.csv' -c -t -T -S TESTSQL"
Invoke-Expression $bcpCommand

In this case I use -T parameter, to use Windows Authentication (Integrated Security). The command will use the credentials of the user currently logged on to the system.

The table’s dump will be saved in the tmp folder specified

Here the Elastic log related to the PowerShell command execution, and the query to detect it

event.code:4104 
AND event.dataset:"windows.powershell_operational" 
AND  powershell.file.script_block_text: "bcp" 
AND powershell.file.script_block_text: "out"
AND powershell.file.script_block_text: "-S"

Database dump

What if I wanted to dump an entire database? An even more realistic and critical eventuality!

The dump can be done directly from the SQL SERVER for example via query …

… or via PowerShell using the Invoke-Sqlcmd command

Also in this case the command uses the Integrated Windows Authentication.

The Elastic log and the detection query

event.code:4104 
AND event.dataset:"windows.powershell_operational" 
AND powershell.file.script_block_text: *Invoke-Sqlcmd* 
AND powershell.file.script_block_text: *-Query* 
AND powershell.file.script_block_text: "BACKUP DATABASE"
AND powershell.file.script_block_text: *-ServerInstance* 
AND powershell.file.script_block_text:*-Database*

Defense Evasion – TA0005

Another very important aspect is the ability to detects when an attacker modifies or removes audit logs on the server, aiming to disable the traceability of malicious activities.

For this purpose we can evaluate the execution of “ALTER SERVER/DATABASE AUDIT” or “DROP SERVER/DATABASE AUDIT” transactions.

Here we can see the disabling of the Server Audit Specification .

Also for this we use a simple but effective custom query:

event.code:33205 
AND winlog.provider_name : MSSQL*
AND sqlserver.audit.statement:
(
"ALTER SERVER AUDIT SPECIFICATION" OR 
"ALTER DATABASE AUDIT SPECIFICATION"
)

Persistence – TA0003

The goal in this case is to detect when an attacker tries to backdoor the MSSQL server by adding a backdoor account to the sysadmin fixed server role.

In this case it’s necessary to configure the SERVER AUDIT SPECIFICATION to monitor group changes, server setting changes, and audit setting changes

-- track group changes
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP), 
-- track server setting changes
ADD (AUDIT_CHANGE_GROUP)

CREATE LOGIN TestUser WITH PASSWORD = 'StrongPassword123';
ALTER SERVER ROLE [sysadmin] ADD MEMBER [TestUser];

Elastic log and detection query :

event.code:33205 
AND winlog.provider_name : MSSQL*
AND sqlserver.audit.object_name: "sysadmin"
AND sqlserver.audit.statement: ("alter server role [sysadmin] add member ")

Command and Scripting Interpreter: Windows Command Shell – T1059.003

Extremely critical aspect… the possibility to execute operating system commands directly from within the database.
If an attacker gains unauthorized access to the database, they could leverage xp_cmdshell to escalate privileges, compromise the system, or exfiltrate sensitive data.

As you can see, it is possible to run the executable associated with the Windows calculator in background… in this case, nothing dangerous 🙂 .
It is a best practice not to have this option active, but in any case with the right rights it is easily activated.

# xp_cmdshell

-- enable advanced options
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
-- enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
-- hide advanced options
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

To collect this kind of events it’s necessary to configure a server audit specification with the SCHEMA_OBJECT_ACCESS_GROUP. This action group at the server level is activated when an object, like xp_cmdshell, is accessed using a permission.

The corresponding log in elastic and a possible detection query :

event.code:33205  
AND event.action: "execute-stored-proc-or-function" 
AND sqlserver.audit.statement: "EXEC"
AND sqlserver.audit.class_type: "STORED PROCEDURE EXTENDED"
AND sqlserver.audit.object_name: "xp_cmdshell"

Final Considerations

“The purpose of this article was to provide a comprehensive overview of the key activities that are both possible and valuable to monitor in the context of Microsoft SQL Server security. While the scenarios discussed offer a solid foundation for understanding potential risks and detection strategies, it’s important to acknowledge that the threat landscape is constantly evolving.

For instance, beyond the examples covered, there are additional scenarios worth exploring, such as advanced techniques for Lateral Movement leveraging the Command and Scripting Interpreter. Investigating how attackers might abuse SQL Server functionalities to pivot within a network can reveal further detection opportunities.

Moreover, future developments could include monitoring for anomalous backup operations, unauthorized data exports, or the misuse of extended stored procedures. Each of these vectors represents a potential pathway for malicious activity, emphasizing the importance of continuous refinement in monitoring strategies.

In conclusion, staying proactive and adaptive in detection approaches is crucial for maintaining robust SQL Server security.”