none
Auditoría C2 - SQL Server RRS feed

  • Pregunta

  • Buen día. Les quería consultar si antes de habilitar la auditoria C2 hay alguna manera de elegir qué quiero auditar, para no llegar al punto de auditar todo o no auditar nada, ya que esta auditoria tiene el problema de que puede hacer que un servidor deje de funcionar.

    Saludos.

    miércoles, 29 de noviembre de 2017 12:21

Respuestas

  • Depende de qué cosas exactamente quieras auditar. Por ejemplo, si son los logins, desde la pestaña de Seguridad puedes marcar que se auditen los fallidos, los exitosos o ambos. Si son los cambios de datos en las tablas, normalmente te diría que usaras el Change Data Capture o el Change Tracking, pero habría que ver si estaban disponibles en el 2008 R2 Standard, que ahora mismo no lo sé de memoria.

    Si todo eso falla, puedes usar triggers para auditar los cambios, desde los triggers "normales" para los cambios de datos, hasta los triggers de DML para los cambios en el esquema, pasando por los triggers de Logon.

    Otra opción es usar el SQL Trace para grabar una captura de las sentencias SQL que te interese registrar. Esto funciona en todas las ediciones, incluida la Express, pero hay que activarlo por línea de comandos usando un script que te genera el Profiler.

    miércoles, 29 de noviembre de 2017 19:40
  • Básicamente quiero capturar altas, bajas y modificaciones en los usuarios.

    Es decir, que tienes una tabla "Usuarios" en la que quieres capturar los Insert, Delete y Update.

    Podrías usar un Trigger sobre la tabla, pero otra opción es habilitar Change Tracking sobre la misma:

    https://technet.microsoft.com/en-us/library/bb964713(v=sql.105).aspx

    https://technet.microsoft.com/en-us/library/cc280462(v=sql.105).aspx

    Lo he comprobado, y esta funcionalidad sí que está disponible desde el 2008 en todas las ediciones:

    https://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx


    jueves, 30 de noviembre de 2017 13:26
  • Si necesitas elegir qué es lo que quieres auditar, utiliza el SQL Audit en lugar de la auditoría C2. Esta última lo audita "todo", y genera archivos de gran tamaño, lo que puede ocasionar la parada del sistema en caso de que se agote el espacio para grabar el registro de auditoría. En cambio el SQL Audit lo puedes configurar a nivel de servidor o de base de datos, y seleccionar qué eventos concretos quieres auditar. Puedes incluso descender al nivel de "quiero auditar todos los INSERTs en la tabla xxxx realizados por el dbo".
    • Marcado como respuesta D13G083 miércoles, 29 de noviembre de 2017 13:29
    miércoles, 29 de noviembre de 2017 12:33
  • Tengo SQL Server 2008 r2 versión Standard.

    Ese es el problema. En el 2008 se requería una edición Enterprise para poder usar el SQL Audit. Más adelenate lo librealizaron, creo que en la version 2014, en la que sí que se puede usar con todas las ediciones. Pero me temo que con un 2008 r2 Standard no tienes esta opción.
    miércoles, 29 de noviembre de 2017 17:24
  • Pues tienes toda la razon, he intentado borrar una accion y no encuentro forma de hacerlo en la interfaz de usuario. Podrias borrar la accion por linea de comandos:

    ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20171219-164557]
    FOR SERVER AUDIT [AuditOutputFromTesting]
    DROP (AUDIT_CHANGE_GROUP)

    • Marcado como respuesta D13G083 martes, 9 de enero de 2018 20:16
    martes, 19 de diciembre de 2017 16:48
  • En teoría, con el DATABASE_OBJECT_PERMISSION_CHANGE_GROUP ya te debería tomar todos los GRANT, REVOKE y DENY. Véase la descripción en

    https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions

    Si te está fallando, ¿puede ser que en las condiciones le hayas puesto algún filtro sobre solo algunos objetos, o sobre algún Principal?

    viernes, 12 de enero de 2018 19:56
  • No hay problema, puedes asociar las dos especificaciones a la misma auditoria. Es mas, podrias incluso asociar tambien una especificacion del servidor (en lugar de base de datos) a la misma auditoria y funcionan todas juntas.

    Si la separas en dos auditorias, tiene la ventaja de que puedes deshabilitar selectivamente una y no la otra, y de que cada una tiene los eventos de cada base de datos, con lo que no se te mezclan y puede que te resulte mas sencillo consultarlos. Por otra parte, te llevara mas trabajo gestionar un mayor numero de ficheros, y te resultara mas complejo analizar la auditoria cuando quieras consultar algo que abarque todas las bases de datos.

    • Marcado como respuesta D13G083 miércoles, 17 de enero de 2018 13:57
    miércoles, 17 de enero de 2018 13:35

