Habilitar Servidor de Auditoria en SQL Server

La Auditoría en SQL Server registra eventos y acciones que ocurren en el servidor y las bases de datos. Estos eventos pueden incluir operaciones como inicio de sesión, ejecución de consultas, cambios en configuraciones, y más. Los registros de auditoría se almacenan en archivos de auditoría o en el registro de eventos de Windows y se pueden revisar para detectar anomalías o cumplir con requisitos de cumplimiento.

Componentes de la Auditoría

1. Servidor de Auditoría (Server Audit):
   • Define la configuración general de la auditoría, como el destino del registro (archivo, registro de eventos de Windows, etc.).
   • Se crea a nivel del servidor.
2. Especificaciones de Auditoría de Servidor (Server Audit Specifications):
   • Especifican los eventos que se desean auditar a nivel del servidor.
   • Se asocian a un servidor de auditoría.
3. Especificaciones de Auditoría de Base de Datos (Database Audit Specifications):
   • Especifican los eventos que se desean auditar a nivel de la base de datos.
   • También se asocian a un servidor de auditoría.

Configuración de Auditoría

Creación de un Servidor de Auditoría

-- Crear un servidor de auditoría que escriba en un archivo
CREATE SERVER AUDIT MiServidorAudit
TO FILE (FILEPATH = 'C:\Audits\', MAXSIZE = 100 MB, MAX_ROLLOVER_FILES = 5, RESERVE_DISK_SPACE = OFF);
GO

-- Habilitar el servidor de auditoría
ALTER SERVER AUDIT MiServidorAudit WITH (STATE = ON);
GO

Creación de Especificaciones de Auditoría

Servidor de Auditoría

-- Crear especificación de auditoría para el servidor
CREATE SERVER AUDIT SPECIFICATION MiServidorAuditSpec
FOR SERVER AUDIT MiServidorAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP);
GO

-- Habilitar la especificación de auditoría del servidor
ALTER SERVER AUDIT SPECIFICATION MiServidorAuditSpec WITH (STATE = ON);
GO

Base de Datos de Auditoría

-- Crear especificación de auditoría para una base de datos
USE MiBaseDeDatos;
GO

CREATE DATABASE AUDIT SPECIFICATION MiDatabaseAuditSpec
FOR SERVER AUDIT MiServidorAudit
ADD (SELECT ON SCHEMA::dbo BY public);
GO

-- Habilitar la especificación de auditoría de la base de datos
ALTER DATABASE AUDIT SPECIFICATION MiDatabaseAuditSpec WITH (STATE = ON);
GO

Consideraciones Importantes

1. Cumplimiento y Seguridad:
   • La auditoría es esencial para cumplir con normativas como HIPAA, SOX, y GDPR.
   • Proporciona una trazabilidad detallada de las acciones y cambios en el sistema.
2. Rendimiento:
   • La auditoría puede impactar el rendimiento, especialmente si se auditan muchos eventos o si los eventos auditados son de alta frecuencia.
   • Es recomendable auditar solo lo necesario para minimizar este impacto.
3. Revisión de Auditorías:
   • Los registros de auditoría deben revisarse regularmente para detectar actividades sospechosas.
   • Se pueden utilizar herramientas de análisis o scripts personalizados para revisar y reportar estos eventos.

Ejemplo de Revisión de Auditoría

-- Consultar registros de auditoría desde un archivo de auditoría
SELECT *
FROM sys.fn_get_audit_file('C:\Audits\MiServidorAudit_*.sqlaudit', DEFAULT, DEFAULT);

La herramienta de Auditoría de SQL Server es una funcionalidad poderosa para asegurar la integridad y seguridad de los datos, proporcionando una manera estructurada de monitorear y registrar eventos críticos dentro del servidor y las bases de datos. Abajo tiene un script que puede pegar en el sql management studio para repasar.

–Crear un servidor de auditoria que reporte –en el evet viewer de windows –Crear una base de datos Create database Cosa go –Crear una tabla dentro de la base de datos use cosa go Create table EmployeeReview (Employeeid int not null, reviewdate datetime default getdate() not null, comments varchar(2000) not null ) –Crear el servidor de auditoria use master go Create server audit Test_server to application_log with(queue_delay = 1000, on_failure = continue) go –Crear la base de datos de auditoria para el objeto –EmployeeReview base de datos Cosa Use Cosa Create database audit specification Test_Database_Auditoria for server audit Test_server add ( insert on dbo.EmployeeReview by dbo) with (state=on) –habilito el servidor use master go alter server audit Test_server with (state=on) –insertamos datos a la tabla y vamos al visor de sucesos –a revisar que se registre la inserción insert into EmployeeReview (Employeeid,reviewdate ,comments ) values (1,’05-09-2012′,‘Comentario no encriptado’)