SQL Server Audit – Unable to Find Audit Specification for Server Level Audit

auditloginssql server

I am trying to use 'SERVER_PRINCIPAL_CHANGE_GROUP' audit action on a SQL Server to capture the sp_addlogin events. But when I try to create a Server level audit using GUI, I am not seeing the Audit action type drop down when I try to create the audit. Any help on how to do this ? I just want to capture who added a server level login using the SQL Server audits.

Best Answer

We can't troubleshoot pointing and clicking through a GUI, because we don't know exactly what you did or what you might have missed.

You need to create a server level audit (which is like a container for specific audit definitions) and a server level audit specification (which is the part that dictates the auditing).

I suggest looking at the documentation for examples using DDL (e.g. CREATE SERVER AUDIT and CREATE SERVER AUDIT SPECIFICATION). This wasn't too hard to work out:

USE master;
GO

CREATE SERVER AUDIT ServerAudit
TO FILE 
(
    FILEPATH = N'C:\temp\',
    MAXSIZE = 0 MB,
    MAX_ROLLOVER_FILES = 2147483647,
    RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE,
    AUDIT_GUID = '0da5fd7b-8218-486f-8641-c31465cae657'
);
GO

ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION [AuditPrincipals]
FOR SERVER AUDIT [ServerAudit]
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON);
GO

So then I did this:

CREATE LOGIN floob WITH PASSWORD = 'blat', CHECK_POLICY = OFF;

And I saw this in the audit log:

Related Solutions

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:

CREATE TRIGGER [ddl_log] 
ON ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS 
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DECLARE @data XML
SET @data = EVENTDATA() 

EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
   GETDATE(),
   CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'), 
   CONVERT(NVARCHAR(100), HOST_NAME()),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
   ) ;
REVERT 
END

As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.

Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.

SQL Server Audit with Over 1,000 Entries – Best Practices

Have you tried reading your audit using sys.fn_get_audit_file?

-- query all audit files in the D:\audit directory
SELECT *
 FROM sys.fn_get_audit_file('D:\audit\*.sqlaudit', default, default)

The CREATE SERVER audit TSQL command you used did not specify

MAXSIZE = { max_size } The default value is UNLIMITED.

MAX_ROLLOVER_FILES ={ integer | UNLIMITED} The default value is UNLIMITED.

MAX_FILES =integer Specifies the maximum number of audit files that can be created. Does not rollover to the first file when the limit is reached. When the MAX_FILES limit is reached, any action that causes additional audit events to be generated will fail with an error.

Not sure what the default is for MAX_FILES but its probably the FS limit which is somewhere around two billion.

So no there should not be any limit on the size of the audit files generated or the number of rows stored within them.

Best Answer

Related Solutions

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

SQL Server Audit with Over 1,000 Entries – Best Practices

Related Question