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:

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

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

1. If automatic updates are not enabled, download and install the most recent version of the Microsoft SQL Server RPM on your JSA Console.

2. For each instance of Microsoft SQL Server, configure your Microsoft SQL Server appliance to enable communication with JSA.

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

Create an auditing object to store audit events.

1. Log in to your Microsoft SQL Server Management Studio.
2. From the navigation menu, select Security > Audits.
3. Right-click Audits and select New Audit.
4. In the Audit name field, type a name for the new audit file.
5. From the Audit destination list, select File.
6. From the File path field, type the directory path for your Microsoft SQL Server audit file.
7. Click OK.
8. 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. 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.

1. From the Microsoft SQL Server Management Studio navigation menu, select one of the following options:

   • Security > Server Audit Specifications

   • <Database> > Security > Database Audit Specifications

2. Right-click Server Audit Specifications, and then select one of the following options:

   • New Server Audit Specifications

   • New Database Audit Specifications

3. In the Name field, type a name for the new audit file.
4. From the Audit list, select the audit object that you created.
5. In the Actions pane, add actions and objects to the server audit.
6. Click OK.
7. 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.

1. From the Microsoft SQL Server Management Studio toolbar, click New Query.
2. Type the following Transact-SQL statement:

   For example:
3. From the Standard toolbar, click Execute.

Configuring a Microsoft SQL Server Log Source

Use this procedure if your JSA Console did not automatically discover the Microsoft Windows Security Event log source.

1. Click the Admin tab.
2. Click the Log Sources icon.
3. Click the Add button.
4. From the Log Source Type list, select Microsoft SQL Server.
5. From the Protocol Configuration list, select JDBC or WinCollect.
6. Optional. If you want to configure events for JDBC, configure the parameters. The following table describes the JDBC parameters that require specific values to collect events from Microsoft SQL Server:

   Parameter	Description
   Log Source Name	Type a unique name for the log source.
   Log Source Description (Optional)	Type a description for the log source.
   Log Source Type	From the list, select Microsoft SQL Server.
   Protocol Configuration	From the list, select 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.
   Username	Type the user name to access the SQL database.
   Password	Type the password to access the SQL database.
   Confirm Password	Type the password to access the SQL database.
   Authentication Domain	If you did not select Use Microsoft JDBC, Authentication Domain is displayed. The domain for MSDE that is a Windows domain. If your network does not use a domain, leave this field blank.
   Database Instance	Note: If you have a non-standard port in your database configuration, or access is blocked to port 1434 for SQL database resolution, you must leave the Database Instance parameter blank.
   Predefined Query (Optional)	Select a predefined database query for the log source. If a predefined query is not available for the log source type, administrators can select the none option.
   Table Name	Type dbo.AuditData as the name of the table or view that includes the audit event records.
   Select List	Type * for all fields from the table or view. You can use a comma-separated list to define specific fields from tables or views. The list must contain the field that is defined in the Compare Field parameter. The comma-separated list can be a maximum of 255 characters. You can include the special characters, dollar sign ($), number sign (#), underscore (_), en dash (-), and period (.).
   Compare Field	Type event_time in the Compare Field parameter. The Compare Field identifies new events that are added between queries, in the table.
   Start Date and Time (Optional)	The Start Date and Time parameter must be formatted as yyyy-MM-dd HH:mm with HH specified by using a 24-hour clock. If the start date or time is clear, polling begins immediately and repeats at the specified polling interval.
   Use Prepared Statements	Select this check box to use prepared statements Prepared statements allow the JDBC protocol source to set up the SQL statement, and then run the SQL statement many times with different parameters. For security and performance reasons, you might want to use prepared statements. Clearing this check box requires you to use an alternative method of querying that does not use pre-compiled statements.
   Polling Interval	You can type a polling interval number. The polling interval is the amount of time between queries to the event table. The default polling interval is 10 seconds. You can define a longer polling interval by appending H for hours or M for minutes to the numeric value. The maximum polling interval is 1 week in any time format. Numeric values that are entered without an H or M, poll in seconds.
   EPS Throttle	Type the number of Events Per Second (EPS) that you do not want this protocol to exceed. The default value is 20000 EPS.
   Use Named Pipe Communication	If you did not select Use Microsoft JDBC, Use Named Pipe Communication is displayed. Clear the Use Named Pipe Communications check box. If you use a Named Pipe connection, the user name and password must be the appropriate Windows authentication user name and password, and not the database user name and password. Also, you must use the default Named Pipe.
   Database Cluster Name	If you select the Use Named Pipe Communication check box, the Database Cluster Name parameter is displayed. If you are running your SQL server in a cluster environment, define the cluster name.
   Use NTLMv2	If you did not select Use Microsoft JDBC, Use NTLMv2 is displayed. Select this option if you want MSDE connections to use the NTLMv2 protocol when they are communicating with SQL servers that require NTLMv2 authentication. This option does not interrupt communications for MSDE connections that do not require NTLMv2 authentication. Does not interrupt communications for MSDE connections that do not require NTLMv2 authentication.
   Use Microsoft JDBC	If you want to use the Microsoft JDBC driver, you must enable Use Microsoft JDBC.
   Use SSL	Select this option if your connection supports SSL. This option appears only for MSDE.
   Microsoft SQL Server Hostname	If you selected Use Microsoft JDBC and Use SSL, the Microsoft SQL Server Hostname parameter is displayed. You must type the host name for the Microsoft SQL server.

7. Optional. If you want to configure events for WinCollect, see the JSA WinCollect User Guide.
8. Click Save.
9. On the Admin tab, click Deploy Changes.