Auditoría de Seguridad en SQL Server 2008


Esta semana mientras trabajaba en Bolivia escuché al personal de seguridad de un cliente discutir sobre las opciones que podían utilizar para auditar las transacciones realizadas a las tablas de una base de datos. Los interrumpí y les pregunté si habían escuchado a cerca de la configuración de auditorías en SQL Server 2008 y me dijeron que no. Así que aproveché el tema para escribir el siguiente artículo.

La primera pregunta que nos surge es: ¿Porque auditamos?

Revisando la literatura de auditoría de seguridad, los autores coinciden en que el tener políticas de seguridad es un factor crítico para mantener los datos seguros. La auditoría también nos ayuda a identificar lo que accedió un intruso en el evento de que un ataque sea exitoso.

En SQL 2005 el enfoque estaba orientado a asegurarse de que el usuario no tuviera privilegio mayores a los que necesitaba y que los cambios fueran realizados por el personal autorizado. La herramienta que se promovía para auditar los cambios a la base de datos era el SQL Profiler ya que podía auditar cambios al esquema, operaciones de insertar, actualizar o eliminar registros; y eventos relacionados con cambios de permisos o la creación de nuevos Logins.

Una de las mejoras que trajo SQL Server 2008 Enterprise es un incremento en la capacidad de auditoría a través del uso del SQL Server Audit. A través de esta funcionalidad se puede rastrear y registrar de forma automática los eventos que ocurren a nivel del servidor o a nivel de la base de datos. Esto es posible a través del uso del objeto Audit. Veamos entonces como crear una auditoría, como crear y habilitar una especificación de auditoría a nivel de Servidor o a nivel de base de datos y como visualizar los registros de auditoría.

Crear una Auditoría

Un objeto de auditoría es una colección de una o más acciones individuales o un grupo de acciones que podrán ser rastreadas. Por ejemplo, se puede configurar un objeto de auditoría para identificar todos los logins fallidos. Los eventos se escriben en la localización que se especifique. Se pueden almacenar en un archivo, la bitácora de eventos de aplicaciones o la bitácora de eventos de seguridad.

El objeto de auditoría se puede crear a través del Management Studio (SSMS) o utilizando T-SQL. Desde SSMS se debe presionar el botón de la derecha del mouse sobre la opción New Audit localizada en la carpeta de auditoría bajo el árbol de Seguridad, como se muestra a continuación:

En la pantalla Create Audit se debe ingresar el nombre del objeto de auditoría, y se debe especificar el destino. Si se indica que el destino será un archivo, hay que especificar la ruta donde será almacenado. Finalmente se debe presionar el botón OK para crear el objeto de auditoría.

Para propósitos de este ejemplo cree un segundo objeto de auditoría e ingrese el nombre “AuditarConsultasTablaEmpleado”. Seleccione como destino un archivo e indique la ruta donde desee almacenarlo. Ambos objetos estarán localizados bajo la carpeta Audits, como se muestra a continuación. Este objeto de auditoría se utilizará para rastrear las transacciones SELECT realizadas contra la tabla HumanResources.Employee de la base de datos AdventureWorks2008R2.

Si desea crear un objeto de auditoría a través de T-SQL lo puede hacer utilizando el comando CREATE SERVER AUDIT. La siguiente consulta crea el objeto  “AuditarConsultasTablaEmpleado”. Esta operación fue realizada anteriormente a través de SSMS.

USE master

CREATE SERVER AUDIT [AuditarConsultasTablaEmpleado]

TO FILE  (FILEPATH = N'C:\TEMP');

Crear y habilitar una especificación de auditoría a nivel de Servidor

Una vez se ha creado los objetos de auditoría, el siguiente paso es crear las especificaciones apropiadas de auditoría. Las especificaciones de auditoría le indican al objeto de auditoría lo que debe rastrear. En el caso del objeto de auditoría llamado “AuditarLoginsFallidos”, debemos crear una especificación que busque los logins que no son exitosos. Para lograrlo debemos seleccionar el botón de la derecha del mouse sobre la carpeta “Server Audit Specifications” ubicada bajo el árbol de Seguridad.

Asigne a la especificación de auditoría el nombre: “EspecificacionAuditoriaServidor-LoginsFallidos”. Bajo audit seleccione la opción “AuditarLoginsFallidos”. Esto asignará la especificación de auditoría “EspecificacionAuditoriaServidor-LoginsFallidos” al objeto de auditoría “AuditarLoginsFallidos”. Seleccione el tipo de acción para auditar: “FAILED_LOGIN_GROUP” y presione OK para crear y asignar el objeto de auditoría.

Presione el botón de la derecha del mouse sobre “EspecificacionAuditoriaServidor-LoginsFallidos” para habilitar la especificación a través de la opción: “Enable Server Audit Specification”

Finalmente debe habilitar el objeto de auditoría presionando el botón de la derecha del mouse sobre “AuditarLoginsFallidos” para habilitar la auditoria a través de la opción Enable Audit como se muestra en la siguiente figura.

Si desea crear una especificación de auditoría a nivel de servidor, a través de T-SQL, lo puede hacer utilizando el comando CREATE SERVER AUDIT SPECITICATION. La siguiente consulta crea la especificación de auditoría  “EspecificacionAuditoriaServidor-LoginsFallidos”. Esta operación fue realizada anteriormente a través de SSMS.

USE master

CREATE SERVER AUDIT SPECIFICATION [EspecificacionAuditoriaServidor-LoginsFallidos]

FOR SERVER AUDIT [AuditarLoginsFallidos]

ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON)

GO

Crear y habilitar una especificación de auditoría a nivel de base de datos

Para crear y habilitar una especificación de auditoría a nivel de base de datos debe expandir la base de datos, en este ejemplo utilice AdventureWorks2008R2, y seleccione con el botón de la derecha del mouse la opción  “Audit Specifications” bajo el árbol de seguridad de la base de datos. Seleccione la opción “New Database Audit Specification” y asigne un nombre (para propósito de este ejemplo asignaremos el nombre: “EspecificacionAuditoriaDB-ConsultasTablaEmpleado”)

Como se muestra en la siguiente figura, seleccione la opción “AuditarConsultasTablaEmpleado” Bajo audit. Esto asignará la especificación de auditoría “EspecificacionAuditoriaDB-ConsultasTablaEmpleado” al objeto de auditoría “AuditarConsultasTablaEmpleado”.

Nota: La clase de objeto se utiliza para indicar lo que se quiere auditar. Las opciones son: Objeto, Base de datos o Esquema. Seleccione objeto para auditar Tablas, Funciones, Procedimientos Almacenados o Vistas. El nombre del Principal son entidades que pueden solicitar recursos de SQL Server.

Para propósitos de este ejemplo, seleccione “SELECT” en el tipo de acción para auditar; en la clase del objeto seleccione “OBJECT”; en el nombre del objeto ingrese [HumanResources].[Employee]; en el nombre del Principal ingrese [public] y presione OK para crear y asignar el objeto de auditoría.

Esto permite rastrear las consultas con SELECT que realizan todos los usuarios a la tabla [HumanResources].[Employee].

Presione el botón de la derecha del mouse sobre “EspecificacionAuditoriaDB-ConsultasTablaEmpleado” para habilitar la especificación a través de la opción: “Enable Database Audit Specification”

Finalmente debe habilitar el objeto de auditoría presionando el botón de la derecha del mouse sobre “AuditarConsultasTablaEmpleado” para habilitar la auditoria a través de la opción Enable Audit como se muestra en la siguiente figura.

Si desea crear una especificación de auditoría a nivel de base de datos, a través de T-SQL, lo puede hacer utilizando el comando CREATE DATABASE AUDIT SPECITICATION. La siguiente consulta crea la especificación de auditoría  “EspecificacionAuditoriaDB-ConsultasTablaEmpleado”. Esta operación fue realizada anteriormente a través de SSMS.

USE master

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec-EmployeesTable]

FOR SERVER AUDIT [Audit-EmployeeQueries]

ADD (SELECT ON OBJECT::[HumanResources].[Employee] BY [public])

WITH (STATE = ON)

GO

Visualizar los registros de auditoría

Los registros de auditoría pueden ser accedidos a través la opción “View Audit Logs” o a través de la bitácora de eventos de aplicaciones o de seguridad, dependiendo de dónde se especificó que se almacenaría la auditoría.

Los resultados pueden ser filtrados o inclusive hasta exportados en los siguientes formatos: log, csv y txt

Conclusión

