Microsoft SQL Server

Logs Collected

There are several different sources from which information is collected for MSSQL Server. Below are screenshots that highlight a few different events collected from MSSQL Server that also have different log severity levels.

11491149

MSSQL Server Logs with a high severity level

11451145

MSSQL Server Logs of repeatedly failed login attempts

Supported Versions

Microsoft SQL Server 2012+

Log Collection Setup

Follow the steps below to setup log collection for Microsoft SQL Server

🚧

Windows Event Log

At this time, logs are only collected from Windows Event Log. The logs being collected are only error logs.

Prerequisites

  • Confirm that the MSSQL Server is logging to Windows Event Log

Configure a Microsoft SQL Server Log Source

  1. Install the BindPlane Log Agent on the host system.
  2. Login to BindPlane and select the Logs tab.
284284

Logs Tab

  1. Select the Sources tab.
249249

Sources Tab

  1. In the top-right portion of the screen, click on the Add Source Configuration button
576576

Add Source Configuration Button

  1. Choose Microsoft SQL Server
  2. Fill out the MSSQL Server Log Configuration Form
10781078

MSSQL Log Configuration Form

Enabling Query Logging in Microsoft SQL

The BindPlane Log Agent can help to provide insights down to the query level by sending query logs to Google Stackdriver. Google Stackdriver can help to uncover trends that might exist by showing historic data about the queries. The following section describes how to enable the logging of MSSQL queries so that the BindPlane Log Agent can provide maximum visibility.

To setup Query Logging in MS SQL, follow these steps:

  1. Open Microsoft SQL Server Manager Studio and log into your SQL server.
  2. Expand the Security folder and right click on New Audit
361361

Steps 1-2

  1. Name the Audit and choose the Audit destination as Application Log. Press OK.
667667

Step 3

  1. Expand the Audits folder, right click on your new audit and choose Enable Audit.
  2. Right click on Server Audit Specifications and choose New Server Audit Specification.
373373

Steps 4-5

  1. Name the specification, click on the Audit drop down and choose your above named audit.
  2. Choose all of the action types you would like audited and press OK.
508508

Steps 6-7

  1. Expand the Server Audit Specifications folder, right click on your specification and choose Enable Server Audit Specification.
  2. Expand the Databases folder, expand your database, expand Security, right click on Database Audit Specifications and choose New Database Audit Specification.
359359

Steps 8-9

  1. Name the specification, click on the Audit drop down and choose your above named audit.
  2. Choose all of the action types you would like audited and press OK. Keep in mind that things like INSERT would need additional parameters filled in before you can hit the OK button.
512512

Steps 10-11

  1. Right click on your specification and choose Enable Database Audit Specification.

After completing these tasks the events that meet your specifications should start appearing in Event Viewer under Application logs.