locked
SERVER AUDIT not working RRS feed

  • Question

  • Hello everybody,

    I created a server audit to monitor when a privilege elevation happens on my SQL Server.

    Sadly, it's not working at all... :(

    What am I doing wrong?

    Thanks in advance for the help.

    Kind Regards,

    Alexis.

    USE master
    GO
    /* Create Server Audit */
    CREATE SERVER AUDIT [Test_AuditToFile] TO FILE (FILEPATH = 'C:\Program Files\Microsoft SQL Server\MSSQL13.REP2016\MSSQL\Log\', MAXSIZE = 0 MB, MAX_ROLLOVER_FILES = 2147483647, RESERVE_DISK_SPACE = OFF) WITH (QUEUE_DELAY = 1000, ON_FAILURE = FAIL_OPERATION)
    CREATE SERVER AUDIT SPECIFICATION [Test_ServerAuditSysAdminRole] FOR SERVER AUDIT [Test_AuditToFile] ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
    
    /* Start Server Audit */
    ALTER SERVER AUDIT SPECIFICATION [Test_ServerAuditSysAdminRole] WITH (STATE = ON)
    ALTER SERVER AUDIT [Test_AuditToFile] WITH (STATE = ON);
    
    /* Create Login and Add it to the SysAdmin Server Role */
    CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [test]
    
    /* Get Audit data */
    DECLARE @LogFilePath nvarchar(255) = (SELECT log_file_path + log_file_name FROM sys.server_file_audits  WHERE name = 'Test_AuditToFile')
    SELECT * FROM sys.fn_get_audit_file (@LogFilePath, default, default) af
    
    /* Remove Login */
    DROP LOGIN [test]
    
    /* Stop Audit */
    ALTER SERVER AUDIT [Test_AuditToFile] WITH (STATE = OFF);
    ALTER SERVER AUDIT SPECIFICATION [Test_ServerAuditSysAdminRole] WITH (STATE = OFF)
    	
    /* Remove Audit */
    DROP SERVER AUDIT SPECIFICATION [Test_ServerAuditSysAdminRole]
    DROP SERVER AUDIT [Test_AuditToFile]
    	

    Monday, May 8, 2017 4:34 PM

Answers

  • It's always when you post something on a Forum that you find the solution...

    So the problem was the name of the audit file.

    I shouldn't have put the .sqlaudit extension, and I should have had the * symbol to ask every files linked to this audit.

    So to get the file name, now, I'm using this line of code :

    DECLARE @LogFilePath nvarchar(255) = (SELECT log_file_path + REPLACE(log_file_name, '.sqlaudit', '*') FROM sys.server_file_audits WHERE name = 'Test_AuditToFile') 

    Hope it would help someone in the future.

    Kind Regards,

    Alexis.

    • Marked as answer by H2Apps Monday, May 8, 2017 5:39 PM
    • Edited by H2Apps Monday, May 8, 2017 5:40 PM Bad Formating
    Monday, May 8, 2017 5:38 PM

All replies

  • Hi,

    Please, paste SELECT @@VERSION results

    Monday, May 8, 2017 5:25 PM
  • It's always when you post something on a Forum that you find the solution...

    So the problem was the name of the audit file.

    I shouldn't have put the .sqlaudit extension, and I should have had the * symbol to ask every files linked to this audit.

    So to get the file name, now, I'm using this line of code :

    DECLARE @LogFilePath nvarchar(255) = (SELECT log_file_path + REPLACE(log_file_name, '.sqlaudit', '*') FROM sys.server_file_audits WHERE name = 'Test_AuditToFile') 

    Hope it would help someone in the future.

    Kind Regards,

    Alexis.

    • Marked as answer by H2Apps Monday, May 8, 2017 5:39 PM
    • Edited by H2Apps Monday, May 8, 2017 5:40 PM Bad Formating
    Monday, May 8, 2017 5:38 PM