We sometimes complain that the Security Operations Center (SOC) doesn’t spot cyber-attacks on time, but maybe we’re not giving them critical information they need to do their job. SIEM stands for Security Information and Event Management; it encompasses systems which combine information from multiple sources to detect, analyze and respond to cybersecurity threats. The SQL Server error log contains not only restore / batch / stop / restart / kernel and server-level messages, but mainly contains relevant events useful to security analysts.

Once an adversary has made its way into the database (i.e. using SQL Injection), there will be many traces in the error log that can be spotted to stop the attack. Probably the first thing an adversary will do is limit the number of log files / log file size to the minimum and cycle the error log multiple times to hide his actions, so the following will start appearing:

The error log has been reinitialized. See the previous log for older entries.

The next thing will be disabling the default trace / auditing / any other trace, so any of the following will appear:

Configuration option 'default trace enabled' changed from 1 to 0
Audit: Server Audit: 6… Session has been closed
SQL Trace stopped. Trace ID = '2'. Login Name = '…'

Other things they will most likely do are enable “show advanced options”, “xp_cmdshell”, and “Ole Automation Procedures” because that allows them to interact directly with the underlying operating system and go beyond the database limitations.

The error log contains changes made to your disaster recovery solutions so it’s important that you know if / when they’re being tampered with; mirroring, replication, log shipping, always on, etc. It also contains backup operations, so you won’t notice if your data is being sent somewhere else, and if your own scripts and third-party tools stop backing up your data externally, preventing you from a successful recovery.

Disabling traces / audits hides extended events, which can track administrative operations performed in the system. They also log permission changes, so you won’t know when adversaries create their own users, roles, and assign permissions. And they also log certificates and encryption key management; modifying them can prevent your data from being readable ever.

If you have enabled “failed login auditing”, which you should, then the SOC can detect password spay attacks; this, by creating a rule which detects multiple “login failed” attempts and triggers an alert. Many people recommend disabling or renaming the “sa” user because it will be tried first:

Login failed for user "sa". Reason: Password did not match that for the login provided.

But there are many other application-specific users which can have administrative permissions and you probably didn’t change their default passwords; here is a list of usernames adversaries always test with guessable passwords: 401hk, admin, arcserve_udp, bizbox, bwsa, ELNAdmin, hbv7, jirauser, kisadmin, lansweeperuser, msi, mssql, mssqla, neterp, probe, ps, root, sp, sql, su, sysdba, uep, unierp, vice, web, WinCCAdmin, WinCCConnect, zabbix.

The attacker will also try to connect with integrated authentication in case your server trusts everyone everywhere, so you will start seeing some of the following messages:

SSPI handshake failed with error code 0x80090308, state 14 while establishing a connection with integrated security; the connection has been closed.
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

The error log also shows port / vulnerability scanning, and you will notice the following errors:

Length specified in network packet payload did not match number of bytes read; the connection has been closed.
The login packet used to open the connection is structurally invalid; the connection has been closed.
A valid TLS certificate is not configured to accept strict (TDS 8.0 and above) connections.

That’s why it’s important to have the SOC team read the SQL Server error log automatically, for them to detect an anomaly in the administrative operations performed, aggregate multiple operations performed in a short amount of time and know the source IP address of these actions; this is probably the first and foremost countermeasure to stop attacks and data leaks, and resolve misconfigurations or block IPs as needed.

SEIM Integration

Now, how do you send those messages to the SIEM? Let’s take for example Wazuh, which is “a free and open-source security platform that unifies XDR and SIEM protection for endpoints and cloud workloads”.

There are three changes to be made, and one of them requires the help of the DBA. In the SQL Server host the SOC team need to modify the agent configuration file “ossec.conf”, usually located under “/var/ossec/etc/” in Linux or in “C:\Program Files (x86)\ossec-agent\” in Windows by adding the following lines:

  <localfile>
    <location>/var/opt/mssql/log/errorlog</location>
    <log_format>mssql_log</log_format>
    <ignore_binaries>yes</ignore_binaries>
  </localfile>

The location changes if it is SQL Server on Windows:

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

But remember this depends on your installation path, the SQL Server version, if it’s the default or a named instance, the number of instances, and if the path has been modified. That’s the importance of the DBA, to define which files need to be monitored, and which alerts are of importance to the SOC.

The other two changes need to be done by the SOC team on the SIEM side: log decoding and rule matching / alert visualization; you can find more information in this link.

Remember I mentioned there are other sources of information: traces, audits and extended events? If the audit is written to a file, it can be fed to the SIEM, and if the extended event session is written to a file, it can be fed to the SIEM but its XML format requires additional decoding, rules and alerts. Only the traces, which are in binary format, require intermediate processing using the built-in function “fn_trace_gettable”, see an example here about how to read that file, but it needs to be written to a text file and that file also needs to be fed into the SIEM.

Note: if you need any help regarding the solutions described here, you can contact me in my LinkedIn profile.