CODEWITHSUNDEEP
sqlsql-server-2012audit
Steve Townsend
Steve Townsend

Reputation: 54178

SQL Server 2012 Audit Report Generation

Trying to work out whether audit files output by one server can be read without CONTROL SERVER access on that server. The MSDN docs suggest this is possible:

Even when the Database Engine is writing to a file, other Windows users can read the audit file if they have permission. The Database Engine does not take an exclusive lock that prevents read operations.

and also:

We recommend that you generate audit reports from a separate instance of SQL Server, such as an instance of SQL Server Express, to which only Audit Administrators or Audit Readers have access. By using a separate instance of the Database Engine for reporting, you can help prevent unauthorized users from obtaining access to the audit record.

In short, can I do this?

[Clarification] The critical part of the q is, can you access the file using sys.fn_get_audit_file from a separate DB, without having admin access on the DB from which the audit information is created. That way we can have audit readers with filesystem access separate from DBAs with DB admin access. Sorry for not making this clear initially.

Speaking with respect to your answer, can this query be run from an unrelated SQL Mgmt Studio/DB, by somebody who is not a DBA on the original DB?

SELECT 
    event_time, action_id, session_id, object_id, class_type, 
    database_principal_name, database_name, object_name, statement
FROM 
    sys.fn_get_audit_file('\\Temp\Audit\*',NULL,NULL);

Upvotes: 4

Views: 3148

Answers (1)

milivojeviCH
milivojeviCH

Reputation: 1608

Indeed, this works.

USE [master]
GO

CREATE SERVER AUDIT [SQL2012-Audit-20121214-Demo]
TO FILE 
(   FILEPATH = N'\\Temp\Audit'
    ,MAXSIZE = 2 MB
    ,MAX_FILES = 32
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 2000,ON_FAILURE = CONTINUE)
GO

ALTER SERVER AUDIT [SQL2012-Audit-20121214-Demo] WITH (STATE = ON);

USE [Performance]
GO

CREATE DATABASE AUDIT SPECIFICATION [SQL2012-DBAudit-20121214-Demo]
FOR SERVER AUDIT [SQL2012-Audit-20121214-Demo]
ADD (SELECT,INSERT,DELETE,UPDATE,EXECUTE ON DATABASE::[Performance] BY [dbo])
WITH (STATE = ON);
GO

After the server audit and the database audit were put in place and activated, the first audit file was immediately created and it was impossible to delete it because Windows stated that the file is in use.

However, selecting from the file works all the time. Here's the "workload" with activity that is supposedly caught by the audit set-up:

SELECT * INTO partition_stats_4 FROM Performance.sys.dm_db_partition_stats
SELECT * INTO partition_stats_3 FROM Performance.sys.dm_db_partition_stats
SELECT * INTO partition_stats_2 FROM Performance.sys.dm_db_partition_stats
SELECT * INTO partition_stats_1 FROM Performance.sys.dm_db_partition_stats
SELECT * INTO partition_stats   FROM Performance.sys.dm_db_partition_stats

DELETE FROM partition_stats
DELETE FROM partition_stats_1
DELETE FROM partition_stats_2
DELETE FROM partition_stats_3
DELETE FROM partition_stats_4

DROP TABLE partition_stats_4
DROP TABLE partition_stats_3
DROP TABLE partition_stats_2
DROP TABLE partition_stats_1
DROP TABLE partition_stats

And here's the result:

SELECT 
    event_time, action_id, session_id, object_id, class_type, 
    database_principal_name, database_name, object_name, statement
FROM 
    sys.fn_get_audit_file('\\Temp\Audit\*',NULL,NULL);

SQL Server 2012 - DB Audit Results

By the way, this is exactly the same pattern when it comes to the server side trace files. We have traces running all the time and the files are "query-able" without any trouble.

Happy auditing!

Upvotes: 2

Related Questions