Cómo habilitar el registro de auditoría de SQL Server y revisar el registro de auditoría

Auditar Microsoft SQL Server es fundamental para identificar problemas de seguridad e incidentes. Además, auditar SQL Server es un requisito para el cumplimiento de regulaciones como PCI DSS y HIPAA.

El primer paso es definir qué auditar. Por ejemplo, podrías auditar los inicios de sesión de usuarios, la configuración del servidor, los cambios de esquema y las modificaciones de los datos de auditoría. A continuación, debes elegir qué funciones de auditoría de seguridad utilizar. Las funciones útiles incluyen las siguientes:

• Auditoría C2
• Criterios Comunes de Cumplimiento
• Auditoría de inicio de sesión
• Auditoría de SQL Servering
• SQL Trace
• Eventos Extendidos
• Captura de Cambios de Datos
• Disparadores DML, DDL y de inicio de sesión

Este artículo es para administradores de bases de datos (DBAs) que están considerando el uso de la auditoría C2, Criterios Comunes de Cumplimiento y Auditoría de SQL Server. No vamos a examinar ninguna herramienta de auditoría de terceros, aunque pueden ser de gran ayuda, especialmente para entornos más grandes y en industrias reguladas.

Habilitando la Auditoría C2 y el Cumplimiento de Criterios Comunes

Si actualmente no estás auditando tu SQL Server, el lugar más fácil para comenzar es habilitando la auditoría C2. La auditoría C2 es un estándar aceptado internacionalmente que se puede activar en SQL Server. Audita eventos como inicios de sesión de usuarios, procedimientos almacenados y la creación y eliminación de objetos. Pero es todo o nada — no puedes elegir lo que audita, y puede generar una gran cantidad de datos. Además, la auditoría C2 está en modo de mantenimiento, por lo que probablemente se eliminará en una versión futura de SQL Server.

La conformidad con Common Criteria es un estándar más reciente que reemplaza la auditoría C2. Fue desarrollado por la Unión Europea y se puede habilitar en las ediciones Enterprise y Datacenter de SQL Server 2008 R2 en adelante. Pero puede causar problemas de rendimiento si su servidor no tiene las especificaciones suficientes para manejar la sobrecarga adicional.

Aquí se explica cómo habilitar la auditoría C2 en SQL Server 2017:

1. Abra el SQL Server Management Studio.

2. Conéctese al motor de base de datos para el cual desea habilitar la auditoría C2. En el cuadro de diálogo Conectar al servidor, asegúrese de que Server type esté configurado en Database Engine y luego haga clic en Connect.

3. En el panel Explorador de objetos a la izquierda, haga clic derecho en su instancia de SQL Server en la parte superior y seleccione Properties del menú.

4. En la ventana de Propiedades del Servidor, haga clic en Security bajo Select a page.

5. En la página de Seguridad, puedes configurar el monitoreo de inicio de sesión. Por defecto, solo se registran los inicios de sesión fallidos. Alternativamente, puedes auditar solo los inicios de sesión exitosos, o ambos inicios de sesión fallidos y exitosos.

Figura 1. Configuración de la auditoría de acceso

6. Marque Habilitar seguimiento de auditoría C2 en la sección Opciones.

7. Si desea habilitar la auditoría de cumplimiento de Criterios Comunes C2, marque Enable Common Criteria compliance.

La conformidad con los Criterios Comunes (CC) es un estándar flexible que se puede implementar con diferentes Niveles de Garantía de Evaluación (EALs), del 1 al 7. Los EALs más altos tienen un proceso de verificación más exigente. Cuando activas la opción Enable Common Criteria compliance en SQL Server, estás habilitando la conformidad con CC EAL1. Es posible configurar SQL Server manualmente para EAL4+.

Habilitar los cambios de CC Compliance modifica el comportamiento de SQL Server. Por ejemplo, los permisos DENY a nivel de tabla tendrán prioridad sobre los GRANTs a nivel de columna, y se auditarán tanto los inicios de sesión exitosos como los fallidos. Además, se activa la Protección de Información Residual (RIP), que sobrescribe las asignaciones de memoria con un patrón de bits antes de que sean utilizadas por un nuevo recurso.

8. Haga clic en OK.

9. Basado en las opciones seleccionadas, es posible que se le solicite reiniciar SQL Server. Si recibe este mensaje, haga clic en OK en el cuadro de diálogo de advertencia. Si habilitó el Cumplimiento de Criterios Comunes C2, reinicie el servidor. De lo contrario, haga clic derecho en su instancia de SQL Server en el Explorador de Objetos nuevamente y seleccione Restart en el menú. En el cuadro de diálogo de advertencia, haga clic en Yes para confirmar que desea reiniciar SQL Server.

Habilitando SQL Server Audit

La auditoría de SQL Server se puede habilitar en lugar de la auditoría C2; también puede optar por habilitar ambas. Los objetos de auditoría de SQL Server se pueden configurar para recopilar eventos a nivel del servidor o a nivel de la base de datos de SQL Server.

Crear objeto de auditoría de servidor

Creemos un objeto de auditoría a nivel de servidor para SQL Server:

1. En el panel Explorador de Objetos a la izquierda, expanda Security.

2. Haga clic derecho en Audits y seleccione New Audit… del menú. Esto creará un nuevo objeto de SQL Server Audit para la auditoría a nivel de servidor.

3. En la ventana Crear Auditoría, asigne un nombre a la configuración de la auditoría en el campo Audit name

4. Especifique qué debe suceder si falla la auditoría de SQL Server utilizando la opción On Audit Log Failure. Puede elegir Continue o decidir apagar el servidor o detener las operaciones de la base de datos que están siendo auditadas. Si selecciona Fail operation, las operaciones de la base de datos que no están auditadas continuarán funcionando.

Figura 2. Creación de un objeto de auditoría de SQL Server a nivel de servidor

5. En el menú desplegable de Audit destination, puede elegir escribir el rastro de auditoría SQL en un archivo o auditar eventos en el registro de seguridad de Windows o en el registro de eventos de la aplicación. Si elige un archivo, debe especificar una ruta para el archivo.

Tenga en cuenta que si desea escribir en el registro de eventos de seguridad de Windows, se deberá otorgar permiso a SQL Server. Para simplificar, seleccione el registro de eventos de la aplicación. Además, puede incluir un filtro como parte del objeto de auditoría para proporcionar un conjunto de resultados más específico; los filtros deben estar escritos en Transact-SQL (T-SQL).

6. Haga clic en OK.

7. Ahora encontrará la nueva configuración de auditoría en el Explorador de Objetos debajo de Audits. Haga clic derecho en la nueva configuración de auditoría y seleccione Enable Audit del menú.

8. Haga clic en Close en el diálogo de habilitación de auditoría.

Crear objeto de auditoría de base de datos

Para crear un objeto de auditoría de SQL Server para la auditoría a nivel de base de datos, el proceso es un poco diferente y necesita crear al menos un objeto de auditoría a nivel de servidor primero.

1. Expanda Databases en el Explorador de objetos y expanda la base de datos en la que desea configurar la auditoría.

2. Expanda la carpeta Security, haga clic derecho en Database Audit Specifications y seleccione New Database Audit Specification… del menú.

Figura 3. Creación de una especificación de auditoría de servidor para la auditoría a nivel de base de datos

3. En la ventana de Propiedades bajo Acciones, utilice los menús desplegables para configurar uno o más tipos de acción de auditoría, seleccionando las declaraciones que desea auditar (como DELETE o INSERT), la clase de objeto en la que se realiza la acción, y así sucesivamente.

4. Cuando haya terminado, haga clic en OK y luego habilite el objeto de auditoría haciendo clic derecho sobre él y seleccionando Enable Database Audit Specification.

Visualización de registros de auditoría de SQL Server

Los registros de auditoría de C2 Audit SQL Server se almacenan en el directorio de datos predeterminado de la instancia de SQL Server. Cada archivo de registro puede tener un máximo de 200 megabytes. Se crea automáticamente un nuevo archivo cuando se alcanza el límite.

Una solución nativa que se recomienda para ver los registros de auditoría de SQL Server se llama Log File Viewer. Para usarla, siga los siguientes pasos:

1. En SQL Server Management Studio, en el panel de Explorador de Objetos, expanda Security y

2. Haga clic derecho en el objeto de auditoría que desea ver y seleccione View Audit Logs del menú.

3. En el Visor de Archivos de Registro, los registros se mostrarán en el lado derecho. Independientemente de si los registros se escriben en un archivo o en el Registro de Eventos de Windows, el Visor de Archivos de Registro mostrará los registros.

4. En la parte superior del Visor de archivos de registro, puede hacer clic en Filter para personalizar qué entradas de registro se muestran. Los archivos de registro de SQL Server se guardan en formato .sqlaudit y no son legibles, por lo que Log File Explorer le permite hacer clic en Export para guardar los registros en un formato de archivo delimitado por comas .log.

Figura 4. Revisión del registro de auditoría de SQL Server en el Visor de archivos de registro

FAQ

Cómo verificar si la auditoría de SQL Server está habilitada?

Para verificar si la auditoría de SQL Server está habilitada, consulte la vista de gestión dinámica sys.dm_server_audit_status o revise la carpeta de Seguridad en SQL Server Management Studio (SSMS). En SSMS, expanda Seguridad > Auditorías para ver todas las auditorías configuradas y su estado actual: las auditorías habilitadas mostrarán un icono verde, mientras que las deshabilitadas aparecerán con un icono rojo. También puede ejecutar esta consulta para verificar el estado de la auditoría de manera programática:

      SELECT name, is_state_enabled FROM sys.server_audits
      

Recuerde que tanto la auditoría del servidor como la especificación de auditoría de la base de datos deben estar habilitadas para una cobertura de auditoría completa. Data Security que comienza con la identidad requiere una visibilidad completa de quién accede a qué datos, y la auditoría de SQL Server proporciona esa base cuando está correctamente configurada y verificada.

¿Por qué el archivo de auditoría de mi SQL Server está creciendo tanto?

