The SQL Server Database Engine has the feature Login Auditing, that can create a simple audit log. This log contains among other things service start and stop and logins.
I was approached with a similar requirement on SQL Server Analysis Services (SSAS), but this does not have something like Login Auditing.
SSAS does have tracing features, and can be monitored with
Extended Events. The documentation is mostly written for the Database Engine, but with some trial-and-error it can be implemented on SSAS with a
XMLA statement.
The documentation from Microsoft is not good, but I found a great blogpost with a working example (
http://byobi.com/blog/2013/06/extended-events-for-analysis-services/). To this example I have added some features to meet requirements.
Auto Restart
Auto Restart is required to ensure that the trace is running even after a restart of the SSAS service or the server. This is implemented by adding a
AutoRestart element to the XMLA statement.
Limited number of tracefiles
To ensure that the tracing does not run wild, we decided to limit the size of the logfiles and the number of logfiles. This is by the configuration of the
Event File Target.
The size of each logfile is defined with the option "
max_file_size" with a value i MBs (
MiBs).
The number of logfiles is defined by the option "
max_rollover_files".
Implementation
The XMLA statement to implement the Audit Log:
<Create
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<ObjectDefinition>
<Trace>
<ID>SQLAdmin_Audit</ID>
<Name>SQLAdmin_Audit_2014-W02</Name>
<AutoRestart>true</AutoRestart>
<ddl300_300:XEvent>
<event_session name="xeas"
dispatchLatency="1"
maxEventSize="4"
maxMemory="4"
memoryPartitionMode="none"
eventRetentionMode="allowSingleEventLoss"
trackCausality="true">
<event package="AS" name="AuditLogin" />
<event package="AS" name="AuditLogout" />
<event package="AS" name="ExistingConnection" />
<event package="AS" name="ExistingSession" />
<event package="AS" name="SessionInitialize" />
<target package="Package0" name="event_file">
<parameter name="filename" value="L:\MSSQL_AS_Log\SQLAdmin_Audit.xel" />
<parameter name="max_file_size" value="1024" />
<parameter name="max_rollover_files" value="3" />
</target>
</event_session>
</ddl300_300:XEvent>
</Trace>
</ObjectDefinition>
</Create>
Operation
This
DMX statement shows what traces that are running:
SELECT *
FROM [$System].[DISCOVER_TRACES];
This XMLA statement deletes the trace. The logfiles (.xel) will not be deleted.
<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<TraceID>SQLAdmin_Audit</TraceID>
</Object>
</Delete>
A logfile can be browsed with SQL Server Management Studio. The file can be accessed on a
UNC path.
The original blogentry mentions that the logfile can be read with the
T-SQL function
sys.fn_xe_file_target_read_file, but this is a Database Engine function.
To use this function to get the contents of the logfile – in
XML – the file must be moved or copied to a location where a Database Engine can acces the file.
Posts
