Microsoft SQL Server¶

LogSentinel Collector supports MS SQL Server's native audit log and change tracking functionalities

Audit Log¶

In order to setup MS SQL Server audit log, the following should be executed:

USE [master]
GO

CREATE SERVER AUDIT [Audit-LogSentinel]
TO FILE 
(   FILEPATH = N'C:\auditlogs\'
    ,MAXSIZE = 256 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '69691cea-886a-4740-8e17-c5654e75aea5'
)
ALTER SERVER AUDIT [Audit-LogSentinel] WITH (STATE = ON)
GO

USE [database_name]


CREATE DATABASE AUDIT SPECIFICATION LogSentinelAuditSpec
FOR SERVER AUDIT [Audit-LogSentinel]
     ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES 
ON DATABASE::database_name BY PUBLIC);

ALTER DATABASE AUDIT SPECIFICATION [LogSentinelAuditSpec] WITH (STATE = ON);

If per-server audit specifications are needed, the following should be executed

CREATE SERVER AUDIT SPECIFICATION LogSentinelAuditServerSpec
FOR SERVER AUDIT [Audit-LogSentinel]
ADD(APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD(AUDIT_CHANGE_GROUP),
ADD(BACKUP_RESTORE_GROUP),
ADD(DATABASE_CHANGE_GROUP),
ADD(DATABASE_LOGOUT_GROUP),
ADD(DATABASE_OBJECT_ACCESS_GROUP),
ADD(DATABASE_OBJECT_CHANGE_GROUP),
ADD(DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD(DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD(DATABASE_OPERATION_GROUP),
ADD(DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD(DATABASE_PERMISSION_CHANGE_GROUP),
ADD(DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD(DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
ADD(DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD(DBCC_GROUP),
ADD(FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD(FAILED_LOGIN_GROUP),
ADD(FULLTEXT_GROUP),
ADD(LOGIN_CHANGE_PASSWORD_GROUP),
ADD(SCHEMA_OBJECT_CHANGE_GROUP),
ADD(SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD(SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD(SERVER_OBJECT_CHANGE_GROUP),
ADD(SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD(SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD(SERVER_PERMISSION_CHANGE_GROUP),
ADD(SERVER_PRINCIPAL_CHANGE_GROUP),
ADD(SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD(SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD(SERVER_STATE_CHANGE_GROUP),
ADD(TRANSACTION_GROUP),
ADD(USER_CHANGE_PASSWORD_GROUP),
ADD(USER_DEFINED_AUDIT_GROUP);

GO  

ALTER SERVER AUDIT SPECIFICATION [LogSentinelAuditServerSpec] WITH (STATE = ON);

(Optionally, ADD(SCHEMA_OBJECT_ACCESS_GROUP) can be used, however it generates too many entries. Also optional is ADD(TRACE_CHANGE_GROUP) and ADD(SERVER_OPERATION_GROUP))

If authentication events are needed:

ADD(BROKER_LOGIN_GROUP),
ADD(LOGOUT_GROUP),
ADD(SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD(SUCCESSFUL_LOGIN_GROUP),
ADD(DATABASE_MIRRORING_LOGIN_GROUP),

Change Tracking¶

In order to setup Change tracking, the connected user should be given the necessary permissions:

use <database>;
CREATE LOGIN logsentinel WITH PASSWORD = '<password>';
CREATE USER logsentinel FOR LOGIN logsentinel;
GRANT SELECT ON DATABASE::<database> TO logsentinel;
GRANT ALTER ON DATABASE::<database> TO logsentinel;
GRANT VIEW CHANGE TRACKING ON SCHEMA::dbo TO logsentinel;
GRANT SELECT ON DATABASE::master TO logsentinel;

LogSentinel collector can also listen to login events through the error log

use [master]
GRANT EXECUTE ON xp_readerrorlog to logsentinel;
GRANT VIEW SERVER STATE to logsentinel; -- optional, only if login monitoring is needed

In order to enable reading the login audit log, it has to be enabled - see here for details

Microsoft SQL Server¶

Audit Log¶

Change Tracking¶

Login logs¶