El crecimiento excesivo del archivo de auditoría suele ocurrir cuando se auditan demasiados eventos o no se han configurado adecuadamente los ajustes de gestión de archivos. Los culpables más comunes son habilitar TODOS los grupos de acciones de auditoría, auditar sentencias SELECT en tablas de alto tráfico o establecer un crecimiento ilimitado de archivos sin rotación. Para controlar el crecimiento, concéntrate en auditar solo los eventos que realmente necesitas para el cumplimiento, típicamente LOGIN_CHANGE_PASSWORD_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, y operaciones DML específicas en tablas sensibles. Configura límites máximos de tamaño de archivo y habilita la rotación de archivos con las opciones MAXSIZE y MAX_ROLLOVER_FILES. Para entornos de alto volumen, considera usar el destino APPLICATION_LOG en lugar del destino FILE, o implementa filtrado de auditoría con cláusulas WHERE para reducir la captura de eventos innecesarios. Auditar inteligentemente significa rastrear lo que importa sin ahogarse en el ruido de los datos.

¿Cómo solucionar problemas cuando el auditoría de SQL Server no se inicia?

Cuando la auditoría de SQL Server falla al iniciar, el problema suele estar relacionado con permisos de archivos, accesibilidad de rutas o conflictos de configuración. Primero, verifique que la cuenta de servicio de SQL Server tenga permisos de escritura en el directorio del archivo de auditoría – esta es la causa más común de fallos de inicio. Revise el registro de errores de SQL Server para mensajes de error específicos, que típicamente proporcionan una guía clara sobre el problema. Asegúrese de que el directorio objetivo exista y sea accesible desde la instancia de SQL Server, especialmente en entornos agrupados donde las rutas de almacenamiento compartido deben ser válidas en todos los nodos. Si utiliza el Registro de Aplicaciones de Windows como destino, verifique que la cuenta de servicio tenga los permisos adecuados de escritura en el registro de eventos. Errores de configuración como nombres de auditoría duplicados o rutas de archivo inválidas también impedirán el inicio. La clave es la solución de problemas metódica: verifique primero los permisos, luego las rutas y finalmente la sintaxis de configuración. Netwrix simplifica esta complejidad al proporcionar una gestión de auditoría centralizada que elimina estas trampas comunes.

¿Cuál es el impacto en el rendimiento de la auditoría de SQL Server?

La auditoría de SQL Server tiene un impacto mínimo en el rendimiento cuando se configura correctamente, típicamente añadiendo un sobrecoste del 2-5% en la mayoría de los entornos. El impacto real depende de tres factores clave: qué eventos auditas, con qué frecuencia ocurren y el rendimiento del subsistema de almacenamiento. Auditar operaciones de alta frecuencia como las sentencias SELECT en sistemas OLTP con mucha actividad generará más sobrecarga que centrarse en eventos relevantes para la seguridad como inicios de sesión, cambios de permisos y operaciones DML en tablas sensibles. Los destinos de auditoría asíncronos (la opción predeterminada) ofrecen un mejor rendimiento que las opciones síncronas, pero con un registro de eventos ligeramente retrasado. Para minimizar el impacto, utiliza filtros de auditoría con cláusulas WHERE, evita auditar operaciones innecesarias del sistema y asegúrate de que tu almacenamiento de archivos de auditoría tenga capacidad de E/S adecuada. Extended Events generalmente tienen menos sobrecarga que SQL Server Audit para escenarios de alto volumen, pero SQL Server Audit proporciona características de cumplimiento superiores y una gestión más fácil. Un diseño de auditoría inteligente se centra en el valor de seguridad sobre el registro exhaustivo: quieres visibilidad que proteja sin paralizar el rendimiento.

Auditoría de SQL Server vs SQL Trace: ¿cuál debería usar?

SQL Server Audit es la opción moderna para nuevas implementaciones, mientras que SQL Trace está obsoleto y debe evitarse en proyectos nuevos. SQL Server Audit ofrece mejor seguridad, rendimiento y capacidades de gestión en comparación con la funcionalidad heredada de SQL Trace. A diferencia de SQL Trace, los eventos de SQL Server Audit no pueden ser modificados o eliminados por los usuarios (incluidos los administradores del sistema), asegurando la integridad de la auditoría para los requisitos de cumplimiento. El marco de auditoría ofrece procesamiento asincrónico para un mejor rendimiento, capacidades de filtrado integradas e integración con el Registro de Eventos de Seguridad de Windows. SQL Trace requiere codificación manual con procedimientos almacenados y ha sido marcado para eliminación en versiones futuras de SQL Server. Extended Events es el reemplazo recomendado para las capacidades de diagnóstico de SQL Trace, mientras que SQL Server Audit maneja la seguridad y el monitoreo del cumplimiento. Si actualmente estás utilizando SQL Trace para auditorías de seguridad, migra a SQL Server Audit inmediatamente – proporciona el registro de auditoría a prueba de manipulaciones que la verdadera seguridad de datos exige. Las soluciones de Netwrix se basan en estas capacidades de auditoría nativas para proporcionar visibilidad centralizada en todo tu entorno de datos.