El efectuar La auditoría de SQL Server pasó de una alternativa súper agradable a un transformarse en un requisito legal, especialmente después de una nueva legislación como HIPAA y GDPR. Por esta razón se tiene que ahora las organizaciones tienen la tarea de auditar el acceso a los registros, detectar e informar sobre actividades sospechosas y potencialmente maliciosas, efectuar una acción de auditoría forense a los cambios en los datos, así como rastrear en esos intentos de inicio de sesión, los cambios de seguridad y mucho más.

La auditoría de SQL Server se puede dividir en varias técnicas:

Auditoría manual – esto puede implicar un conjunto de consultas, investigaciones y posiblemente informes para realizar un seguimiento y control de la actividad por cada tabla, las transacciones de los usuarios, los cambios recientes en las tablas confidenciales, etc. Pero, además de consumir y llevar mucho tiempo en esta actividad, será prácticamente imposible aplicar a todos los posibles eventos de auditoria

Eventos extendidos de SQL Server – como sustitución y el reemplazo definitivo para los elementos rastreadores y de SQL Server Profiler, se tiene que los eventos extendidos ofrecen varias ventajas, incluidas las herramientas GUI integradas y un rendimiento potencialmente mejorado. Los eventos extendidos pueden auditar una amplia gama de acciones, pero sufren algunas limitaciones y además de deficiencias, como no poder proporcionar información aclaratoria sobre lo que se eliminó o insertó en la ejecución de todos estos procesos. Además, no hay medios para la auditoría de antes y después para comparar valores nuevos y antiguos para las actualizaciones.

Desencadenadores de SQL Server – testos han sido unos elementos establecidos básicos durante años. Adicionalmente se pueden configurar fácilmente y rastrear una variedad de datos de información. Los desencadenadores y disparadores se prestan a una personalización completa que permite a los usuarios crear sus propios repositorios de información de auditoría. Los desencadenadores son una tecnología intrusiva invasiva y pueden arrojar y generar errores a las aplicaciones de sus clientes cuando se rompen o se dañen. No se recomiendan para operaciones/tablas de alto rendimiento o de inserción masiva y el mantenimiento de una capa basada en disparador puede consumir y llevar mucho tiempo.

Nota: Para agregar automáticamente la auditoría basada en plantilla DML basada en disparador (y DDL) a las bases de datos de SQL Server – vea ApexSQL Trigger

Registros de transacciones de SQL Server – el registro de transacciones en SQL Server es como la caja negra existente de un avión. Grabará todo lo que ocurra, lo que se presta bien con los objetivos de lograr los fines como son la auditoría. No hay sobrecarga adicional ya que este ya es un proceso integrado en SQL Server. Pero hay que considerar los archivos de registro los que son notoriamente difíciles de leer e incluso cuando pueden ser, los datos que no están organizados para un acceso fácil de consumo y gran parte de ellos están en formato hexadecimal. Para tal fin, consulte el artículo Leer un registro de transacciones , para obtener más información sobre varias soluciones.

SQL Server Profiler y las trazas de SQL Server – esta ha sido la tendencia y la opción de la tecnología con los fines requeridos para auditar en SQL Server durante años, pero conlleva a producir grandes cantidades de información y este programa llegará a ser obsoleto. Lea más sobre esto aquí ¿Es este el final de SQL Profiler?

Auditoría de SQL Server – esta característica ya ha existido desde SQL Server 2008 y adicionalmente también ofrecen capacidades de auditoría rudimentarias “agregadas”, como quién realizó un cambio y cuándo se lo efectuó, pero de manera singular no ofrece otra información, que hoy se consideraría esencial, como que fue realmente el cambio realizado.

Seguimiento de cambios – esta característica de SQL Server está un paso delante y previo de la Auditoría de SQL Server, pero fundamentalmente el mismo requiere algo de experiencia y comprensión de las estructuras de tabla subyacentes y solo realiza un seguimiento de los cambios en la clave principal, por esta razón es que no es tan útil ya que solo sirve para nada más que auditorías muy superficiales

