Microsoft SQL Server

The JSA DSM for Microsoft SQL Server collect SQL events by using the syslog, WinCollect Microsoft SQL, or JDBC protocol.

The following table identifies the specifications for the Microsoft SQL Server DSM:

Table 1: Microsoft SQL Server DSM
Specification	Value
Manufacturer	Microsoft
DSM name	SQL Server
RPM file name	DSM-MicrosoftSQL-`JSA-version-Build_number`.noarch.rpm
Supported versions	2012, 2014 (Enterprise editions only), 2016, 2017, and 2019
Event format	syslog, JDBC, WinCollect
JSA recorded event types	SQL error log events
Automatically discovered?	Yes
Includes identity?	Yes
More information	Microsoft website (http://www.microsoft.com/en-us/server-cloud/products/sql-server/)

You can integrate Microsoft SQL Server with JSA by using one of the following methods:

Syslog	The JSA DSM for Microsoft SQL Server can collect LOGbinder SQL events. For information about configuring LOGbinder SQL to collect events from your Microsoft SQL Server, go to the Syslog documentation.
JDBC	Microsoft SQL Server Enterprise can capture audit events by using the JDBC protocol. The audit events are stored in a table view. Audit events are only available in Microsoft SQL Server 2012, 2014, and 2016 Enterprise.
WinCollect	You can integrate Microsoft SQL Server 2012, 2014, 2016, 2017, and 2019 with JSA by using WinCollect to collect ERRORLOG messages from the databases that are managed by your Microsoft SQL Server. For more information, see your WinCollect documentation.

To integrate the Microsoft SQL Server DSM with JSA, use the following steps:

If automatic updates are not enabled, download and install the most recent version of the Microsoft SQL Server RPM from the Juniper Downloads onto your JSA Console.
For each instance of Microsoft SQL Server, configure your Microsoft SQL Server appliance to enable communication with JSA.
If JSA does not automatically discover the Microsoft SQL Server log source, create a log source for each instance of Microsoft SQL Server on your network.

Microsoft SQL Server Preparation for Communication with JSA

To prepare Microsoft SQL Server for communication with JSA, you must create an audit object, audit specification, and database view.

Creating a Microsoft SQL Server Auditing Object
Creating a Microsoft SQL Server Audit Specification
Creating a Microsoft SQL Server Database View

Creating a Microsoft SQL Server Auditing Object

Create an auditing object to store audit events.

Log in to your Microsoft SQL Server Management Studio.
From the navigation menu, select Security > Audits.
Right-click Audits and select New Audit.
In the Audit name field, type a name for the new audit file.
From the Audit destination list, select File.
From the File path field, type the directory path for your Microsoft SQL Server audit file.
Click OK.
Right-click your audit object and select Enable Audit.

Creating a Microsoft SQL Server Audit Specification

Create an audit specification to define the level of auditing events that are written to an audit file.

You must create an audit object. For more information, see Creating a Microsoft SQL Server Auditing Object.

You can create an audit specification at the server level or at the database level. Depending on your requirements, you might require both a server and database audit specification.

From the Microsoft SQL Server Management Studio navigation menu, select one of the following options:
- Security > Server Audit Specifications
- <Database> > Security > Database Audit Specifications
To enable Server or Database Audit, select one of the following options:
- Right-click Server Audit Specification, then select New Server Audit Specifications
- Right-click Database Audit Specification, then select New Database Audit Specifications
In the Name field, type a name for the new audit file.
From the Audit list, select the audit object that you created.
In the Actions pane, add actions and objects to the server audit.
Click OK.
Right-click your server audit specification and select one of the following options:
- Enable Server Audit Specification
- Enable Database Audit Specification

Creating a Microsoft SQL Server Database View

Create the dbo.AuditData database view to allow JSA to poll for audit events from a database table by using the JDBC protocol. The database view contains the audit events from your server audit specification and database audit specification.

From the Microsoft SQL Server Management Studio toolbar, click New Query.

Type the following Transact-SQL statement:

For example:

From the Standard toolbar, click Execute.

JDBC Log Source Parameters for Microsoft SQL Server

If JSA does not automatically detect the log source, add a Microsoft SQL Server log source on the JSA Console by using the JDBC protocol.

When using the JDBC protocol, there are specific parameters that you must use.

The following table describes the parameters that require specific values to collect JDBC events from Microsoft SQL Server:

Parameter	Value
Log Source Type	Microsoft SQL Server
Protocol Configuration	JDBC
Log Source Identifier	Type a name for the log source. The name can't contain spaces and must be unique among all log sources of the log source type that is configured to use the JDBC protocol. If the log source collects events from a single appliance that has a static IP address or host name, use the IP address or host name of the appliance as all or part of the Log Source Identifier value; for example, 192.168.1.1 or JDBC192.168.1.1. If the log source doesn't collect events from a single appliance that has a static IP address or host name, you can use any unique name for the Log Source Identifier value; for example, JDBC1, JDBC2.
Database Type	From the list, select MSDE.
Database Name	Type `Master` as the name of the Microsoft SQL database.
IP or Hostname	Type the IP address or host name of the Microsoft SQL server.
Port	Type the port number that is used by the database server. The default port for MSDE is 1433. The JDBC configuration port must match the listener port of the Microsoft SQL database. The Microsoft SQL database must have incoming TCP connections that are enabled to communicate with JSA. Note: If you define a Database Instance when you are using MSDE as the Database Type, you must leave the Port parameter blank in your configuration.
Table Name	Type `dbo.AuditData` as the name of the table or view that includes the audit event records.
Compare Field	Type `event_time` in the Compare Field parameter. The Compare Field identifies new events that are added between queries, in the table.

Microsoft SQL Server Sample Event Message

Use this sample event message to verify a successful integration with JSA.

Note:

Due to formatting issues, paste the message format into a text editor and then remove any carriage return or line feed characters.

Microsoft SQL Server Sample Message when you use the Syslog Protocol

The following sample event message shows a Microsoft SQL Server Drop Login event.

Table 2: Highlighted Values in the Microsoft SQL Server Sample Event
JSA field name	Highlighted values in the event payload
Event ID	action_id + class_type
Category	When the Microsoft SQL Server DSM parses this type of event, the Category value in JSA is always MicrosoftSQL.
Username	session_server_principal_name
Log Source Time	event_time

ON THIS PAGE