Note That: Audit option is not available in Express versions. SQL Server Enterprise 2008 version is used in the explanation.
To configure Audit, SQL Server Management Studio is opened, 'Security -> Audits' path is followed on 'Object Explorer'. Right click on 'Audits' node and select 'New Audit' option, 'Create Audit' window will appear. In the 'Audit name' field, the name of the Audit object is written. In the 'Destination' section, File, Security log, Application log options can be selected. By selecting the File option, we can show where the file should be saved in the 'File Path' section. With Security and Application log options, it can be ensured that the logs are in windows event log format.
Then, determine which events will be logged, so right-click on the 'Server Audit Specification' node on the Object Explorer and select 'New Server Audit Specification'. The events that we want to be logged are selected from the 'Audit Action Type'.
From a security perspective, it is generally recommended to select the following events:
- FAILED_LOGIN_GROUP
- SUCCESSFUL_LOGIN_GROUP
- DATABASE_OBJECT_CHANGE_GROUP
- DATABASE_PRINCIPAL_CHANGE_GROUP
- SCHEMA_OBJECT_CHANGE_GROUP
- SERVER_PRINCIPAL_CHANGE_GROUP
- LOGIN_CHANGE_PASSWORD_GROUP
- SERVER_STATE_CHANGE_GROUP
- DATABASE_CHANGE_GROUP
- DATABASE_OBJECT_CHANGE
- SCHEMA OBJECT ACCESS
- SCHEMA OBJECT CHANGE
After saving as specified, right click on Audit Object and Audit Specifications nodes, click 'Enable' and activate.
In the Logsign web interface, click the Device+ button in the menu bar on the top of the page, which is opened by clicking on the Settings tab. Then select brand information in the Vendor section of MSSQL. A page will come up to configure your MSSQL DB.
Host: IP address information of the MSSQL product that you want to retrieve the logs.
SourceType: Auditing or table can be selected.
Audit File Path: In the audit configuration, the path where you want the logs to be written is entered.
Audit File: Audit file extension is entered
Period: The polling period of the logs is determined.
Offset: To explain it in terms of definition, let's say "time difference." If the "system" time you want to log in is forward or backward from the real-time difference, you can edit it accordingly. The symbol "+" moves forward, and "-" moves backward. Time information is specified in minutes.
Data Policy: As a definition, you can filter in or out of incoming data. In the Data Policy section, you can specify the kind of logs (word, event movement type, etc.) that you want to receive or not from the source. The default setting here is the Default Policy, which has the default rule is "collect all logs."
Max Line Length to Process: Each log is generated as a single line. So, the Logsign Unified SecOps Platform takes these logs and analyzes them. In some cases, the number of characters in a single line of a single log file can be more significant than two thousand forty-eight (2048). In such cases, you can change this part.
Check Health: If you tick the box, that will inform you about the service and operability of the Logsign Unified SecOps Platform. The Health Check Period tab will come up when you tick the box. This part is the time interval information to be checked.
Device Name: You must enter a descriptive name according to the configuration that you are making (For ex., MSSQL). It can provide convenience for people who analyze logs. You can think of the Description field as a resource-specific area.
Tag: Slightly different from the Description section, it can be used for a broader purpose. For example, you can query by tag; and make tag-based definitions while creating a report if you use multiple SQL Server and define each tag as mssql1 or mssql2. If you want to query about an event, you will get a shorter result when searching according to the mssql1 name.
Once configured your MSSQL product, click the Save button to save your configuration and add the source.