Creación de auditorias en instancias de SQL Server

A continuación vamos a desarrollar un caso práctico, donde se creará una auditoría de servidor, para almacenar en un fichero todas las conexiones con éxito a una instancia de SQL Server.

Creamos la auditoría desde el desplegable de seguridad del Servidor:

Crear auditoría con SQL Server

Configuramos el target de la auditoría:

Configuramos el target de la auditoría

Audit name: Se da nombre a la auditoría.

Queue delay: En términos generales, la configuración predeterminada (1.000 milisegundos) es lo suficientemente bueno, y no se debería cambiar. No conozco caso alguno donde modificando este valor se consiga un beneficio claro. En algunos entornos no se establece retardo alguno, y se configura a 0 milisegundos. ¡Ojo con esta configuración! La auditoría registra los datos de forma síncrona, pudiendo crear un gran perjuicio al rendimiento de la instancia SQL Server. Solo algunos escenarios lo contemplan, aunque los recursos que lo atienden son muy elevados.

Shut down server on audit log failure: Esta opción se recomienda no marcarla, ya que un fallo en la auditoría apagaría el servidor. Solo en entornos críticos, donde la auditoría tiene un papel crucial, puede hacer necesario que se habilite.

Audit destination: En este apartado se configura la forma de almacenar los valores recogidos de la auditoría.

  • File: Se almacena el registro en un fichero plano. Generalmente esta es la opción preferida.
  • Security Log: Se almacena el registro en el log de seguridad del visor de eventos de Windows.
  • Application Log: Se almacena el registro en el log de aplicación del visor de eventos de Windows.

File path: Ruta del fichero que almacena los registros de la auditoría.

Maximun rollover files: Por defecto está configurado con “2147483647”, es decir, puede crear tantos ficheros como necesite. Esto puede derivar en un problema de espacio en el almacén de registros. Se recomienda realizar un cálculo previo, junto con la siguiente propiedad “Maximun file size”. En este caso, de inicio albergará 20 ficheros con un tamaño de 200 megas, es decir que el tamaño total será de 4 gigas aproximadamente.

Maximun file size: Esta propiedad indica el tamaño de los ficheros que van a almacenar los registros de la auditoría. Lo interesante es establecer un histórico de registros.

Cuando los registros de la auditoría han completado los 20 ficheros, el sistema reutiliza los ficheros, respetando así la configuración establecida. Lógicamente, hay que preparar un método que permita consultar la información registrada.

Una vez habilitada la auditoría es necesario realizar un seguimiento. En poco tiempo, se puede realizar un cálculo del número de ficheros y el tamaño de cada uno de ellos, para que recoja al menos los registros de un día completo. A continuación se puede crear una tarea que realice la salva de la carpeta donde se encuentran los ficheros. De esta forma, se mantendría un histórico.

Reserve disk space: Esta opción es recomendable marcarla, ya que de esta forma, el disco siempre tendrá disponible el espacio necesario para mantener viva la auditoría.

Con Transact-SQL:

CREATE SERVER AUDIT [Audit-Conexiones Correctas]
TO FILE (FILEPATH = ‘G:\Auditoria\’, MAXSIZE = 200MB, MAX_ROLLOVER_FILES = 20,RESERVE_DISK_SPACE = ON)
WITH (QUEUE_DELAY = 1000)

Se habilita la auditoría.

Se habilita la auditoría

Botón derecho sobre la auditoría, y seleccionar Enable Audit.

Con Transact-SQL:

ALTER SERVER AUDIT [Audit-Conexiones Correctas]
WITH (STATE = ON)
GO

Creación de especificaciones de auditoría. Desde aquí se detallan los eventos que se quieren auditar. En este caso, las conexiones exitosas a la instancia SQL Server:

Creación de especificaciones de auditoría

Se da nombre a la especificación, se asocia al target de auditoría previamente configurado y se selecciona el evento que se quiere registrar:

Se da nombre a la especificación

Con Transact-SQL:

CREATE SERVER AUDIT SPECIFICATION [ConexionesCorrectasAlServidor]
FOR SERVER AUDIT [Audit-Conexiones Correctas]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = OFF)

Se habilitan los detalles de la auditoría, y por tanto se pone en marcha su registro.

Pulsando botón derecho sobre el nuevo Server Audit Specification, se selecciona la opción Enable Server Audit Specification:

Habilitar especificacion

Con Transact-SQL:

ALTER SERVER AUDIT SPECIFICATION [ConexionesCorrectasAlServidor]
WITH (STATE = ON)
GO

Comprobamos que en disco ya está creado el primer fichero:

comprobación en disco

Comprobamos que ya se registran en un log destinado a cada auditoría.

Ver log de auditoría SQL Server

Si queremos consultar los datos registrados.

  • De un fichero en concreto:

    SELECT * FROM fn_get_audit_file(
    ‘g:\Auditoria\Audit-Conexiones%5Correctas_C8A01FA0-D55A-4E23-A102-38FF6F14A1D8_0_130385800911850000.sqlaudit’,
    default, default)

  • De todos los ficheros generados:

    SELECT * FROM fn_get_audit_file(‘g:\Auditoria\*’, default, default)

admin

View more posts from this author
One thought on “Creación de auditorias en instancias de SQL Server

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *