Is it possible to monitor MSSQL Transactions?

[Daniel D'Angeli]

Hi!

Im looking for a way to monitor Microsoft SQL transactions with a Wazuh Agent.

Is there any way to achieve this? If not how can i do it?

Regards,

Daniel

[Jose Antonio Izquierdo]

Hi Daniel, 

In order to collect info from your windows MS SQL server you can: 

1.- you can use the eventchannel log format to read the events directly from the EventChannel service. You can see the source channel of the events in one of them, and set an agent configuration like:

<localfile> 

  <location>Microsoft SQL Server/Operational</location> 

  <log_format>eventchannel</log_format> 

</localfile>

2.- when your logs are in a plain text we can use standard collection using localfile configuration like this: 

<localfile> 

   <location>C:\Program Files\Microsoft SQL Server\MSSQL....</location> 

   <log_format>syslog</log_format> 

</localfile>

you can use wildcards if needed to read all files from a folder regardless extension or full name of the file.

3.- you are asking for transactions and you may need some extra work to be done if I understand correctly. MSSQL transaction log are store per DB in a DB file and you must access that information using some kind of SQL query like 

USE ReadingDBLog; 

GO 

SELECT [Current LSN], 

  [Operation], 

  [Transaction Name], 

  [Transaction ID], 

  [Transaction SID], 

  [SPID], 

  [Begin Time] 

FROM fn_dblog(null,null)

If this is what your are expecting to monitor, you can grab info from your transaction DB files by a script that will store SQL results in a plain text file, maybe with a csv like format. (please consider your desired format with the right fields, this is just a sample)

CurrentLSN,Operation,TransactionName,TransactionID,TransactionSID,SPID,BeginTime

So, wazuh agent can read that output files and manage them. We will need to have the right decoder/rules on manager side but this will be easy to achieve. 

Please, let us know whether this helps and if you need information on how to build the decoder/rule. Also, please, feel free to provide any extra details about your scenario that I may be missing in this answer.

Thanks, 

Jose.

[Daniel D'Angeli]

Hi! Thanks for the quick response!

Is there a way to find who did a query? The fn_dblog() function doesn't retrieve that kind of info.

Regards,

Daniel

[Jose Antonio Izquierdo]

Hi Daniel, 

Maybe those links will help you,  

https://logicalread.com/sql-server-dbcc-log-command-tl01/

https://www.mssqltips.com/sqlservertip/3076/how-to-read-the-sql-server-database-transaction-log/

Also, Microsoft site can help

https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15

Hope it helps.

Best, 

Jose.