Auditoría de SQL Server – cómo ser alertado acerca de eventos de auditoría importantes

Mientras que numerosos métodos nativos de auditoría están disponibles para SQL Server, ninguno de ellos provee una característica por defecto para generar una alerta cuando un evento específico de SQL Server es detectado. Veremos cómo utilizar soluciones nativas y también una solución inmediata, ApexSQL Audit.

SQL Server Audit

SQL Server Audit permite establecer una auditoría automática y la creación de especificaciones de auditoría para eventos a nivel de servidor y base de datos, las cuales luego pueden ser escritas a los registros de seguridad o eventos de la aplicación o al archivo de auditoría. SQL Server Audit pone disponibles las herramientas y procesos que deben ser habilitados, almacenados y luego para ver auditorías en los objetos del servidor y la base de datos.

SQL Server Audit permite grabar auditorías a nivel de SQL Server y/o la base de datos. El evento de auditoría será registrado cada vez cuando las acciones auditables sean encontradas.

El siguiente ejemplo le mostrará cómo ser notificado por un correo electrónico en cualquier evento de seguridad relacionado con cambios en el inicio de sesión de SQL Server.

Primero, cree un nuevo SQL Server Audit que enviará todos los eventos de auditoría a un archivo .audit.

En Object Explorer, navegue a la carpeta Security -> Audits y desde el menú contextual seleccione New Audit.

Esto abrirá el diálogo Create Audit como el que se ve abajo, pero dependiendo de la versión de SQl Server, la disposición puede ser ligeramente diferente:

Revisemos rápidamente algunas opciones menos específicas que pueden ser configuradas aquí:

Audit name (nombre de la auditoría) es suficientemente explícito.

Queue delay (in milliseconds) (Retraso de la cola en milisegundos) permite definir el tiempo de demora después m del cual los eventos auditados serán escritos a los registros.

Shut down server on audit log failure (Apagar el servidor en caso de un fallo en el registro) forzará al servidor a apagarse cuando el servidor no puede escribir datos al repositorio de auditoría. Esta opción debería ser usada sólo cuando una falla de auditoría podría afectar seriamente la seguridad o integridad del servidor.

Nota: La cuenta usada para la auditoría debe tener el permiso SHUTDOWN para aplicar exitosamente esta opción

Fail operation (operación fallida) evitará que la operación actual del servidor sea ejecutada si SQL Server Audit no puede escribir en el repositorio por alguna razón. A las operaciones que no desencadenan los eventos auditados se les permitirá continuar con la ejecución. Esta opción debería ser usada sólo si la integridad de la auditoría es más importante que la operatividad completa de SQL Server.

La siguiente opción es para elegir el destino donde las auditorías serán registradas:

Cuando el Registro de Seguridad de Windows y el Registro de la Aplicación son seleccionados para almacenar eventos de auditoría, todas las otras opciones en este diálogo serán deshabilitadas. El comportamiento, en este caso, será controlado por Windows de acuerdo a los ajustes para el registro seleccionado. Generalmente, la opción más amigable al usuario que asegura la seguridad más alta, así como la mejor estabilidad y desempeño, es la opción File, la cual almacenará el archivo binario del registro en el sistema de archivos de Windows, así que basaremos nuestra solución en esta opción. El resto de las opciones son explícitas, y más información puede ser encontrada aqui

En el caso de que la auditoría tenga que soportar reflejos de bases de datos, un GUID específico debe ser asignado a la auditoría que coincida con el GUID en la base de datos reflejo. Si este es el caso, un script SQL para crear la nueva auditoría debe ser usado, y en nuestro caso se ve así:

USE [master]
GO

CREATE SERVER AUDIT [Audit Login Changes]
TO FILE 
(	FILEPATH = N'C:\SqlAudits\'
	,MAXSIZE = 1024 MB
	,MAX_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = '<enter_appropriate_guid_here>'
)
ALTER SERVER AUDIT [Audit Login Changes] WITH (STATE = ON)
GO

Ahora que el nuevo objeto de SQL Server Audit es creado, el objeto de Server Audit Specifications para el objeto de auditoría creado debe ser establecido.

En Object Explorer navegue a la carpeta Security -> Server Audit Specifications y desde el menú contextual seleccione New Server Audit Specification

El objeto de Especificación de Auditoría de Servidor describe qué eventos a nivel de servidor serán auditados. El único objeto de Especificación de Auditoría de Servidor puede ser creado por el objeto Auditoría de Servidor; de todas maneras, la Especificación de Auditoría de Servidor puede contener múltiples grupos de acciones de auditoría. Para rastrear todos los cambios hecho en inicios de sesión, usuarios y roles, el siguiente Tipo de Acción de Auditoría mostrado en la siguiente imagen debería ser creado. Más acerca de los tipos especificados de Acciones de Auditoría está disponible en el appendice

Y finalmente, después de especificar el nombre para este objeto de Especificación de Auditoría de Servidor e ingresar el nombre del objeto de Auditoría al cual el objeto de Especificación de Auditoría de Servidor estará relacionado, la solución de auditoría de SQL Server está lista.

Para quienes prefieran crear el objeto de Especificación de Auditoría de Servidor usando T-SQL, aquí está un script que lo hará por usted:

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Audit Login]
FOR SERVER AUDIT [Audit Login Changes]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON)
GO

Ahora, después de que el nuevo SQL Server Audit está configurado para recolectar los eventos de seguridad especificados, el sistema que lanzará una notificación cuando el evento ocurra tiene que ser establecido. Primero, un script que es capaz de leer y analizar el archivo .audit y luego enviar un correo electrónico al usuario especificado debe ser creado. Aquí está un ejemplo:

DECLARE @TempTime datetime2;
DECLARE @Counter int;
DECLARE @MailQuery NVARCHAR(MAX);
SET @Counter = 0
SET @TempTime = (SELECT TOP 1 LastEventTime FROM dbo.TempAuditTime)
SET @Counter= (SELECT COUNT (event_time) 
 FROM sys.fn_get_audit_file('C:\SqlAudits\*.sqlaudit', default, default)
 WHERE DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time ) > @TempTime)
 PRINT @Counter
 IF @Counter > 0 

 	BEGIN
	SET @MailQuery = CAST ((SELECT td = DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time), '', 
							td =statement, ''
					FROM sys.fn_get_audit_file('C:\SqlAudits\*.sqlaudit', default, default)
					WHERE DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time ) > @TempTime FOR XML PATH('tr'), TYPE
					) AS NVARCHAR(MAX))

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Security Event Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Event time</th><th>Statement</th>'+
	N'</tr>' +
    @MailQuery +
    N'</table>';
	
 PRINT @tableHTML

 -- Update temp table event time
USE master

	UPDATE dbo.TempAuditTime
   SET [LastEventTime] = SYSDATETIME ()

-- Send Email
EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SecurityEvent', 
		@recipients = 'nikola.dimitrijevic@apexsql.com',
		@body = @tableHTML,
		@body_format = 'HTML',
		@subject = 'Security Event Occured';
		
    	END; 

Este script particular requiere que se cree una tabla dbo.TempAuditTime, ya que será usada para almacenar la información acerca del tiempo cuando ocurrió la última lectura del archivo, y este DateTime será usado para comparación con el tiempo cuando el evento ha ocurrido. Si el tiempo del evento es más nuevo, el correo electrónico con los detalles del evento será enviado.

Para ese propósito, el trabajo de SQL Server puede ser creado para que ejecute este script. Un trabajo de SQL Server Agent permite programar la ocurrencia tan frecuentemente como sea necesario, pero cuán frecuentemente un trabajo será ejecutado depende también de cuán seguido habrá la necesidad de revisar el archivo de repositorio de SQL Server Audit en busca de eventos importantes y luego enviar una alerta de correo electrónico. En nuestro caso, una alerta de correo electrónico es formateada a HTML como una tabla para asegurar una mejor legibilidad de la información importante.

Las desventajas de este método son que requiere un conocimiento avanzado de T-SQL, recursos significativos de SQL Server pueden ser usados dependiendo de la frecuencia del programa, pero también dependen del tamaño del archivo .audit que tiene que ser leído cada vez. Finalmente, requiere el procedimiento completo descrito anteriormente sea repetido manualmente y configurado manualmente para cada tipo de evento y base de datos.

ApexSQL Audit

ApexSQL Audit es una  herramienta de cumplimiento de normas y auditoría de SQL Server que rastrea más de 200 eventos en múltiples instancias de SQL Server y sus objetos y lo graba en un repositorio central a prueba de manipulación. La información capturada está disponible a través de un rango de reportes integrado con un diseñador de reportes personalizados adicional para cumplir con los requerimientos específicos del usuario.

Una de las características que ApexSQL Audit provee es las alertas. Consiste en alertas predefinidas de sistema (monitorean estados de salud básicos como el espacio en disco y el uso del repositorio central), alertas de datos (monitorean eventos capturados) y alertas SQL personalizadas (la habilidad de alertar en cualquier evento y/o métrica que puede ser recuperada con un script SQL).

Para crear alertas de datos en ApexSQL Audit:

1. Inicie la aplicación principal de  ApexSQL Audit

2. Seleccione la pestaña Alerts en el panel izquierdo en la pestaña Manage, haga clic en el botón New.

   

3. Desde el menú desplegable, seleccione Data alert (la opción Custom script alert es descrita abajo).

   

En el texto siguiente nos enfocaremos en las opciones de alertas. Los siguientes pasos generalmente aplican a todos los tipos de alertas con diferencia en condiciones específicas de la alerta.

1. Ingrese la información de la alerta en el diálogo Alert name and notification options. Aquí, el nombre de la alerta, el sujeto y el cuerpo del reporte pueden ser especificados. Los marcadores de posición como $LoginName$ serán reemplazados cuando la alerta sea desencadenada, así que el usuario puede personalizar completamente el formato del reporte de alerta que obtendrá. La opción Limit the number of reports for this alert to one per minute (for each server) asegura que el usuario final no reciba una cantidad desordenada de reportes, en caso de un pico en las alertas.

   

