Discussion :

[hmira]

Ma question fait référence à l’excellent turorial réalisé par David BARBARIN (mikedavem) : Les audits avec SQL Server 2008
et plus précisément au paragraphes et code T-SQL relatifs à l’objectif "Auditer et enregistrer les changements d'état du serveur de bases de données".
Exemple :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell' , 1;
GO
RECONFIGURE;

Ma question est la suivante :
Existe-t-il un moyen de visualiser à travers la fonction fn_get_audit_file le détails de l’évènement (c.à.d. le détails de l’opération effectuée) ? En effet, actuellement le résultat du select ci-dessous (colonne f.statement) ne donne pas le détails de l’évènement( par exemple valeur de l’option de configuration avant et après Exec sp_configure). La colonne f.statement affiche uniquement la mention "RECONFIGURE;" !

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
	f.event_time, 
	f.session_id,
	a.name AS action_name,
	m.class_type_desc,
	a.covering_action_name,
	f.statement,
	f.server_principal_name
FROM fn_get_audit_file('E:\SQL2008R2\Audit\Audit_Srv_File_Change_Status_Server*', DEFAULT, DEFAULT) AS f
INNER JOIN sys.dm_audit_class_type_map AS m
ON m.class_type = f.class_type
INNER JOIN sys.dm_audit_actions AS a
ON a.action_id = f.action_id 
ORDER BY f.event_time DESC

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
event_time	session_id	action_name	class_type_desc	covering_action_name	statement
2014-05-07 09:49:24.7713299	56	ALTER SETTINGS	SERVER	SERVER_OPERATION_GROUP	RECONFIGURE;

Pour trouver le détail de l’opération effectuée, il faut aller consulter le détail des journaux windows (nœud application), faire le cas échéant un filtre sur l’évennement 15457. Pour afficher l’exemple de résultat ci-dessous :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Evénement 15457, MSSQLSERVER 
[Onglet général]  
   L'option de configuration 'xp_cmdshell' est passée de 0 à 1. Pour installer, exécutez l'instruction RECONFIGURE.

Ce serait beaucoup plus pratique, si on pouvait avoir cette même information directement au travers la fonction fn_get_audit_file(..).

Merci,

A+,

[mikedavem]

Hello,

Oui tu as raison mais malheureusement pour le moment c'est un manque au niveau de l'audit SQL Server (j'avais demandé à Microsoft à l'époque mais il faudrait peut être voir s'il n'y a pas d'item connect sur le sujet ... je vais regarder si j'ai un peu de temps)

Un moyen de contourner serait par exemple de recouper avec le journal des erreurs SQL Server par exemple en se servant de la date (qui est en UTC si je me souviens bien) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC xp_readerrorlog 0, 1, N'Configuration option', NULL, 2014-05-07 11:49:24, '2014-05-07 11:49:25', N'ASC'

Ce n'est pas parfait comme méthode bien entendu ...

++

[hmira]

Je réagis un peu tardivement. En effet, comme beaucoup d’entre vous j’ai fait le pont !

Merci David pour ce complément d’information.

La solution que vous proposez me parait effectivement une bonne idée (peut-être même la seule solution) pour contourner le problème, et ce, en attendant que Microsoft apporte des améliorations sur le système de l’audit, notamment le contenu de la colonne statement en cas de modification d’une option de configuration du serveur.

Sachant qu’on ne pourra que rapprocher, par exemple à une seconde près, les 2 colonnes LogDate (de xp_readerrorlog) et Event_Time (de fn_get_audit_file). D’après les tests que j’ai effectués, Il n’y a pas une égalité parfaite des 2 colonnes. En effet, cela s’explique par le type différent des 2 colonnes :
LogDate (résultat de xp_readerrorlog) est de type DateTime et représente une date et heure locale, alors que Event_Time (résultat de fn_get_audit_file) est de type datetime2 et représentant une date et heure en UTC.

Donc avec quelques transformations (UTC en heure locale), filtre sur la colonne Text, et un rapprochement des 2 colonnes (LogDate et Event_Time), la solution de contournement que vous proposez permet d’obtenir le résultat attendu, c.à.d. le contenu de la colonne ‘Text’ de xp_readerrorlog où on dispose enfin du détail de l’évènement.

Merci,

A+,