6.9 Microsoft SQL Server

The Microsoft SQL Server channel allows the logging server to log events to a Microsoft SQL Server database. The logging server can use the Microsoft SQL Server channel to create the central data store or a filtered database.

IMPORTANT:Native connections to a Microsoft SQL server database are available only when running the Secure Logging Server on a Windows platform. JDBC must be used to connect to a Microsoft SQL Server database from other platforms.

The space you need for your database depends on a number of factors. These include, but are not limited to, how many events per second you are storing and how long you want to keep the data. For the data store, a system that generates around 80 events per second with an average event size of 80 bytes consumes approximately 500 MB of disk space for the database table and 150 MB for the index in a 24-hour period.

IMPORTANT:Microsoft SQL Server limits the size of the data field to 10,484 bytes. If the size of the data field logged by the Platform Agent exceeds 10,484 bytes, the Microsoft SQL Server channel driver truncates the data in the Data field.

6.9.1 Microsoft SQL Server Channel Driver

When the Microsoft SQL Server Channel object configuration is loaded in the logging server’s memory, the Microsoft SQL Server channel driver, lgdmssql, automatically creates the following table structure for the SQL Server data store:

Figure 6-1 Microsoft SQL Server Table Structure

Image displaying SQL Server table format

The table name is defined in the Microsoft SQL Channel object configuration page. The default table name is NAUDITLOG.

To create this table manually, run the following, replacing nauditlog with the name you want to use for the table:

CREATE TABLE [auditusr].[nauditlog] (
 [SourceIP] [int] NOT NULL ,
 [ClientTimeStamp] [int] NOT NULL ,
 [ClientMS] [int] NOT NULL ,
 [ServerTimestamp] [int] NOT NULL ,
 [SessionID] [int] NOT NULL ,
 [Component] [varchar] (255) COLLATESQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [EventID] [int] NOT NULL ,
 [Severity] [int] NOT NULL ,
 [Grouping] [int] NULL ,
 [Originator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [OriginatorType] [int] NULL ,
 [Target] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [TargetType] [int] NULL ,
 [SubTarget] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Text1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Text2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Text3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Value1] [int] NULL ,
 [Value2] [int] NULL ,
 [Value3] [int] NULL ,
 [MIMEType] [int] NOT NULL ,
 [DataSize] [int] NOT NULL ,
 [Data] [image] NULL , 
 [Signature] [varchar] (184) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

6.9.2 Microsoft SQL Server Channel Object

The Microsoft SQL Server Channel object stores the information the Microsoft SQL Server driver needs to write events to a Microsoft SQL Server database.

The following table provides a description of each Channel object attribute.

IMPORTANT:You must restart the logging server to effect any changes in Channel object configuration. For more information, see Section H.3, Secure Logging Server Startup Commands

Table 6-11 Microsoft SQL Server Channel Object Attributes

Attribute

Description

Configuration

 

Server Name

The IP Address or host name of the database server.

Database

The name of the database to which the logging server writes events. This field is case sensitive.

IMPORTANT:This database must already exist. The SQL Server channel driver does not create the database. If no database name is specified, the logging server looks for NAudit. for information on creating the database, see Section E.2, Preparing the Microsoft SQL Server Database.

Table

The name of the database table to which the logging server writes events.

The SQL Server channel driver, lgdmssql, automatically creates this table when the logging server first loads the current Channel object configuration in memory. For information on the table structure, see Section 6.9.1, Microsoft SQL Server Channel Driver.

Do not use hyphens, spaces, or other special characters in the table name. The default table name is NAUDITLOG.

User Name

The user name for the account the logging server uses to authenticate with the database. This account adds records to the Microsoft SQL database.

Password

The password for the account the logging server uses to authenticate with the database.

Use SSL

(Optional) Select whether SSL should be used to encrypt data transferred between the Secure Logging Server and the Microsoft SQL server.

Status

Allows you to enable or disable the Channel object. By default, all Channel objects are enabled. This means that the logging server loads the Channel object’s configuration in memory at startup.

The Channel object must be located in a supported Channel container for the logging server to use it. For more information on the logging server’s Channel Container property, see Logging Server Object Attributes .

If you select the Disabled option, you must restart the Secure Logging Server for the setting to become effective. Thereafter, the logging server cannot load the object’s configuration until you select Enabled.

For information on unloading the logging server, see Section H.3, Secure Logging Server Startup Commands.