How to Monitoring MSSQL Security with Wazuh

[Juan Ferdinan]

Hello Wazuh Teams

Is wazuh able to monitor all user activities carried out in the MSSQL database? Please help to provide how to do it.

Thanks & Regards

Juan

[Anthony Faruna]

Hello Juan

you can collect MSSQL audit logs through Windows event channels.

MSSQL logs are typically logged under the Security event channel, which is part of the default configuration of a Wazuh agent.

For instance, you can create a rule to alert when the Windows event ID 33205 (which related to MSSQL audit logs) has triggered in the system by adding the following rule to /var/ossec/etc/rules/local_rules.xml in your Wazuh manager side:

<group name="MSSQL,">

<rule id="100002" level="3"> <if_sid>60003</if_sid> <field name="win.system.eventid">^33205</field> <description>MSSQL audit log</description> </rule>
</group>

Don't forget to restart your Wazuh manager after the change.

If you want to learn more about how to add Windows event channel configuration to your Wazuh agents you can refer to: https://documentation.wazuh.com/current/user-manual/capabilities/log-data-collection/how-to-collect-wlogs.html

You can learn more about Wazuh rules and decoders here: https://documentation.wazuh.com/current/user-manual/ruleset/

Best Regards

--
You received this message because you are subscribed to the Google Groups "Wazuh mailing list" group.
To unsubscribe from this group and stop receiving emails from it, send an email to wazuh+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/b88a42c7-f873-45fe-a79b-2904964acef3n%40googlegroups.com.

[Juan Ferdinan]

Hi Anthony

Is there a requirement in MSSQL, for example, the audit trail must be on?

[Anthony Faruna]

Hello Juan

Please can you clarify what you mean by audit trail ?

Are you referring to the event logs?

My understanding is that MYSQL audit logs are forwarded to Windows event channels 

You can read more on this configuration at  https://documentation.wazuh.com/current/user-manual/capabilities/log-data-collection/how-to-collect-wlogs.html

Best Regards

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/77d02a2f-0937-4c3b-8283-fdbdf4600502n%40googlegroups.com.

[Juan Ferdinan]

Hi Anthony

I'm sorry I just responded to your answer, I hope you still want to help me.

I've tried to browse the MSSQL audit log storage and it turns out that the storage has been changed to drive D:\ (attached).

How can wazuh get the information in it?

Thanks and Regards

Juan

[Juan Ferdinan]

Hi Everyone

can anyone help me?

Thanks & Regards

Juan

[Anthony Faruna]

Hello Juan 

Please give me sometime to provide response to this new requirement 

Best Regards

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/4aee4bff-5e4d-459f-a466-6eb79f48564cn%40googlegroups.com.

[Juan Ferdinan]

Hi Anthony

Thank you

Thanks & Regards

Randy

[Anthony Faruna]

Hello Juan

Please confirm that these logs are been collected by Windows Events as there several options for collecting the audits logs

Windows events can be gathered and forwarded to the manager, where they are processed and alerted if they match any rule. There are two formats to collect Windows logs:

Eventlog (supported by every Windows version)

Eventchannel (for Windows Vista and later versions)

Also, please confirm if the requirement is to monitor the audit storage location for file modification activities?

Best regards

[Juan Ferdinan]

Hi Anthony

Here is the current MS SQL audit log configuration.

Based on my analysis, the logs are not collected to the windows event but are stored in the file that I attached before. Do I have to change the audit destination to the application log first, so that Wazuh can read the contents of the audit log activity?

 Or maybe wazuh can read the contents of the audit log activity that is formed and stored on a specific path that I have attached before?

Thanks & Regards

Juan

[Anthony Faruna]

Hello Juan

You can monitor MS SQL logs using the following configuration on your server, this will read logs/events from the log file specified in the location property.

<localfile>
    <location>C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG</location>
    <log_format>mssql_log</log_format>
    <ignore_binaries>yes</ignore_binaries>
</localfile>

Please modify the location to reflect the location of where the logs are stored in your environment