Todas las respuestas

  • Si necesitas elegir qué es lo que quieres auditar, utiliza el SQL Audit en lugar de la auditoría C2. Esta última lo audita "todo", y genera archivos de gran tamaño, lo que puede ocasionar la parada del sistema en caso de que se agote el espacio para grabar el registro de auditoría. En cambio el SQL Audit lo puedes configurar a nivel de servidor o de base de datos, y seleccionar qué eventos concretos quieres auditar. Puedes incluso descender al nivel de "quiero auditar todos los INSERTs en la tabla xxxx realizados por el dbo".
    • Marcado como respuesta D13G083 miércoles, 29 de noviembre de 2017 13:29
    miércoles, 29 de noviembre de 2017 12:33
  • Antes que todo, te agradezco por la respuesta Alberto. Ahora bien, estoy tratando de generar esa auditoria como me indicas pero me encuentro con lo siguiente:

    Tengo SQL Server 2008 r2 versión Standard. Puede ser que esta versión es la que no me deje crear la auditoria a nivel de servidor?

    Saludos.

    miércoles, 29 de noviembre de 2017 14:48
  • Tengo SQL Server 2008 r2 versión Standard.

    Ese es el problema. En el 2008 se requería una edición Enterprise para poder usar el SQL Audit. Más adelenate lo librealizaron, creo que en la version 2014, en la que sí que se puede usar con todas las ediciones. Pero me temo que con un 2008 r2 Standard no tienes esta opción.
    miércoles, 29 de noviembre de 2017 17:24
  • Gracias nuevamente Alberto. Y en este caso que me recomiendas para poder llevar a cabo auditorias, sin utilizar C2 y 2008 r2? En la empresa tenemos pensado tener esta versión al menos un año más.

    Veo que se complica un poco el tema.

    Saludos.


    • Editado D13G083 miércoles, 29 de noviembre de 2017 19:31
    miércoles, 29 de noviembre de 2017 19:12
  • Depende de qué cosas exactamente quieras auditar. Por ejemplo, si son los logins, desde la pestaña de Seguridad puedes marcar que se auditen los fallidos, los exitosos o ambos. Si son los cambios de datos en las tablas, normalmente te diría que usaras el Change Data Capture o el Change Tracking, pero habría que ver si estaban disponibles en el 2008 R2 Standard, que ahora mismo no lo sé de memoria.

    Si todo eso falla, puedes usar triggers para auditar los cambios, desde los triggers "normales" para los cambios de datos, hasta los triggers de DML para los cambios en el esquema, pasando por los triggers de Logon.

    Otra opción es usar el SQL Trace para grabar una captura de las sentencias SQL que te interese registrar. Esto funciona en todas las ediciones, incluida la Express, pero hay que activarlo por línea de comandos usando un script que te genera el Profiler.

    miércoles, 29 de noviembre de 2017 19:40
  • Básicamente quiero capturar altas, bajas y modificaciones en los usuarios. Lo de accesos (válidos e inválidos) ya lo tenía auditado, como me indicas.



    • Editado D13G083 jueves, 30 de noviembre de 2017 11:57
    miércoles, 29 de noviembre de 2017 20:10
  • Básicamente quiero capturar altas, bajas y modificaciones en los usuarios.

    Es decir, que tienes una tabla "Usuarios" en la que quieres capturar los Insert, Delete y Update.

    Podrías usar un Trigger sobre la tabla, pero otra opción es habilitar Change Tracking sobre la misma:

    https://technet.microsoft.com/en-us/library/bb964713(v=sql.105).aspx

    https://technet.microsoft.com/en-us/library/cc280462(v=sql.105).aspx

    Lo he comprobado, y esta funcionalidad sí que está disponible desde el 2008 en todas las ediciones:

    https://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx


    jueves, 30 de noviembre de 2017 13:26
  • Buenas. Antes todo, muchas gracias por las respuesta. Finalmente opté por crear auditorias y luego crear especificaciones para una auditoria en particular. La pregunta que tengo ahora es si una vez creada una especificación, como se muestra a continuación

    Se puede quitar un tipo de acción (ya que no logro hacerlo) o debo generar otra especificación?

    Muchas gracias de antemano.

    Saludos.

    martes, 19 de diciembre de 2017 15:15
  • Pues tienes toda la razon, he intentado borrar una accion y no encuentro forma de hacerlo en la interfaz de usuario. Podrias borrar la accion por linea de comandos:

    ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20171219-164557]
    FOR SERVER AUDIT [AuditOutputFromTesting]
    DROP (AUDIT_CHANGE_GROUP)

    • Marcado como respuesta D13G083 martes, 9 de enero de 2018 20:16
    martes, 19 de diciembre de 2017 16:48
  • Excelente Alberto, anduvo a la perfección. Te consulto una algo más.

    Una vez creada la auditoria, he elegido como destino de los registros "Security Log" para luego tomar estos con un sistema analizador de logs.

    La duda es si hay alguna desventaja, como sucede con Auditoria C2, donde pueda traer problemas con el servidor o situaciones similares.

    Saludos.

    martes, 9 de enero de 2018 20:16
  • Enviarlo al log de Seguridad tiene el problema de que requiere que el proceso SQL Server esté rodando con privilegios administrativos para poder escribir en ese log, y por razones de seguridad no es conveniente que el servicio tenga privilegios administrativos. El log de Aplicación no tiene ese requisito de privilegios elevados.

    Aparte de eso, verifica la configuración del Log en cuanto a tamaño máximo y truncamiento cuando se excede ese tamaño. Si no está configurado para borrar los eventos antiguos, puede llenarse y entonces te detendría el proceso SQL Server, a no ser que lo tengas configurado para continuar funcionando en caso de no poder escribir la auditoría. Pero en ese caso se te quedarían los datos sin auditar. Y si sí que lo tienes configurado para que se trunque, entonces perderías los registros antiguos.

    martes, 9 de enero de 2018 20:37
  • Genial Alberto, ya pude manejar lo que me mencionaste.

    Lo último que me queda y creo que con esto termino es lo siguiente:

    Quiero auditar los permisos que se le pueden dar a usuarios en diferentes objetos, a nivel de base de datos, como te muestro a continuación:

    He probado con las siguientes especificaciones a nivel de base de datos:

    DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
    DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP
    DATABASE_OBJECT_CHANGE_GROUP
    DATABASE_OBJECT_ACCESS_GROUP

    Pero no he tenido el log de los cambios que se aplican a un usuario, por ejemplo, el de asignar una tabla con posibilidad de realizar delete y select.

    Desde ya les agradezco.

    Saludos.

    viernes, 12 de enero de 2018 13:10
  • En teoría, con el DATABASE_OBJECT_PERMISSION_CHANGE_GROUP ya te debería tomar todos los GRANT, REVOKE y DENY. Véase la descripción en

    https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions

    Si te está fallando, ¿puede ser que en las condiciones le hayas puesto algún filtro sobre solo algunos objetos, o sobre algún Principal?

    viernes, 12 de enero de 2018 19:56
  • Ok Alberto, voy a verificar lo que me dices; había elegido la opción que me indicas al principio, pero controlaré todo por las dudas.

    Ahora me surge una duda, si creo auditorias a nivel de servidor y luego varias especificaciones, tanto a nivel de servidor como de base de datos. 

    Supongamos el caso hipotético que creo tres especificaciones a nivel de servidor y dos a nivel de base de datos. Además creo dos auditorias a nivel de servidor, digamos, auditoria_1 y auditoria_2. 

    A la auditoria_1 le asocio las tres especificaciones a nivel de servidor y a la auditoria_2 las dos a nivel de base de datos. 

    ¿Qué ventaja, o desventaja, tendría en crear un esquema como el mencionado o, por ejemplo, colocar en una auditoria todas las especificaciones?

    Si tengo varias especificaciones de distintas bases, es mejor crear una auditoria por base o se puede incluir todas estas especificaciones de base de  datos en una sola auditoría?

    Saludos.

    martes, 16 de enero de 2018 20:00
  • La auditoría a nivel de base de datos, que yo sepa, solo afecta a la base de datos en la que se configura, por lo que si tienes varias bases de datos hay que ponerle a cada una su auditoría. No da igual hacerlo a nivel de servidor, porque son eventos distintos. Por ejemplo, el "create login" se audita a nivel de servidor (porque los logins están en el servidor y no en base de datos), pero el "create table" se audita a nivel de base de datos (porque las tablas están en la base de datos y no en el servidor). Lo que sí puedes hacer es enviar todos los audit specification al mismo destino (tal como un único archivo en disco o el log de aplicación).
    martes, 16 de enero de 2018 21:58
  • La duda no era por ahí, tal vez no me expresé bien. Te pongo un ejemplo:

    Hay dos bases: Prueba_SLG y Prueba_DBA. Cada base tiene creada su especificación. Tengo una auditoria, creada a nivel de servidor, bajo el directorio Auditoria. Mi duda es, ¿qué ventaja, o desventaja, tiene asociar estas dos especificaciones a esa auditoria? o ¿debo crear dos auditorias para asociarle una especificación a cada auditoria?

    Saludos.

    miércoles, 17 de enero de 2018 11:45
  • No hay problema, puedes asociar las dos especificaciones a la misma auditoria. Es mas, podrias incluso asociar tambien una especificacion del servidor (en lugar de base de datos) a la misma auditoria y funcionan todas juntas.

    Si la separas en dos auditorias, tiene la ventaja de que puedes deshabilitar selectivamente una y no la otra, y de que cada una tiene los eventos de cada base de datos, con lo que no se te mezclan y puede que te resulte mas sencillo consultarlos. Por otra parte, te llevara mas trabajo gestionar un mayor numero de ficheros, y te resultara mas complejo analizar la auditoria cuando quieras consultar algo que abarque todas las bases de datos.

    • Marcado como respuesta D13G083 miércoles, 17 de enero de 2018 13:57
    miércoles, 17 de enero de 2018 13:35
  • Con respecto a este tema, vi que obtuve lo que quería con la especificación "SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP" y no con "DATABASE_OBJECT_PERMISSION_CHANGE_GROUP"

    • Editado D13G083 miércoles, 17 de enero de 2018 20:13
    miércoles, 17 de enero de 2018 19:46
  • Buenas tardes. Te quería consultar una duda. En un SQL 2008 r2 Enterprise, tengo implementada las auditorías a nivel de servidor con las siguientes especificaciones:

    SERVER_ROLE_MEMBER_CHANGE_GROUP
    SERVER_PERMISSION_CHANGE_GROUP
    FAILED_LOGIN_GROUP
    SERVER_PRINCIPAL_CHANGE_GROUP
    LOGIN_CHANGE_PASSWORD_GROUP
    DATABASE_PRINCIPAL_CHANGE_GROUP 
    DATABASE_ROLE_MEMBER_CHANGE_GROUP 
    DATABASE_CHANGE_GROUP

    y a nivel de base de datos la especificación tiene lo siguiente:

    SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP

    Mi pregunta es cómo puedo obtener lo mismo pero en un SQL 2008 Estandard dado que no se encuentra disponible este tipo de auditorías.

    Saludos.

    viernes, 26 de enero de 2018 15:55
  • [...] lo mismo pero en un SQL 2008 Estandard

    Mal asunto. En un 2008 Standard vas a tener que usar Triggers de DDL (lo cual requiere escribir bastante codigo SQL) o crear una traza con el SQL Trace y luego analizar esa traza, cosa que tambien sera trabajosa.
    viernes, 26 de enero de 2018 17:03
  • Estoy viendo que los Triggers los puedo utilizar en vistas y tablas, posicionandome en un objeto y de ahí me propone crear un Trigger. Pero si quisiera ver los ABM de los usuarios a nivel de servidor, por ejemplo, cuando le asigno un rol o permisos sobre una base, esto se puede hacer con Triggers? ya que no me lo propone como con las tablas y vistas del sistema.

    Saludos.

    martes, 6 de marzo de 2018 19:43
  • Hola D13G083,

    Te invitamos a que realices tu consulta de manera independiente asi es mas facil para los colaboradores ubicarla y poder brindarte la ayuda necesaria.

    Recibe un coridal saludo.

    Gracias por usar los foros de MSDN.

    Juan

    _____

    Por favor recuerde "Marcar como respuesta" las respuestas que hayan resuelto su problema, es una forma común de reconocer a aquellos que han ayudado, y hace que sea más fácil para los otros visitantes encontrar la solución más tarde.

    Microsoft ofrece este servicio de forma gratuita, con la finalidad de ayudar a los usuarios y la ampliación de la base de datos de conocimientos relacionados con los productos y tecnologías de Microsoft.

    Este contenido es proporcionado "tal cual" y no implica ninguna responsabilidad de parte de Microsoft.


    martes, 6 de marzo de 2018 23:12
    Moderador
  • Estoy viendo que los Triggers los puedo utilizar en vistas y tablas, posicionandome en un objeto y de ahí me propone crear un Trigger.

    Esos son los trigers de DML (Data Modification Language)
    Pero si quisiera ver los ABM de los usuarios a nivel de servidor, por ejemplo, cuando le asigno un rol o permisos sobre una base, esto se puede hacer con Triggers?
    Esos son los triggers de DDL (Data Definition Language) que yo te mencionaba en la respuesta anterior.
    ya que no me lo propone como con las tablas y vistas del sistema.
    No sé si existe algún sitio donde los "proponga". Yo siempre los he creado tecleando el oportuno comando "create trigger ...".
    miércoles, 7 de marzo de 2018 7:23
  • Buen día Juan. He ido marcando como respuestas aquellas que me han solucionado mi problema.

    Igual si hay algo que se me ha escapado, les pido disculpas.

    miércoles, 7 de marzo de 2018 11:30
  • Alberto, cuando mencionaba lo de "proponer" me refería a esto:

    Esto me lo propone en un objeto dentro de las vistas del sistema. Aquí coloco "nuevo triggers" y me muestra una plantilla del mismo, pero en los usuarios que te mencioné no. Pero según tu comentario es como que tengo que armarlo desde cero por linea de comando. Probaré, y te comento las novedades.


    • Editado D13G083 miércoles, 7 de marzo de 2018 11:42
    miércoles, 7 de marzo de 2018 11:38
  • Los triggers de DDL a nivel de servidor salen aqui:

    Screenshot

    Pero no hay una opcion para crearlos a partir de una plantilla, o al menos yo no la he encontrado. Se necesita crearlos con un comando de este estilo:

    CREATE TRIGGER ddl_trig_database   
    ON ALL SERVER   
    FOR CREATE_DATABASE   
    AS   
        PRINT 'Database Created.'  
        SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
    GO  
    

    https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers


    miércoles, 7 de marzo de 2018 12:16
  • Gente, siguiendo con este tema. Estoy haciendo pruebas con un usuario (prueba), y luego de tres intentos inválidos se bloquea. Ahora bien, el log que registro es el siguiente: 

    WinEvtLog: Application: AUDIT_FAILURE(33205): MSSQL$AA22: (no user): no domain: mz30.montemar.cf: Audit event: event_time:2018-04-06 14:57:07.0523966 sequence_number:1 action_id:LGIF succeeded:false permission_bitmask:0 is_column_permission:false session_id:0 server_principal_id:0 database_principal_id:0 target_server_principal_id:0 target_database_principal_id:0 object_id:0 class_type:LX session_server_principal_name: server_principal_name:prueba server_principal_sid: database_principal_name: target_server_principal_name: target_server_principal_sid: target_database_principal_name: server_instance_name:AA22\AA22 database_name: schema_name: object_name: statement:Login failed for user 'prueba' because the account is currently locked out. The system administrator can unlock it.  [CLIENT: 192.168.X.X] additional_information:<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><pooled_connection>0</pooled_connection><error>0x00004836</error><state>1</state><address>192.168.X.X</address></action_info>; .

    Hay una tabla donde me diga que <error>0x00004836</error><state>1</state> equivale que el usuario se encuentra bloqueado? he visto tablas que me dicen que la contraseña no coincide, pero no se si estoy mirando bien.

    Saludos.



    • Editado D13G083 viernes, 6 de abril de 2018 16:05
    miércoles, 4 de abril de 2018 21:04