Captura de datos de cambio (también conocido como CDC) – esta es la mejora de SQL Server en el seguimiento de cambios y muestra que está disponible en la edición estándar desde SQL Server 2016. En esta circunstancia, CDC ofrece mucha mejor información y capacidades de auditoría en contraste con el que ofrece el seguimiento de cambios, pero carece de una interfaz de usuario para realizar la visualización y el procesamiento de información de manera fácil. Se requerirá de mayor conocimiento de T-SQL para consultar las tablas y extraer información

Tablas temporales – Testa es otra característica que ha sido introducida por el SQL Server que ofrece una solución complementaria a los CDC. Considerando el visualizar las tablas temporales se puede ver un historial completo de cambios y también se pueden utilizar los mismos con fines de recuperación. La característica requiere la creación de tablas de historial para cada tabla de auditoría y requiere interacción con T-SQL para ver los resultados. Consulte Concepto y conceptos básicos de las tablas temporales en SQL Server 2016 para obtener más información sobre esta característica

¿Qué solución o combinación de soluciones es la más apropiada? Por esta razón es que depende de sus requisitos de auditoría, el tiempo para configurar y mantener las pistas de auditoría, así como los requisitos de almacenamiento, seguridad e informes.

Auditoria manual

Ejecutar estos documentos o scripts para auditar ciertos eventos o actividades es algo que la mayoría de los administradores de bases de datos han estado efectuando, en un momento u otro. La colección de documentos ad hoc se puede construir con el tiempo en un poderoso kit de herramientas especialmente combinado con funcionalidades nativas como CDC y tablas temporales. Muchos de estos documentos de código abierto están disponibles y pueden ser encontrados en Internet. Pero, en general, tales soluciones son gratuitas solo si su administrador de base de datos funciona de forma Pro Bono. De lo contrario, su producción y mantenimiento pueden consumir recursos de tiempo y costos adicionales, especialmente si el propietario de esta solución deja la empresa o cambia a otro puesto. Así que renunciaremos a las discusiones sobre tales soluciones manuales.

Utilizando eventos extendidos de SQL Server

La versión actualizada de SQL Server Audit es una característica de SQL Server, esta versión es introducida por primera vez en 2008 la misma que usa SQL Server Extended Events con objeto de efectuar y auditar las acciones de SQL Server. Permite auditar diferentes acciones, proporcionando mucha granularidad en el proceso de configuración y cubriendo un amplio rango de la actividad de SQL Server

Para crear un nuevo objeto de Auditoría de SQL Server se deben seguir los siguientes pasos:

1. Expanda Seguridad y haga clic con el botón derecho en Auditorías en SSMS

2. Seleccione Nueva auditoría

   

3. Deberá crear un nombre para la auditoría y luego indicar si debe almacenar los datos de auditoría en el registro de eventos de seguridad de la aplicación, el registro de eventos o un archivo. Finalmente, indique una ubicación para el archivo de auditoría

   

4. Haga clic en Aceptar y su auditoría aparecerá en el nodo Auditorías del Explorador de objetos

5. Por defecto, está deshabilitado. El estado deshabilitado se indica con una flecha roja. Haga clic derecho y seleccione Habilitar auditoría, para habilitar

   

6. Dependiendo de si desea auditar la actividad en una instancia completa de SQL Server o solo en una base de datos particular, usted podrá elegir entre Especificación de auditoría del servidor o Especificación de auditoría de la base de datos

7. Para una especificación de auditoría de base de datos, expanda el nodo de la base de datos para auditar, vaya a Seguridad, haga clic con el botón derecho en Especificaciones de auditoría de base de datos y seleccione Nueva auditoría de base de datos

   

8. En el cuadro de diálogo Crear especificación de auditoría de base de datos, deberá indicar el nombre de la especificación, asocie la especificación con el objeto de auditoría creado en el paso anterior, especifique la actividad a auditar en el Tipo de acción de auditoría. Con el objeto de auditar una base de datos en particular, indique la base de datos, el objeto o el esquema como una clase de objeto, el nombre del objeto auditado y el inicio de sesión auditado

   

Cuando visualice en la lista desplegable para el tipo de acción de auditoría, se puede observar que todas las acciones se pueden auditar utilizando Auditoría de SQL Server

En este cuadro de diálogo, se deberán especificar las cuentas de usuario que se supervisarán.

Es importante indicar que las especificaciones de auditoría de la base de datos están deshabilitadas, de manera predeterminada. Con objeto de poderlas habilitar, seleccione esta opción en el menú contextual

Ahora, todas las declaraciones DELETE ejecutadas contra Person.BusinessEntityAddress serán auditadas e insertadas en archivos cuyos nombres comiencen con Audit-, como Audit-AW2012Test_9D93CA4A-8B90-40B8-8B0B-FCBDA77B431D_0_130161593310500000.sqlaudit \, y se almacenarán en E:\10500000.sqlaudit\, y se guardarán en E:\

Se debe considerar que, para bases de datos de gran volumen, se considera una buena práctica guardar la información auditada en un archivo. Pese a ello, este archivo no se puede abrir directamente, incluso con un editor hexadecimal. Para verlo, use fn_get_audit_file

Por ejemplo:

muestra los siguientes resultados:

En la obtención de los resultados incluirán columnas, pero no se incluirán los datos reales que han sido borrados. Por esta razón es importante verificar de quien se trata el usuario, quién realizó la eliminación y cuándo se realizó esta acción de eliminación por lo que representa a los principales elementos de auditoría aquí, lo cual es una desventaja para los usuarios que necesitan datos de auditoría más completos.

Otras desventajas incluyen:

• SQL Server Audit utiliza los recursos del propio previo SQL Server auditado, lo que puede degradar el rendimiento
• Es difícil administrar de manera integral varias instancias y consolidar los datos de auditoría
• Hay mucho trabajo muy reciente involucrado en la administración, análisis y archivo de datos de auditoría, ya sea en un archivo o registro, y requiere un esfuerzo manual para importar, archivar e informar
• Esta función no está disponible en la versión estándar de SQL Server hasta la versión 2016 de SQL Server

Usar desencadenadores de SQL Server

Los desencadenadores de SQL Server son quizás tan antiguos como el propio SQL Server (no me cite al respecto). Según esta definición por su nombre, son artefactos existentes en el motor de SQL Server que “disparan” en un evento particular, como la inserción de un nuevo registro. Existen desencadenadores para las operaciones DML (datos) y DDL (esquema) y, dado que están basados en T-SQL, estos mismos pueden personalizarse completamente e integrarse directamente en su base de datos.

Un desencadenador existe para efectuar un desencadenante para una solamente una tabla, por lo que se deberá crear y mantener al menos un desencadenante para cada tabla “sensible” que desee auditar. Como los disparadores son objetos definidos por el usuario, se debe escribir T-SQL para especificar qué datos capturar y, en última instancia, qué hacer con ellos. Por consiguiente, Será necesario crear una tabla o tablas, a las cuales los desencadenantes apuntarán y entregarán la información auditada que han capturado. Entonces esta información de seguimiento de auditoría puede consultarse e informarse

Para ilustrar, usaremos un ejemplo. En nuestro caso, un desencadenador que se activa después de insertar un registro en la tabla Person.Person el mismo inserta un nombre, hora y fecha de la tabla cuando se insertó el registro y el nombre de usuario utilizado para insertar el registro en un dbo. La tabla del repositorio debe verse de esta manera:

Aunque integrados y efectivos, los desencadenantes son difíciles de crear y requieren mucho tiempo, ya que se puede necesitar cientos o más de estos disparadores, y mantenerlos requiere de un costo adicional bastante considerable, ya que deberán actualizarse cada vez que se cambie la tabla subyacente. El uso de herramientas de terceros para automatizar la creación de disparadores basados en plantillas puede ayudar

