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.

1149

MSSQL Server Logs with a high severity level

1145

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.
284

Logs Tab

  1. Select the Sources tab.
249

Sources Tab

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

Add Source Configuration Button

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

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
361

Steps 1-2

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

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.
373

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.
508

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.
359

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.
512

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.