Also, Wazuh have some rules to work with these events, you can check them here

Also, you can check more on how to configure local file at https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/localfile.html#log-format

Please let me know if you have any questions

Best Regards

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/8550efeb-c828-405b-98e8-310194e4043an%40googlegroups.com.

[Juan Ferdinan]

Hi Anthony

where should i add the configuration, on wazuh-manager server or wazuh-agent server?

Thanks & Regards

Juan

[Anthony Faruna]

Hello Juan

My sincere apologies for not specifying where you will add it.

You will add it on the agent ossec.conf file

Best Regards

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/7574e00c-eaab-4da2-8d2d-707a1cc883b7n%40googlegroups.com.

[Juan Ferdinan]

Hi Anthony

I'm sorry I was only able to try again, because there is some work that must be completed first, I hope you still want to help me with this problem.

I've managed to create an audit log from MSSQL put in an Application event and here's an example of the resulting log

But why can't the logs be sent and received by the wazuh manager server, the only thing on the wazuh manager server is login and log out activities

[Anthony Faruna]

Hello Juan

If you go back to my first reply to your email, I actually detailed the steps to follow if you are collecting the logs via Windows Eventchannel.

However you replied that you are storing the logs in a location and I gave you another option for the scenario

From your recent email and the screenshot, you have reverted to using eventchannel and this enabled by default however you need to create rules to trigger the events on the dashboard.

For instance, you can create a rule to alert when the Windows event ID 33205 (which related to MSSQL audit logs) has triggered in the system by adding the following rule to /var/ossec/etc/rules/local_rules.xml in your Wazuh manager side:

<group name="MSSQL,">

<rule id="100002" level="3"> <if_sid>60003</if_sid> <field name="win.system.eventid">^33205</field> <description>MSSQL audit log</description> </rule>
</group>

Don't forget to restart your Wazuh manager after the change.

If you want to learn more about how to add Windows event channel configuration to your Wazuh agents you can refer to: https://documentation.wazuh.com/current/user-manual/capabilities/log-data-collection/how-to-collect-wlogs.html

You can learn more about Wazuh rules and decoders here: https://documentation.wazuh.com/current/user-manual/ruleset/

Best Regards

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/d9db8631-b2ba-4df7-ac1e-988c4a2f9eaen%40googlegroups.com.

[Juan Ferdinan]

Hi Anthony

thanks you still want to help me

I have made the rule you asked for and still haven't managed to get the desired log

I also checked the wazuh agent activity log on the target server, there is the following error, is this error related to the problem I'm facing?

[Juan Ferdinan]

Hi Anthony

is there any answer about this?

[Anthony Faruna]

Hello Juan 

Rule id 60003 triggers for audit success event however the events you are trying to monitor are informational events

Please check our rule set to monitor application event channel  at https://github.com/wazuh/wazuh-ruleset/blob/master/rules/0585-win-application_rules.xml

Please try out this configuration and let me know  the outcome 

<group name="MSSQL,">

<rule id="100002" level="3"> <if_sid>60600</if_sid>

<field name="win.system.eventid">^33205</field> <description>MSSQL audit log</description> </rule>
</group>

Best Regards

To view this discussion on the web visit https://groups.google.com/d/msgid/wazuh/d1dc7423-7f2f-40c9-b417-0ae885b852ecn%40googlegroups.com.

[Juan Ferdinan]

Hi Anthony

I've managed to get the information I want, but I can only get that information using a manual search in the wazuh log storage. How to display this information on the wazuh-manager dashboard?

Best Regards

Juan

[chachab]

Hello Team, I have the same issue. For me, I enabled the logs from MS SQL server to come into the Application Event viewer (See below)

Now, I enabled the <logall>yes</logall> to get the logs into the /var/ossec/logs/archives/archives.log file

So I run the command.

tail -f /var/ossec/logs/archives/archives.log | grep -iE email99 to see if the event will be displayed. Yes, good thing I get the output of it.


Now, how can I get that log into my Wazuh dashboard?