ApexSQL Trigger es una herramienta de auditoría de la base de datos de SQL Server para capturar datos y cambios de esquema incluido el de personalizar quién realizó el cambio, qué objetos se vieron afectados, cuándo se realizó el cambio, así como la información sobre el inicio de sesión SQL, la aplicación y el host utilizados para realizar el cambio. Adicionalmente Almacena toda la información capturada en una tabla de repositorio central. Los datos de auditoría pueden ser reportados y exportados. Los disparadores se basan en plantillas que se pueden personalizar. Una vez que se han efectivizado y diseñado, cada desencadenador se creará perfectamente, sin errores y se pueden crear miles de disparadores en segundos. Si la base de datos subyacente cambia, los desencadenantes afectados se pueden refactorizar fácilmente.

Para crear una pista de auditoría basada en desencadenador de base de datos SQL Server con ApexSQL Trigger, siga estos pasos

1. Inicie el disparador ApexSQL

2. Conéctese a la base de datos para auditar

   

3. En la cuadrícula principal, seleccione las tablas para auditar

   

4. En el panel Columnas, seleccione las columnas para auditar

   

5. Verifique las transacciones a auditar, incluyendo Insertar, Actualizar y/o Eliminar
6. Repita los pasos 3 a 5 para todas las tablas que desea auditar
7. En el menú, haga clic en Create triggers

8. El script que genera los disparadores especificados se muestra en el cuadro de diálogo Script. Compruébelo y presione F5 para ejecutarlo contra la base de datos para crear los desencadenantes

   

Una vez que se crean los disparadores, se activarán por cada INSERTAR, BORRAR y ACTUALIZAR una vez que hayan sido ejecutados en la tabla, a partir de ese momento, la totalidad de los detalles de la operación se almacenaran en tablas predefinidas por el usuario.

Efectuar los informes son una tara fácil con los reportes integrados, y así como de esta manera las tablas de auditoría también se pueden consultar directamente

Los disparadores representan unos bloques de construcción muy poderosos y que brindan una buena solución de auditoría de SQL Server, pero por el contrario las desventajas incluyen la inversión de la cantidad de tiempo para crearlos y mantenerlos. Para tablas de transacciones altas, los desencadenantes también pueden agregar una sobrecarga de rendimiento innecesaria.

Lectura de registros de transacciones

Si alguna vez quisiste una parada corta y rápida para obtener información completa de auditoría de transacciones de SQL Server, hay que considerar que el registro de transacciones probablemente sería el primer lugar donde buscarías. Por definición, se debe mantener un historial de todo lo ejecutado en SQL Server por cuanto cada cambio de datos y esquema se agrega automáticamente al registro de transacciones en línea. Simplemente el lograr obtener el acceso a esta información puede considerarse como una solución de auditoria en sí misma.

El desafío es que el registro de transacciones nunca fue diseñado para la percepción de los ojos humanos. Abrir fuentes de datos de registro de transacciones como el registro en línea, los registros separados o las copias de seguridad de registros constituye en un desafío, pero una vez abierto el mismo, el leerlos resulta en una actividad mucho más dificultosa. Existen varias opciones para leer el registro de transacciones, incluidos fn_dblog, fn_dump_dblog y DBCC PAGE. Para obtener más información sobre estas opciones, consulte el artículo: Lea un registro de transacciones

Cada una de estas soluciones muestra que las mismas tienen algunas limitaciones bastante severas, por cuanto la información que muestran no está organizada y es difícil de procesar. En esta situación se tiene que parte de la información se presenta como hexadecimal, que es imposible de leer y auditar adicionalmente cosas como actualizaciones y blogs también resulta ser actividades muy desafiantes.

Afortunadamente, existe una herramienta generada por terceros que fue creada para eliminar o abstraer de la complejidad de descifrar archivos de registro, de los usuarios, ya que puede leer los archivos y convertirlos en información útil y fácil de leer, presentada en una cuadrícula más eficiente que permite una manipulación adicional

