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 RestartAuto 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
AutoRestartelement to the XMLA statement.
Limited number of tracefilesTo 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 "
ImplementationThe XMLA statement to implement the Audit Log:
<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" />
OperationThis DMX statement shows what traces that are running:
This XMLA statement deletes the trace. The logfiles (.xel) will not be deleted.
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.