Skip to content

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;

Login logs

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