Die Log- oder Trace-Möglichkeiten von Microsoft SQL Server sind vielfältig. Dieser Blogbeitrag gibt eine kleine Einführung in eine Trace-Möglichkeit, die nicht allzu bekannt zu sein scheint: dem SQL-Server-Audit. Auditing ist dann sinnvoll, wenn man wissen will, was sich auf einem SQL Server oder in einer SQL-Server-Datenbank so ereignet. Beim Audit können die Informationen entweder in ein Log der Windows-Ereignisanzeige oder in eine gesonderte Datei geschrieben werden. Bei beiden ist jedoch darauf zu achten, dass sowohl der Service-Account des SQL Servers als auch der Nutzer entsprechende Rechte besitzt. In dieser kleinen Einführung liegt der Fokus darauf, die Audit-Informationen in einem separaten File abzulegen.

Einrichten

Das Einrichten eines SQL-Server-Audits erfolgt zweistufig. Als erstes muss ein Audit erstellt werden, im zweiten Schritt findet die Audit-Spezifikation statt. Die Spezifikation kann innerhalb einer Datenbank oder auf Serverebene eingerichtet werden. Audits sind immer auf Serverebene eingerichtet.
Im SQL Server Management Studio werden die Audits auf Serverebene im Ordner Sicherheit (bzw. Security) angezeigt. Dafür gibt es in diesem Ordner zwei Unterordner: Überwachungen (bzw. Audits) und Serverüberwachungsspezifikationen (bzw. Server Audit Specifications). Zum Erstellen eines Audits legt man einfach im Kontextmenü von Audits ein neues Audit an.

Abb. 1: Anlegen eines neuen Audits

Hier gibt man dem Audit einen Namen und stellt das Queue Delay ein (oder lässt dies auf dem eingestellten Wert von einer Sekunde). Zudem nimmt man Einstellungen vor, wie SQL Server reagieren soll, wenn ein Audit-Log-Fehler auftritt und teilt mit, wo das Audit-File erstellt werden soll.

Nach dem Anlegen einer Überwachung ist diese zunächst deaktiviert, kann aber im Kontextmenü per Überwachung aktivieren schnell aktiviert werden.

Der zweite Schritt ist das Einrichten einer Spezifikation im Order Serverüberwachungsspezifikationen. Diese Spezifikationen können auf Server- als auch auf Datenbankebene erstellt werden.

Abb. 2: Einrichtung der Spezifikation

Hier wiederum wird Folgendes eingestellt: der Name der Spezifikation, das Audit und der Überwachungsaktionstyp (Audit Action Type). Im Beispiel wird gezeigt, welche Objekte abgefragt werden. Deshalb findet sich im Überwachungsaktionstyp folgende Einstellung: Audit Action Type = SELECT; Object Type = DATABASE; Object: DBName; Principal: public. Es muss in diesem Falle genau ein Principal angegeben werden (Hinweis: Principal ist der allgemeine Begriff für alle SQL-Server-Objekte, denen Rechte zugewiesen werden können, also hauptsächlich Rollen, Logins und User). Um in diesem Beispiel alle User zu erfassen, wird die spezielle Rolle public gewählt, welcher automatisch alle User angehören.

Beim Stöbern in den Überwachungstypen wird einem schnell klar, dass im SQL Server möglicherweise nichts unbeobachtet bleibt. Einen Favoriten gibt es noch: DATABASE_OBJECT_CHANGE_GROUP kann verwendet werden, um seine eigene Arbeit an einem Kundensystem zu loggen. Das hilft beim Aufschreiben von Tätigkeiten ganz gut (Da geht aber auch ein Server-Trigger, aber das ist ein anderes Thema).

Auswerten

Beim Audit entstehen Dateien mit sehr kryptischen Namen, wie z. B.:

Audit%5Object%5Access_4248D1CE-2C8F-4C29-96AB-BF1F3518B38B_0_130519536594130000.sqlaudit

Den Inhalt dieses Files kann man sich über das Kontextmenü des Audit ansehen oder mit der Funktion sys.fn_get_audit_file:

SELECT *
FROM sys.fn_get_audit_file ('C:\logs\*.sqlaudit', default, default)

Den genauen Namen anzugeben, ist nicht empfehlenswert, da möglicherweise auch mehrere Files vorhanden sein können. Daher wurde der * im Pfad zu den Audit-Dateien gesetzt.

Das Ergebnis ist in beiden Fällen aber nicht wirklich hilfreich, v. a. dann, wenn durch das Audit sehr viele Datensätze entstehen. Das ist hier auch passiert: ein einziger P_Transform_All-Prozess auf einer kleinen Testdatenbank für DeltaMaster-Modeler-Entwicklungen ergab etwas mehr als 4000 Einträge.

Aber mit etwas SQL kann man interessante Informationen gewinnen, z. B. welche Objekte am häufigsten abgefragt wurden:

SELECT schema_name, object_name, Count(*)
FROM sys.fn_get_audit_file ('C:\logs\*.sqlaudit', default, default)
GROUP BY schema_name, object_name
ORDER BY Count(*) desc

Oder andersherum: welche Views sind im Audit-Zeitraum sehr selten oder gar nicht verwendet worden:

SELECT v.name, schema_name(v.schema_id), Count(a.Class_type)
FROM sys.views v
LEFT JOIN sys.fn_get_audit_file ('C:\logs\*.sqlaudit', default, default) a
      ON v.name = a.object_name
      and schema_name(v.schema_id) = a.schema_name
GROUP BY v.name, schema_name(v.schema_id)
ORDER BY Count(a.class_type)

Außerdem gibt es noch diverse DMVs, um Informationen zu Audits abzufragen. Hier wird nur auf ein Beispiel verwiesen: sys.dm_server_audit_status hilft dabei, zu bestimmen, welche Audits im Moment aktiv sind.

Rechte

User, die Audits anlegen oder ändern wollen, müssen über das Serverrecht ALTER ANY SERVER AUDIT verfügen. Auf Datenbankebene ist ALTER ANY DATABASE AUDIT notwendig.

Anmerkungen

Das Ablegen von Informationen in den Audit-Files kostet natürlich. In erster Linie können solche Dateien sehr groß werden. Es sollte also darauf geachtet werden, dass sie keine Festplatten zum Überlaufen bringen. Gezieltes bzw. eingrenzendes Audit ist dann geboten.