miércoles, 21 de octubre de 2020

Auditoria en SQL SERVER

 Buenas tardes queridos lectores, el día de hoy les traigo la manera de realizar una auditoria en SQL SERVER, imaginemos que hay datos que se borran, y nadie sabe quien fue, y obviamente el culpable aventará la piedra y esconderá la mano, pues bien, con este método sabremos quien fue el responsable de que los datos se borraran, así que acompáñenme a revisar este post.

Antes de comenzar con la práctica es importante revisar algunos conceptos teóricos. 

¿Qué es una auditoria de datos en SQL SERVER?

Según Microsoft, la auditoría de una instancia de SQL Server o de una base de datos de SQL Server implica el seguimiento y registro de los eventos que se producen en el sistema. El objeto SQL Server Audit recopila una única instancia de acciones y grupos de acciones de nivel de servidor o de nivel de base de datos para su supervisión. La auditoría se realiza en el nivel de instancia de SQL Server . Es posible tener varias auditorías por cada instancia de SQL Server . El objeto Especificación de auditoría de base de datos pertenece a una auditoría. Puede crear una única especificación de auditoría de base de datos para cada base de datos de SQL Server y cada auditoría.



¿Cómo realizar una auditoria en SQL SERVER?


Pues pasemos a lo que nos gusta y como aprendemos que es la práctica, siempre hay dos maneras de hacerlo: mediante entorno gráfico y mediante comandos o Transact-SQL, en este caso lo haremos mediante entorno gráfico

Existen también dos tipos de auditorias:

  • Por instancia
  • Por base de datos
Veremos las dos formas

Por instancia



Damos clic en la + de la ficha Security, y en la ficha Audit, damos clic derecho y damos clic en la opción New Audit


Como nombre de auditoría le pondremos Auditoria de Prueba, en el campo Name.

En Audito Log Faulire nos pregunta si, en caso de encontrar un error, qué acción tomar, en este caso le configuramos que continua.

En Audit destination tenemos varias opciones de salida para el log, en este caso, seleccionaremos la salida a un archivo y seleccionamos la ruta donde se encontrará ese archivo.

En Audit File Maximiun limit: Seleccionaremos la opción predeterminada para que sea un número ilimitado, lo mismo en maximun size y Damos clic en el botón OK.

La instancia de auditoria inicia deshabilitado, por lo cual tendremos que habilitarlo.



Con esto ya tenemos creado nuestra instancia de auditoria, ahora vamos a configurar la auditoria de servidor.




Damos clic en la + de la ficha Security, y abrimos la carpeta Server Audit Specifications, ahí daremos clic con el botón derecho del mouse y seleccionaremos New Server Add Specification...


En el campo Name, pondremos un nombre a nuestra auditoria de Servidor, en mi caso le puse Auditoria Servidor.

En el campo Audit, configuraremos la instancia de Auditoria, en este caso Auditoria de Prueba

En el campo Audit Action Type pondremos la acción de servidor que deseamos auditar, en mi caso, deseo auditar los errores de logins que sucedan, por lo cual, pondremos la acción FAILED_LOGIN_GROUP y damos clic en el botón OK.

La auditoria de servidor de manera predeterminada también esta como inactiva y hay que habilitarla



Al momento de presionar OK, se crea en la ruta que le configuramos un archivo de auditoria que estará vacío y se llenara con los registros de auditoria, de la siguiente manera:

Ahora haremos pruebas colocando mal el login de conexión dos veces



Ahora veremos los registros generados por la instancia de auditoria. Para eso daremos clic derecho en la instancia de auditoria y daremos clic en la opción View Audits Logs 



Como podemos ver, ahí tenemos los registros del fallo en el login.


Pero volvemos a la pregunta original, ¿Cómo podemos saber si alguien eliminó datos?, pues para eso necesitamos crear una auditoria a nivel de base de datos.


Por base de Datos

Ahora vamos a crear una auditoria a nivel de base de datos, es decir, que solo tendrá presencia en la base de datos donde esté creada. Vamos a crear la base de datos, una tabla y le agregaremos algunos datos.


create database prueba

go


use prueba

go

 

CREATE TABLE Tabla_Prueba

(

id int primary key identity,

campo1 varchar(max),

campo2 varchar(max))

 

insert into Tabla_Prueba (campo1, campo2) values ('Emmanuel', 'Urias');

insert into Tabla_Prueba (campo1, campo2) values ('Luis', 'Perez');


Una vez que ya tenemos una base de datos, una tabla y algunos datos, vamos a crear la auditoria de base de datos.


Damos clic en la + de la carpeta Databases, damoc cliec en la + Security, damos clic derecho en la carpeta Database Audit Specifications para abrir la pantalla de configuración



En el campo Name, colocamos un nombre, en mi caso será Auditoria_BaseDatos_1.

En el campo Audit, colocamos el nombre de la instancia de auditoria

En el campo Audit Action Type colocamos el tipo de auditoria, en este caso queremos auditar el DELETE.

En el campo Object Class colocamos que vamos a auditar, toda la base de datos, un objeto o todo un esquema, en mi caso sera DATABASE..

En el campo Object Schema, colocaremos el esquema a auditar, en caso de existir.

El el campo Object Name, colocaremos el nombre del objeto a auditar, en este caso la base de datos PRUEBA.

En el campo Principal Name el nombre del rol o usuario de Base de Datos que vamos a auditar, en este caso el rol public lo tienen todos, por lo cual con ese rol auditaremos a todos los usuarios y presionamos el botón OK.

Ahora haremos un DELETE a la tabla anteriormente creada

delete from Tabla_Prueba


Ahora abrimos nuevamente el Log





Hay demasiadas columnas que no caben en una imagen, así que también se puede consultar mediante TSQL el contenido del archivo de auditoria.

SELECT event_time,session_server_principal_name, server_principal_name, database_name, object_name, statement

FROM sys.fn_get_audit_file ('C:\BD\Auditoria%5de%5Prueba_457F454F-866C-4088-BEF0-7065968003C4_0_132477794306950000.sqlaudit',default,default)

where action_id = 'DL  '




Como ven, con esta consulta nos va a arrojar la bitácora de auditoria en SQL, para manipularla más fácil, como vemos, nos aparece la hora GMT en que fue realizado, el nombre del usuario que lo realizó, el nombre del equipo, la base de datos, el nombre del objeto y la sentencia.

Hasta aquí el post, espero haya sido de su agrado y utilidad, espero compartan, comenten o den clic a alguno de los anuncios patrocinados. Saludos

No hay comentarios.:

Publicar un comentario

LOG Shipping (Transvase de Registros) en SQL SERVER

 Log Shipping es una técnica de alta disponibilidad y recuperación de desastres que nos permitirá, restaurar una base de datos almacenada en...