La auditoría en SQL 2008 Enterprise es muy poderosa y flexible ya que permite crear auditorías a nivel de servidor o a nivel de base de datos. La configuración es sencilla ya que solo se requiere especificar donde se almacenará la auditoría (en un archivo, la bitácora de eventos de aplicaciones o la bitácora de eventos de seguridad), que objeto se desea auditar (Base de datos, Esquema, Tablas, Funciones, Procedimientos Almacenados o Vistas) y para cual Principal  (entidades que pueden solicitar recursos de SQL Server).

Comments (34)

  1. Buen Dia Luciano, si podras ver Usuario y Workstation, entre otros. Saludos!

  2. Todas las ediciones de SQL Server admiten auditorías en el nivel de servidor.  Las auditorías de nivel de base de datos se limitan a las ediciones Enterprise, Developer y Evaluation.

  3. Hola Luciano, el Login y Usuario lo puedes ver en el Audit Log (http://msdn.microsoft.com/en-us/library/cc280545.aspx), la Máquina o IP si dependera de que estes auditando, por ejemplo
    si auditas Logins Fallidos en el Statement podras ver la IP de la maquina que se intent hacer la session fallida.
    Saludos!

  4. Ya que SQL Audit está basado en Extended Events usa muy pocos recursos mientras hace la recolección de datos, por tanto no produce un overhead considerable. Sin embargo la cantidad de overhead se verá ligado a: Que tipo eventos estas auditando, ocurren con una frecuencia muy elevada, es adecuado auditar solo lo necesario. Tambien donde estas almacenando el Log de Eventos, Lo más adecuado sería en un disco independiente, nunca en uno donde existan archivos de bases de datos de SQL.

  5. Hola Santiago, Si puedes hacerlo, solo debes seleccionar en el OBJECT CLASS = DATABASE y luego seleccionas las base de datos deseada. Pero ten encuenta que cada click que hagas en SSMS es un SELECT a las tablas del systema, deberas encontrar un Buena manera
    de filtrar tus resultados. Saludos!

  6. Hola Chica10, Para hacer seguimientos a cambios DML tu unica opcion es auditor ya sea usando SQL Audit (La major opcione) o un SQL Trace o C2 Compliant Audit. Sin embargo cambios DDL se guardaran en el Default Trace, pero este trace se sobre escribe con
    el tiempo. Saludos!

  7. Hola Victor, voy a necesitar un poco mas de informacion de como esta configurada tu aduditoria. Saludos!

  8. Buen Dia Mem, asegurate que la Edicion de SQL Server sea Enterprise, Standart Edition no soporta Auditoria en SQL 2008. Saludos!

  9. En el blog se hace referencia a Auditoria a nivel de Servidor (Ejm: Logins y Logouts, cambios de roles o permisologia a nivel de la instancia) y tambien a Auditoria a nivel de la Base de Datos (ejm: Auditar el Acceso a una table o eliminar algun procedimiento).

    Tambien es posible hacer auditoria usando triggers, pero lo mas adecuado es crear un objeto de Auditoria a Nivel de base de datos, ya que te dara mayor flexibilidad y major rendimiento.

  10. Muchas Gracias! Asi será 🙂

  11. veronica luna says:

    Hola, buen dia.

    Solamente me gustaria manifestar que este post esta buenisimo, es my facil de enteder y poner en practica, ademas de ser un tema que casi no se encuentra el la red.

    Saludos, espero puedas seguir escribiendo post tan buenos como este.

  12. Mad_season says:

    No afecta la performance del servicio ??

  13. epic says:

    estimados, la auditoria de SQL Server 2008 bajo que versión se puede utilizar ???

  14. epic says:

    La que señalas en tu blog es a nivel de BD cierto ??? como seria una a nivel de Servidor o a que te refieres con "a nivel de servidor??

    Estuve viendo y se podía auditar tablas pero con Trigger, hay algo mas fácil ???

    Saludos.

  15. JAM4 says:

    Hola SQL PFE LATAM buenas tardes, mira seguí tu post y realice una prueba ejecutando un SP que actualiza un campo de stado, pero no veo los valores solo veo la instrucción UPDATE y las variables. Te adjunto lo que te comento:

    Database Name dbAdministracion

    Schema Name dbo

    Object Name Cliente

    Statement UPDATE dbo.Cliente

    SET estadoactivo = @estadoactivo

    WHERE Cliente_id = @Cliente_id

  16. epic says:

    Hola SQL PFE LATAM, y para auditar los accesos a tablas o cambios como se hace ?? mediante trigger ?? tengo la versión STD.

    Saludos.

  17. Hola Jam,

    Esto es una configuracion de diseño, existe un enlace de feedback donde puedes votar para cambiar esto en proximas versions de SQL: connect.microsoft.com/.../sql-2008-audit-record-for-stored-procedure-is-not-parameterized

    Saludos!

    Edinson Medina

  18. Hola Epic,

    Si posees la version Standard de SQL 2008, Si, solo podras hacer auditorias a nivel de objetos de bases de datos con un trigger, aca hay un post que te puede ayudar, http://www.mssqltips.com/.../keeping-your-sql-server-ddl-trigger-audit-table-in-check

    Saludos!

  19. Victor Diaz says:

    Buenas tardes, estoy tratando de auditar los eventos DML un usuario de dominio que se conecta a la instancia y no me lo permite, cual será el problema

  20. Santiago bolzico says:

    Estoy tratando de registrar todos los insert , update y delete que se realizan sobre todas las tablas de un db. Esto es posible? o hay que agregar select para tablas, update para tablas, delete para tablas. No me permite agregar una accion select para
    un db. Saludos

  21. Santiago says:

    Gracias, lo solucione haciendolo por codigo, del diseñador no me deja.. gracias por la ayuda

  22. Nem says:

    Me da el siguiente error:

    Audit 'Audit_name' failed to start. For more information, see the SQL Server error log. You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'.

  23. Chica10 says:

    Si no se encuentra activa la opcion de audit no existe otra forma de saber los insert delete updates de un usuario sobre una tabla específica de la bd. GRACIAS!

  24. Luciano says:

    Estimado te felicito por tu post...
    mi consulta es: se puede ver que usuario hicieron las modificaciones y desde que maquina hicieron dichas modificaciones..Saludos

  25. Luciano says:

    Y como se puede ver eso doctor... porque según las indicaciones que has enseñado al momento de reportar no se observa el usuario ni menos desde que maquina se ejecuto la consulta....

  26. Luis Ortiz says:

    Hola, al tratar de implementar la auditoria encontre que dentro de Security solo me muestra Logins, Server Roles, Credentials, pero no me aparece Audit. Sera que algo no se instalo o hay algo que debe hacer para habiliar las opciones de auditoria.

    saludos.

  27. Ulises Baron says:

    Hola, me gustaría saber si en la versión 2008 se puede verificar si se cambio la contraseña de un inicio de sesión, gracias.

  28. Hola Luis Ortiz, Es probable que no tengas los permisos adecuados.

  29. Hola Ulises Baron, si puedes auditarlo usando el Server-Level Audit specification LOGIN_CHANGE_PASSWORD_GROUP (https://technet.microsoft.com/en-us/library/cc280663(v=sql.100).aspx)

  30. Anthony says:

    Que tal, quisiera que me ayudaran porfavor sobre la auditoria, veran al verificar lo que ha registrado la auditoria, resulta que la hora que muestra no es la misma a la hora que se efectuo dicha accion, porfavor si fueran tan amables de poder ayudarme
    es urgente

  31. César says:

    A mi me pasa, exactamente lo mismo, la hora que me muestra la auditoria, es 2 horas menos que la hora en la que se a realizado realmente.

    Agradecería mucho si me ayudaran a solucionarlo, o por lo menos me explicaran a que se debe.

    Un saludo y gran articulo.

  32. Hola Anthony, Cesar. El timestamp de la auditoria es un 64 bit integer generado del tiempo en la zona horaria UTC, es por esto que si estan en zonas horarias diferentes a UTC deberan restar o sumar la diferencia. Saludos!
    https://msdn.microsoft.com/en-us/library/cc280649.aspx

  33. Emmanuel Ornelas says:

    que query puedo usar para habilitar una auditoria? y y donde se guarda al momento de habilitarla?
    gracias por su apoyo

    Muy buen post!!!!!

  34. Alondra Hernandez says:

    Disculpa me marca un error aun me podrias explicar
    Msg 33073, Level 16, State 1, Line 2
    Cannot find audit 'AuditarLoginsFallidos' or you do not have the required permissions.
    Msg 33073, Level 16, State 1, Line 3
    Cannot find audit 'Audit-EmployeeQueries' or you do not have the required permissions.

Skip to main content