Important, read before configuring: Logsign's current MSSQL Auditing parser is built to read SQL Server's own ERRORLOG text file (the plain-text server log SQL Server writes by default, containing lines like Login failed for user 'x'. Reason: ... [CLIENT: 10.0.0.5]), not the structured output of a custom Server Audit Specification object. The Server Audit Specification steps below still describe a real, useful SQL Server auditing feature, but as of this writing Logsign does not parse its output. If you only need login success/failure visibility, enabling SQL Server login auditing (Object Explorer > right-click the server > Properties > Security > Login auditing > Both failed and successful logins) is what actually populates ERRORLOG with the events this parser expects; point the Audit File Path field below at your instance's ERRORLOG location (typically ...\MSSQL\Log\ERRORLOG, no file extension) rather than a Server Audit Specification output file. If you need the richer object-level audit trail (schema/database changes, and so on) that Server Audit Specification provides, treat that as a separate requirement to raise with Logsign support, since it is not currently consumed by this integration.
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: Point this at your SQL Server instance's ERRORLOG directory (typically ...\MSSQL\Log\), not a Server Audit Specification output path; see the note at the top of this article.
Audit File: The ERRORLOG file name (no extension).
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.