2. Seleccione la instancia SQL Server acerca de la que desea ser alertado en el diálogo Server deployment. Presione Next para continuar configurando las condiciones para la alerta.

   

3. La característica de alerta de ApexSQL Audit utiliza un filtro avanzado que permite una configuración granular de las condiciones de la alerta. Los usuarios pueden establecer las condiciones bajo las cuales el evento desencadenará la alerta. Los eventos pueden ser filtrados usando el nombre de la aplicación, el cliente del anfitrión, el nombre de la base de datos, la operación del servidor y los datos en texto. Todas las condiciones pueden usar los operadores is e is not, mientras que los datos de texto pueden usar adicionalmente los operadores contains y does not contain.

   

   Adicionalmente, para bases de datos con colación sensible a mayúsculas y minúsculas, la casilla Case sensitive debería ser seleccionada.

4. En el siguiente diálogo Actions, el usuario puede elegir si desea tener la notificación de alerta vía correo electrónico. Después de seleccionar la casilla Send this alert report via email, la cuenta de correo electrónico puede ser configurada haciendo clic en el enlace Click here to configure an account for sending e-mail.

   

5. Ingrese la dirección de correo electrónico de destino en el campo To y luego presione el enlace Send test e-mail. Si el correo electrónico de prueba es recibido correctamente, presione Next.

6. El último diálogo Alert summary muestra toda la información relevante acerca del correo electrónico creado. Si todo está correcto, la nueva alerta será creada haciendo clic en OK.

   

En caso de que alguna habilidad de alerta avanzada/personalizada sea necesaria, y la opción Custom script alert es usada en el paso 3, reemplace los pasos del 3 al 6 con lo siguiente:

1. Seleccione la opción Custom script alert, la cual le permitirá ejecutar el script definido por el usuario y desencadenar o no la alerta de acuerdo al resultado del script.

   

2. Ingrese la información de la alerta en el diálogo Alert name and notification options – el nombre de la alerta, la Severidad (Low, Medium o High), el nombre de la alerta, el asunto y el texto del reporte pueden ser especificados.

   

3. Seleccione la instancia de SQL Server acerca de la que desea ser alertado en el diálogo Server deployment. Presione Next para continuar configurando las condiciones para la alerta.

   

4. En el diálogo Conditions, el valor numérico y el script que será ejecutado debe ser especificado. El script deber estar diseñado para retornar un valor numérico, y si este valor numérico es igual o mayor que el valor numérico ingresado, una alerta será desencadenada. En nuestro ejemplo, un script retornará el tamaño de la base de datos AdventureWorks2014 en MB. Si el tamaño de la base de datos es igual o mayor que 2048 MB (2 GB), la alerta será desencadenada.

   

5. El diálogo Check interval nos permite establecer la fecha/tiempo cuando la alerta iniciará con la ejecución del script, así como el periodo de recurrencia. El periodo de recurrencia puede ser establecido por el usuario ingresando el valor de recurrencia y seleccionando la medida de tiempo desde el menú desplegable.

   

6. Cuando todo esté configurado, el diálogo Alert summary aparecerá al presionar el botón Next. Esto permite una revisión final de los parámetros personalizados de la alerta. Si todo está correcto, presione el botón OK y la nueva alerta personalizada será creada.

   

Una vez que la alerta es desencadenada, aparte del correo electrónico, todas las alertas activadas pueden ser revisadas en la pestaña History del diálogo Alerts.

Desde la pestaña Manage, cada alerta creada puede ser editada, eliminada, deshabilitada o habilitada.

Cada método nativo de auditoría de SQL Server tiene algunas limitaciones significativas y/o desventajas, mientras que al mismo tiempo cada una requiere algo de conocimiento avanzado de SQL para establecer apropiadamente el sistema de alertas. ApexSQL Audit, por otro lado, permite configurar alertas usando las opciones estándar o avanzadas en unos cuantos clics, mientras que asegura unos procesos efficient auditing and reporting para múltiples instancias de SQL Server, bases de datos y sis objetos al mismo tiempo.

Apéndice

• DATABASE_ROLE_MEMBER_CHANGE_GROUP – El evento es registrado cuando un inicio de sesión es añadido/removido de un rol de base de datos.
• SERVER_ROLE_MEMBER_CHANGE_GROUP – El evento es registrado cuando un inicio de sesión es añadido/removido de un rol fijo de servidor.
• DATABASE_PERMISSION_CHANGE_GROUP – El evento es registrado para los eventos GRANT, REVOKE o DENY.
• SERVER_OBJECT_PERMISSION_CHANGE_GROUP – El evento es registrado cuando GRANT, REVOKE o DENY son enviados por un permiso de objeto de servidor.
• SERVER_PERMISSION_CHANGE_GROUP – El evento es registrado cuando GRANT, REVOKE o DENY son enviados para permisos en el alcance del servidor, como crear un inicio de sesión.
• DATABASE_PRINCIPAL_CHANGE_GROUP – El evento es registrado cuando usuarios son creados, alterados o eliminado de la base de datos.
• SERVER_PRINCIPAL_CHANGE_GROUP – El evento es registrado cuando principales del servidor son creados, alterados o eliminados.

Traductor: Daniel Calbimonte