Hi everyone
Fortisiem does not collect events that occur within the database manager, but rather events related to Windows.
We configure everything on the SQL Server side: we create the user, the tables, the triggers, the audits. And we can view the events related to the audit from the database manager but not from Fortisiem. What could be missing?
Annex part of the configuration:
- Configuring login auditing using SQL Server Management Studio:
Enable C2 audit trail.
- Creation of the PH_Events database, the dbo.DDLEvents and dbo.LogonEvents tables and triggers with the following Scripts:
CREATE DATABASE PH_Events
GO
USE PH_Events;
GO
CREATE TABLE PH_Events.dbo.DDLEvents
(
XMLEvent XML,
DatabaseName VARCHAR(MAX),
EventTime DATETIME DEFAULT (GETDATE()),
EventType VARCHAR(MAX),
SPID VARCHAR(MAX),
ServerName VARCHAR(MAX),
LoginName VARCHAR(MAX),
ObjectName VARCHAR(MAX),
ObjectType VARCHAR(MAX),
SchemaName VARCHAR(MAX),
CommandText NVARCHAR(MAX)
)
GO
CREATE TABLE PH_Events.dbo.LogonEvents
(
XMLEvent XML,
EventTime DATETIME,
EventType VARCHAR(MAX),
SPID VARCHAR(MAX),
ServerName VARCHAR(MAX),
LoginName VARCHAR(MAX),
LoginType VARCHAR(MAX),
SID VARCHAR(MAX),
HostName VARCHAR(MAX),
IsPooled VARCHAR(MAX),
AppName VARCHAR(MAX)
)
GO
USE PH_Events
GO
CREATE USER [AOPerfLogin] FOR LOGIN [AOPerfLogin]
GO
ALTER ROLE [db_owner] ADD MEMBER [AOPerfLogin]
GO
USE master;
GO
CREATE TRIGGER PH_Database_Level_Events on DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @eventData AS XML;
SET @eventData = EVENTDATA();
INSERT INTO PH_Events.dbo.DDLEvents(EventTime, EventType, SPID, ServerName, LoginName, ObjectName, ObjectType, SchemaName, DatabaseName, CommandText, XMLEvent)
VALUES(cast(@eventData.query('data(//PostTime)') as varchar(MAX)),
cast(@eventData.query('data(//EventType)') as varchar(MAX)),
cast(@eventData.query('data(//SPID)') as varchar(MAX)),
cast(@eventData.query('data(//ServerName)') as varchar(MAX)),
cast(@eventData.query('data(//LoginName)') as varchar(MAX)),
cast(@eventData.query('data(//ObjectName)') as varchar(MAX)),
cast(@eventData.query('data(//ObjectType)') as varchar(MAX)),
cast(@eventData.query('data(//SchemaName)') as varchar(MAX)),
cast(@eventData.query('data(//DatabaseName)') as varchar(MAX)),
cast(@eventData.query('data(//TSQLCommand/CommandText)') AS NVARCHAR(MAX)),
@eventData
);
GO
USE master;
GO
CREATE TRIGGER PH_DDL_Server_Level_Events
ON ALL SERVER
FOR DDL_ENDPOINT_EVENTS, DDL_LOGIN_EVENTS, DDL_GDR_SERVER_EVENTS, DDL_AUTHORIZATION_SERVER_EVENTS,
CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE, GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE
AS
DECLARE @eventData AS XML;
SET @eventData = EVENTDATA();
insert into PH_Events.dbo.DDLEvents(EventTime, EventType, SPID, ServerName, LoginName, ObjectName, ObjectType, SchemaName, DatabaseName, CommandText, XMLEvent)
values(cast(@eventData.query('data(//PostTime)') as varchar(MAX)),
cast(@eventData.query('data(//EventType)') as varchar(MAX)),
cast(@eventData.query('data(//SPID)') as varchar(MAX)),
cast(@eventData.query('data(//ServerName)') as varchar(MAX)),
cast(@eventData.query('data(//LoginName)') as varchar(MAX)),
cast(@eventData.query('data(//ObjectName)') as varchar(MAX)),
cast(@eventData.query('data(//ObjectType)') as varchar(MAX)),
cast(@eventData.query('data(//SchemaName)') as varchar(MAX)),
cast(@eventData.query('data(//DatabaseName)') as varchar(MAX)),
cast(@eventData.query('data(//TSQLCommand/CommandText)') AS NVARCHAR(MAX)),
/** DB_NAME(),**/
@eventData);
GO
Use master;
GO
CREATE TRIGGER PH_LoginEvents ON ALL SERVER
WITH EXECUTE AS self FOR LOGON
AS
BEGIN
DECLARE @event XML
SET @event = EVENTDATA()
INSERT INTO PH_Events.dbo.LogonEvents(EventTime,EventType,SPID,ServerName,LoginName,LoginType,SID,HostName,IsPooled,AppName,XMLEvent)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(128)),
CAST(@event.query('/EVENT_INSTANCE/SPID/text()') AS VARCHAR(128)),
CAST(@event.query('/EVENT_INSTANCE/ServerName/text()') AS VARCHAR(128)),
CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(128)),
CAST(@event.query('/EVENT_INSTANCE/LoginType/text()') AS VARCHAR(128)),
CAST(@event.query('/EVENT_INSTANCE/SID/text()') AS VARCHAR(128)),
CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(128)),
CAST(@event.query('/EVENT_INSTANCE/IsPooled/text()') AS VARCHAR(128)),
APP_NAME(),
@event)
END;
- Creating audits and server audit specification properties within SQL Server Management Studio.
I would greatly appreciate your help