Para leer los registros de transacciones, use un lector de registro de transacciones de SQL Server como ApexSQL Log. Este log Audita, revierte o reproduce datos y cambios de objetos que han afectado a una base de datos, los mismos que fueron incluidos o los que ocurrieron antes de la instalación de ApexSQL Log. También captura información sobre el usuario, la aplicación y el host utilizados para realizar cada cambio

1. Inicie el registro de ApexSQL

2. Conéctese a la base de datos que desea auditar

   

3. En el paso Seleccionar registros de SQL para analizar, agregue las copias de seguridad del registro de transacciones y los registros de transacciones que se hallan separados y que usted desea leer. Tenga en cuenta que tienen que formar una cadena completa para proporcionar una auditoría exitosa

   

4. Use las opciones de configuración de Filtro opara precisar y reducir el conjunto de resultados usando la hora, el tipo de operación, el nombre de la tabla, el usuario y otras opciones de filtrado

   

5. Haga clic en Abrir

6. Los resultados se muestran en la cuadrícula principal y usted puede fácilmente crear y deshacer los documentos de una forma mucho más efectiva, o exportarlos a archivos CSV, HTML, XML o SQL y guardarlos en el disco duro

   

Las ventajas de usar el registro de transacciones de SQL Server como mecanismo de auditoría incluyen:

• No requieren gastos generales, ya que no hay procesos adicionales para capturar la información de auditoría que pueden afectar el rendimiento de SQL Server
• se puede realizar una auditoría por un período de tiempo incluso antes de instalar la herramienta
• la herramienta se puede configurar para ejecutarse desatendida y todas en horarios que pueden ser nocturnos para capturar un registro continuo de eventos de auditoría directamente en una tabla de SQL Server para consultas directas
• muchas características de valor agregado como filtrado, clasificación, informes, exportación, etc.

Las desventajas son:

• una base de datos debe estar en el modelo de recuperación completa y debe existir una cadena completa de registros de transacciones
• las bases de datos que usan TDE o AlwaysEncrypted no se pueden leer
• no todas las acciones que un usuario podría querer auditar se almacenan en un registro de transacciones. Por ejemplo, sentencias SELECT

Uso de SQL Server Profiler y el rastreo

Es importante mencionar que existe la tecnología en SQL Server para proporcionar una auditoría en ejecución de cada operación realizada y considerar cada evento que ocurre, en forma de seguimientos de SQL Server. Para este fin se debe configurar el SQL Server Profiler para crear tales seguimientos lo que constituye en un método viable para auditar sus bases de datos, pero se debe considerar el hecho de que la cantidad de datos producidos puede ser voluminosa y que Microsoft desaprovechará esta tecnología, y que la misma no es una buena solución a largo plazo

Los eventos extendidos son los sucesores de los perfiles de SQL Server y prometen una tecnología más viable, una con la que Microsoft se ha comprometido en el futuro. Los eventos extendidos cubren todos los eventos encontrados mediante la creación de perfiles, pero sin producir GB de archivos de auditoría que deben procesarse

El desafío consiste en cubrir los servidores SQL que solo admiten profiling y también aquellos que solo cubren eventos extendidos, pese a que por esta razón haya una superposición de versiones. Considere que una solución destinada a la creación de perfiles tendrá que ser abandonada, a medida que una empresa haga la transición a eventos extendidos. Adicionalmente, existe una falta de valor agregado en la funcionalidad adicional como ser alertas, informes, exportaciones, personalización, interfaz con funciones de productividad, etc., Situación que muestra un mayor requerimiento en tiempo y esfuerzo para convertir estas tecnologías de referencia en una solución de auditoría empresarial.

Afortunadamente, existe una herramienta elaborada por parte de terceros que puede cerrar la brecha entre la creación de perfiles y los eventos extendidos y adicionalmente incorpora una capa o nivel bastante voluminosa y gruesa de características y funcionalidades de valor agregado en estas tecnologías subyacentes para proporcionar una solución de auditoría en la modalidad de llave en mano.

