AUDITORÍA SQL Server 2008 / R2 (STANDARD EDITION) Versión 1

 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

1 - Drop Database

Drop Table

2 - Drop Table

Alter Table

3 - Create Table

Cambio de password al usuario TEST_EVT

4 - Cambio de Password usuario

 

IV.            Referencias:

1) https://technet.microsoft.com/es-es/library/bb522542(v=sql.105).aspx

2) http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2012/08/20/monitoring-using-eventdata-function.aspx

3) Ver Versión de SQL https://support.microsoft.com/es-es/kb/321185

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s