En que lío nos encontramos cuando tenemos la necesidad de auditar un motor de MS-SQL Server de versión Standar :(, nos preguntamos porque no nos consultaron antes (área de seguridad), si lo hicieron por reducir costos o si quieren que nos rompamos la cabeza buscando la forma de auditarlo, sabemos que las versiones Enterprise con su Wizard, nos permiten generar la auditoria a varios clics de mouse.
Pero ya estamos en este escenario, así que a trabajar sin chistar. Luego de buscar varias alternativas y probar, encontramos la forma de que cada evento de tipo DDL (de servidor o base de datos) pueden ser registrado por Triggers, de esta manera podemos almacenar dicho eventos en una base de datos de auditoría o si utilizamos alguna solución de monitoreo de eventos que los mismos sea impactados en los eventos de windows (Source: MSSQLxxxx Event ID: 17063).
En esta entrada le vamos a dejar los pasos para generar la auditoria en el registro de eventos de Windows.
Aclaración: No utilicen estos pasos en ambientes productivos, ya que ante algún error puede inutilizar tu base de datos o el motor SQL, antes de llevarlo a Producción asegurate su correcto funcionamiento.
I. Requisitos
Se debe asignar permisos al usuario que ejecuta el servicio del motor SQL para que pueda ejecutar el store procedure XP_LOGEVENT:
Ejecutar: grant execute on xp_logevent to [DOMINIO\NombreDeUsuario]
Verificar que el Compatibility Level de la base de datos sea mayor o igual a 2008.
II. Versión SIN Base de Datos de Auditoría
1) Creamos el Trigger para el Servidor
Con este Trigger auditaremos los eventos generados de tipo servidor o DDL_SERVER_LEVEL_EVENTS
| USE [master]
GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [TR_DDL_ServerEvents] ON ALL SERVER — WITH ENCRYPTION FOR DDL_SERVER_LEVEL_EVENTS AS SET NOCOUNT ON; — SET ARITHABORT ON; — Creamos el Evento @@EventID DECLARE @EventData XML = EVENTDATA(); DECLARE @ip VARCHAR(32) = ( SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID ); declare @eventID NVARCHAR(3000) = ‘ -Database: ‘ + DB_NAME() + ‘ -Hostname: ‘ + HOST_NAME() + ‘ ‘+ ‘ -EventType: ‘ + @EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘NVARCHAR(100)’) +’ -Usuario: ‘ + SUSER_SNAME() +’ -SQL: ‘+ @EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘NVARCHAR(2000)’); EXEC master.dbo.xp_logevent 60120, @eventID, WARNING GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [TR_DDL_ServerEvents] ON ALL SERVER GO |
Aquí definimos que el evento será de ID 60120 (pueden elegir el que deseen)
2) Creamos Trigger para la base de Datos
Con este trigger auditaremos eventos de tipo DDL_DATABASE_LEVEL_EVENTS en las bases de datos (se deberá crear uno por cada base de datos).
| USE [NOMBREBD]
GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO — ============================================= — Author: infoINseg — Create date: 22-07-2015 — Description: Trigger Audit DDL FOR DATABASE — ============================================= CREATE TRIGGER [DDL_AUDITDB_NOMBREBD] ON DATABASE WITH ENCRYPTION FOR DDL_DATABASE_LEVEL_EVENTS AS SET NOCOUNT ON; — SET ARITHABORT ON; — Creamos el Evento @@EventID DECLARE @EventData XML = EVENTDATA(); DECLARE @ip VARCHAR(32) = ( SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID ); declare @eventID NVARCHAR(3000) = ‘Database:’ + DB_NAME() + ‘ -Hostname:’ + HOST_NAME() + ‘ -EventType:’ + @EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘NVARCHAR(100)’) +’ -Usuario:’ + SUSER_SNAME() +’ -SQL:’+ @EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘NVARCHAR(2000)’); EXEC master.dbo.xp_logevent 60130, @eventID, WARNING GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [DDL_AUDITDB_NOMBREBD] ON DATABASE GO |
Aquí definimos que el evento será de ID 60130 (pueden elegir el que deseen)
3) Para Monitorear el estado de los Triggers
Para monitorear el estado de los Triggers creamos un Job que monitoreo su estado y nos informe en caso de que se encuentre funcionando o halla sido deshabilitado o eliminado.
Ejemplo:
| /* ADVERTENCIA: Se debe crear por cada Base de Datos Auditada */
IF EXISTS (select * from NombreDB.sys.triggers where name = ‘DDL_AUDITDB_NombreDB’ and is_disabled = ‘0’) BEGIN EXEC master.dbo.xp_logevent 60200, ‘Triggers DDL_AUDITDB_NombreDB OK’, WARNING END ELSE EXEC master.dbo.xp_logevent 60201, ‘Trigger DDL_AUDITDB_NombreDB Eliminado o Deshabilitado ‘, ERROR /* ADVERTENCIA: Se debe crear por cada Base de Datos Auditada */
IF EXISTS (SELECT * from sys.server_triggers where name = ‘TR_DDL_ServerEvents’ and is_disabled = ‘0’) BEGIN EXEC master.dbo.xp_logevent 60202, ‘Triggers TR_DDL_ServerEvents OK’, WARNING END ELSE EXEC master.dbo.xp_logevent 60203, ‘Trigger TR_DDL_ServerEvents Eliminado o Deshabilitado’, ERROR |
III. Ejemplos de Eventos recolectados:
Drop Database
Drop Table
Alter Table
Cambio de password al usuario TEST_EVT
IV. Referencias:
1) https://technet.microsoft.com/es-es/library/bb522542(v=sql.105).aspx
3) Ver Versión de SQL https://support.microsoft.com/es-es/kb/321185