ApexSQL Audit es una herramienta de auditoría que ha sido incorporada a SQL Server y que fue construida sobre rastros de SQL Server y los eventos extendidos que proporciona información relativas a las cuestionantes del tipo “quién vio qué, cuándo”. El ecosistema de auditoría de auditoría de ApexSQL también incluye auditoría tolerante a fallas, informes centralizados, interfaz amigable fácil de usar para configurar la auditoría en más de 230 operaciones y un repositorio centralizado a prueba para mitigar los cambios d manipulación subjetivos o extraños para almacenar registros de auditoría y configuración de forma segura. Configura las trazas de acuerdo con las configuraciones configurables, pero adicionalmente puede comenzar la auditoría inmediatamente después de la instalación utilizando su configuración predeterminada que cubre las solicitudes de auditoría más comunes

1. Inicie la la auditoría de ApexSQL

2. Haga clic en ‘Agregar servidor’en la pestaña Configurar para seleccionar un servidor para auditoría

   

3. Haga clic en el botón ‘Agregar base de datos’ para seleccionar una base de datos para auditar y seleccione las operaciones del servidor o la base de datos que desea auditar.

   

Otra opción es usar el tipo de filtro Avanzado:

Ahora, cada vez que se realice cualquier operación que seleccione en SQL Server auditado, se guardará un registro en la base de datos del repositorio central.

Para ver los registros de auditoría, puede usar informes locales integrados

ApexSQL Audit es realmente representa ser un depredador de Apex en el ecosistema de auditoría. El mismo ofrece una interfaz totalmente funcional y rica en características que aísla y sustituye los DBA de los requisitos extensivos de configuración, preparación, mantenimiento e informes. Ofrece características empresariales clave como una alta tolerancia a fallas y resistencia a la manipulación externa que le permite cumplir con los requisitos de auditoría más estrictos. Actualmente con los informes basados en la web, incluso se puede permitir que los usuarios remotos puedan ver informes de auditoría y participar en las auditorías de cumplimiento.

En este artículo hemos revisado una serie de técnicas de auditoría de SQL Server, en ella describimos sus ventajas y desventajas y proporcionamos soluciones prácticas para algunas soluciones.

Para varias de estas soluciones, existen soluciones elaboradas por parte de terceros para agregar un valor significativo a la opción, y a veces haciendo que la solución sea más viable para ser considerada.

Por ejemplo, ApexSQL Trigger mitiga uno de los principales problemas con los desencadenantes, que es el factor preponderante que es el tiempo que tardan en crear y mantener.

ApexSQL Log corta la extensa cantidad de problemas y desafíos normalmente asociados con la lectura del registro de transacciones de SQL Server.

Y finalmente ApexSQL Audit cierra la brecha existente entre la creación de perfiles de SQL Server y los eventos extendidos, al mismo tiempo que proporciona un mayor valor agregado con características y funcionalidades avanzadas

Referencias

• ¿Qué son los archivos de registro virtuales en un registro de transacciones de SQL Server?
• Cómo implementar el manejo de errores en SQL Server
• Lectura del registro de transacciones de SQL Server
• Aplicaciones de tabla temporal en entornos de almacenamiento de datos SQL
• Cuándo usar tablas temporales versus variables de tabla
• Concepto y conceptos básicos de las tablas temporales en SQL Server 2016
• Monitoreo de cambios en SQL Server utilizando la captura de datos modificados
• Replicación lógica de datos de SQL Server 101
• Una descripción general de las herramientas de migración de bases de datos de SQL Server proporcionadas por Microsoft

• Acerca de
• Últimas entradas

Nesha Maric

Sales Engineer en ApexSQL

Nesha is an IT engineer and SQL enthusiast which specialized in SQL Server auditing, compliance and disaster recovery from 2012 onwards.

He is a passionate and competitive gamer and experienced fisherman with a keen eye for perfection and high standards and expectations for anything he invests his time into.

View all posts by Nesha Maric

Últimas entradas de Nesha Maric (ver todo)

• Varias técnicas para auditar bases de datos de SQL Server - October 14, 2019