Microsoft SQL Server Configuration

TABLE OF CONTENTS

• Overview
• Enabling Audit logs in the MSSQL Server
• Enabling Server Auditing
  • Creating Audits
  • Creating Server Audit Specifications
• NxLog Configuration
• Verification of Configuration
  • Using the UI

Overview

Enabling audit logs in Microsoft SQL Server (MSSQL) can help track and monitor various activities such as login attempts, database modifications, and other security-related events. Here's a general overview of how you can enable audit logging in MSSQL.

For analyzing MS SQL audit logs by EventLog Analyzer, you need to initially enable auditing in the MS SQL server. To enable auditing in MS SQL server, you need to:

• Create a SQL Server Audit Object that can be used for auditing.
• Create a Server audit specification.
• Enabling the Audit Object.

Enabling Audit logs in the MSSQL Server

Enable Audit logs in the MSSQL server with the steps below:

1. Open Microsoft SQL Management Studio with the appropriate credentials.

2. In Object Explorer, right-click on the database server and select Properties.

3. In the Properties panel, select Security in the Select a page section.

4. In Login auditing, select Both failed and successful logins.

Enabling Server Auditing

1. Open Microsoft SQL Management Studio with appropriate credentials.

2. In Object Explorer, expand the Security tab to view Audits and Server Audit Specifications options.

Creating Audits

1. Right-click Audits to select New Audit...

2. In Audit Properties, provide the appropriate audit name and set the audit destination as the application log. The configured Audit properties pane is shown below:

3. Click OK to apply settings.

Creating Server Audit Specifications

1. Right-click Server Audit Specifications and select New Server Audit Specification...

2. In Server Audit Specification Properties, provide an appropriate specification name and choose an earlier created audit name from the drop-down menu. The Configured Server Audit Specification Properties pane is shown below:

3. Click OK to apply settings.

4. Right-click on the earlier created audit and select Enable.

5. Right-click on the earlier created Server Audit Specification and select Enable Server Audit Specification.

6. To view audit logs, enable login auditing and click on the 'View Audit Logs' button.

7. The outcome will show login success.

NXLog Configuration

1. Log into the Windows SQL Server as Administrator.
2. If you are not already running NXLog, follow the directions below:
   1. Download the latest version of NXLog for Windows: http://nxlog.org/products/nxlog-community-edition/download.
   2. Double-click on the downloaded MSI and install NXLog.
3. As an administrator, open C:\Program Files\nxlog\conf\nxlog.conf in a text editor.
   1. Replace the entire contents of nxlog.conf with the following configuration
   2. Replace CCE_IP_ADDRESS with the local IP of your CCE VM.

      ## This is a sample configuration file. See the nxlog reference manual about the
      ## configuration options. It should be installed locally and is also available
      ## online at http://nxlog.org/docs/
      ## Please set the ROOT to the folder your nxlog was installed into,
      ## otherwise it will not start.
      define ROOT C:\Program Files\nxlog
       
      #define ROOT C:\Program Files (x86)\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 _json>    
        Module xm_json
      </Extension>
       
      #Extension for MSSQL
      <Extension mssql_csv>
          Module          xm_csv
          Fields          $Hostname, $SourceName, $Action_ID, $Result, $DataBase, $SV_Instace, $User, $Message
          FieldTypes      string, string, string, string, string, string, string, string
          Delimiter       ;
      </Extension>
       
      #Input for MSSQL
      <Input in_mssql>
          Module          im_msvistalog
          SavePos         FALSE
          ReadFromLast    TRUE
          Exec   $Message = $raw_event;
          # Finding some values:
          Exec    if $raw_event =~ /action_id:(\S+)/ $Action_ID = $1;
          Exec    if $raw_event =~ /database_name:(\S+)/ $DataBase = $1;
          Exec    if $raw_event =~ /server_instance_name:(\S+)/ $SV_Instace = $1;
          Exec    if $raw_event =~ /session_server_principal_name:(\S+)/ $User = $1;
          Exec    if $raw_event =~ /AUDIT_SUCCESS/\
                    {\
                          $Result = 'Success';\
                    }\
                    else\
                          $Result = 'Failure';
          # Replace white spaces
          Exec            $Message = replace($Message, "\t", " "); $Message = replace($Message, "\n", " "); $Message = replace($Message, "\r", " ");
      </Input>
       
      #Output for MSSQL
      <Output out_mssql>
          Module          om_udp
          Host            CCE_IP_ADDRESS
          Port            514
          # Ensure we send in the proper format:
          Exec           $Hostname = hostname_fqdn();
          Exec            mssql_csv->to_csv(); $raw_event = $Hostname + ' mssql_logs: ' + $raw_event;
      </Output>
       
      #Route for MSSQL Logs
      <Route mssql>
          Path            in_mssql => out_mssql
      </Route>

4. Open a Command or PowerShell Window as an administrator and restart NXLog.
   net stop nxlog
   net start nxlog

Verification of Configuration

1. Log in to the UI and go to System.

2. Go to Log/Flow Collection Status.

3. To verify the source device IP from the UI:

1. Log in to the user interface.
2. Navigate to the "SYSTEM" section.
3. Look for the "SOURCE DEVICE IP".
4. Check the IP address that is displayed.
5. Compare the IP address displayed against the expected source device IP.

This will allow you to ensure that the system is properly identifying the source device IP and that it matches the expected IP address.