Collect Microsoft SQL Server logs

This document describes how you can collect the Microsoft SQL Server logs by using a Google Security Operations forwarder.

For more information, see Data ingestion to Google Security Operations.

An ingestion label identifies the parser that normalizes raw log data to structured UDM format. The information in this document applies to the parser with the MICROSOFT_SQL ingestion label.

Configure the Microsoft SQL Server logs using NxLog agent

1. Go to services.msc and stop the nxlog service.
2. Go to C:\Program Files (x86)\nxlog\data and delete configcache.dat.
3. For the Windows agent, go to the installed location C:\Program Files (x86)\nxlog\conf.

4. Copy and paste the following configuration into the nxlog.conf file.

   This is a sample configuration file. See the nxlog reference manual about configuration options.

5. Set ROOT to the folder that you installed NXLog in, otherwise NXLog won't start.

      #define ROOT C:\Program Files\nxlog
      define ROOT C:\Program Files (x86)\nxlog
      Moduledir %ROOT%\modules
      CacheDir %ROOT%\data
      Pidfile %ROOT%\data\nxlog.pid
      SpoolDir %ROOT%\data
      LogFile %ROOT%\data\nxlog.log
      <Extension charconv>
          Module xm_charconv
          AutodetectCharsets UTF-8, UCS-2LE
      </Extension>
      # Load the json extension
      <Extension json>
          Module      xm_json
      </Extension>
      <Input sql-ERlogs>
          Module      im_file
      File "FILE_PATH"
          ReadFromLast False
          SavePos False
      Exec $FileName = file_name();
      Exec $Hostname = hostname_fqdn();
      Exec $raw_event = "NXLog|" + $Hostname + "|MS_SQL_SERVER_ERROR|" + $FileName + "|" + "000|000" + "|" + convert($raw_event, 'UCS-2LE','UTF-8');
      </Input>
      # Send the read log lines out to nxlog server
      <Output out-sqlERlogs>
          Module      om_tcp
          Host        FORWARDER_IP_ADDRESS
          Port        PORT_NUMBER
      OutputType LineBased
      </Output>
      # Build the route from nxlog on Windows to nxlog on server
      <Route 1>
          Path        sql-ERlogs => out-sqlERlogs
      </Route>
   

   Replace the following:

   • FILE_PATH: the Microsoft SQL error log location
   • FORWARDER_IP_ADDRESS: the Google SecOps forwarder IP address
   • PORT_NUMBER: a high port number

6. Start the NXLog service from services.msc.

   NxLog agent logs are available at C:\Program Files (x86)\nxlog\data\nxlog.log.

   For information about configuration and options for SQL error log files, see the SCM Services - Configure SQL Server Error Logs section in the Microsoft documentation.

Configure the Google SecOps forwarder to ingest Microsoft SQL Server logs

1. In the Google SecOps menu, select Settings > Forwarders > Add new forwarder.
2. In the Forwarder name field, enter a unique name for the forwarder.
3. Click Submit. The forwarder is added and the Add collector configuration window appears.
4. In the Collector name field, enter a unique name for the collector.
5. In the Log type field, enter Microsoft SQL Server.
6. Select Syslog as the Collector type.
7. Configure the following input parameters:
   • Protocol: the connection protocol that the collector uses to listen to syslog data.
   • Address: the target IP address or hostname where the collector resides and listens to syslog data.
   • Port: the target port where the collector resides and listens to syslog data.
8. Click Submit.

For more information about the Google SecOps forwarders, see Manage forwarder configurations through the Google Security Operations UI.

If you encounter issues when you create forwarders, contact Google Security Operations support.

What's next

• Data ingestion to Google Security Operations

Need more help? Get answers from Community members and Google SecOps professionals.