Auditoría descubriendo las Conexiones en SQL Server

Auditoría descubriendo las Conexiones en SQL Server

En el mundo del desarrollo y administración de bases de datos, monitorear las conexiones activas es crucial para asegurar la seguridad y el rendimiento del sistema. SQL Server proporciona varias vistas de administración dinámica (DMVs) que ayudan a los administradores de bases de datos (DBAs) a obtener información detallada sobre las conexiones activas y las sesiones del servidor. Dos de estas DMVs son sys.dm_exec_connections y sys.sysprocesses.

Entendiendo las Vistas de Administración Dinámica (DMVs)

¿Qué son las DMVs?

Las DMVs son vistas y funciones que exponen el estado interno del servidor SQL y proveen información sobre la salud y rendimiento del servidor. Estas vistas son esenciales para la gestión y optimización de las bases de datos, permitiendo a los administradores diagnosticar problemas y tomar decisiones informadas sobre el mantenimiento y la configuración del sistema.

Introducción a sys.dm_exec_connections

La vista sys.dm_exec_connections devuelve información sobre las conexiones de cliente a SQL Server. Cada fila en esta vista representa una conexión de cliente única al servidor. Algunas de las columnas clave en esta vista incluyen:

  • session_id: Identificador de la sesión en SQL Server.
  • client_net_address: Dirección IP del cliente.
  • auth_scheme: Esquema de autenticación utilizado.

Introducción a sys.sysprocesses

La vista sys.sysprocesses proporciona información sobre los procesos activos en SQL Server. Aunque es una vista más antigua y ha sido reemplazada en gran medida por las vistas de administración dinámica modernas, todavía se utiliza comúnmente debido a su familiaridad y la amplia documentación disponible. Algunas columnas importantes son:

  • spid: Identificador del proceso en SQL Server.
  • hostname: Nombre del host desde donde se originó la conexión.
  • program_name: Nombre del programa que inició la conexión.

Consultas Prácticas con sys.dm_exec_connections y sys.sysprocesses

Consulta de Conexiones Específicas por Dirección IP

Para ilustrar el uso de estas vistas, consideremos un escenario donde necesitamos obtener información sobre las conexiones activas desde una dirección IP específica, por ejemplo, 10.28.0.153. La siguiente consulta logra esto uniendo sys.dm_exec_connections y sys.sysprocesses:

SELECT b.spid, b.hostname, b.program_name, a.auth_scheme
FROM sys.dm_exec_connections a
INNER JOIN sys.sysprocesses b
ON a.session_id = b.spid
WHERE a.client_net_address = '10.28.0.153';

Desglose de la Consulta:

  • SELECT b.spid, b.hostname, b.program_name, a.auth_scheme: Esta cláusula selecciona las columnas que nos interesan de ambas vistas.
  • FROM sys.dm_exec_connections a: Especifica la vista principal de donde estamos obteniendo las conexiones.
  • INNER JOIN sys.sysprocesses b: Realiza una unión interna con la vista sys.sysprocesses usando session_id y spid como claves de unión.
  • WHERE a.client_net_address = ‘10.28.0.153’: Filtra los resultados para mostrar solo las conexiones desde la dirección IP 10.28.0.153.

Esta consulta nos proporciona información útil, como el esquema de autenticación utilizado (auth_scheme), que puede ser Kerberos, NTLM, SQL, entre otros.

Consulta de Todas las Conexiones Ordenadas por Dirección IP

Para obtener una visión completa de todas las conexiones activas y ordenarlas por la dirección IP del cliente, podemos utilizar la siguiente consulta:

SELECT a.*
FROM sys.dm_exec_connections a
INNER JOIN sys.sysprocesses b
ON a.session_id = b.spid
ORDER BY a.client_net_address;

Desglose de la Consulta:

  • SELECT a.*: Selecciona todas las columnas de sys.dm_exec_connections para una visión detallada.
  • INNER JOIN sys.sysprocesses b: Similar a la consulta anterior, realiza una unión con sys.sysprocesses.
  • ORDER BY a.client_net_address: Ordena los resultados según la dirección IP del cliente.
Auditoría descubriendo las Conexiones en SQL Server
Auditoría descubriendo las Conexiones en SQL Server

Ejemplos Prácticos

Ejemplo 1: Monitoreo de Conexiones desde un Servidor Específico

Imaginemos que estamos administrando una red de servidores y necesitamos monitorear las conexiones provenientes de un servidor específico con la IP 10.28.0.153. Ejecutando la primera consulta, podemos identificar rápidamente todas las conexiones activas desde este servidor, junto con detalles como el nombre del programa que inició la conexión y el esquema de autenticación utilizado. Esto es particularmente útil para identificar conexiones no autorizadas o inesperadas.

Ejemplo de Resultado:

spid | hostname      | program_name          | auth_scheme
-----|---------------|-----------------------|------------
52 | SERVER1 | Microsoft SQL Server | KERBEROS
53 | SERVER1 | SQLCMD | NTLM
54 | SERVER1 | .NET SqlClient Data | SQL

Ejemplo 2: Auditoría de Conexiones para Seguridad

Supongamos que estamos realizando una auditoría de seguridad y necesitamos revisar todas las conexiones activas a nuestro servidor SQL. La segunda consulta nos proporciona una lista completa de conexiones, ordenadas por dirección IP. Esto nos permite identificar patrones inusuales, como múltiples conexiones desde una misma dirección IP, lo cual podría indicar un ataque o una mala configuración del cliente.

Ejemplo de Resultado:

ession_id | client_net_address | auth_scheme
-----------|--------------------|------------
52 | 10.28.0.153 | KERBEROS
53 | 10.28.0.154 | NTLM
54 | 10.28.0.155 | SQL

Buenas Prácticas para el Monitoreo de Conexiones

Automatización de Consultas

Para mantener un monitoreo continuo, es recomendable automatizar la ejecución de estas consultas y almacenar los resultados en una tabla de auditoría. Esto se puede lograr mediante trabajos de SQL Server Agent que ejecuten las consultas a intervalos regulares.

Alertas y Notificaciones

Configurar alertas y notificaciones basadas en ciertos criterios, como un número inusual de conexiones desde una misma dirección IP o conexiones que utilizan esquemas de autenticación inseguros, puede ayudar a detectar y responder rápidamente a posibles problemas.

Análisis Periódico

Realizar análisis periódicos de los datos recopilados permite identificar tendencias y patrones a lo largo del tiempo. Esto es crucial para la planificación de capacidad y la identificación de posibles problemas antes de que afecten al rendimiento del sistema.

Conclusión

Las vistas sys.dm_exec_connections y sys.sysprocesses son herramientas poderosas para cualquier DBA que necesite monitorear y gestionar conexiones en SQL Server. Al entender cómo utilizar estas vistas y aplicar consultas específicas, podemos obtener una visión clara y detallada del estado de las conexiones en nuestro servidor, lo que nos permite tomar decisiones informadas para mejorar la seguridad y el rendimiento del sistema.

Monitoreo y Mantenimiento SQL: Mantén Tu Base de Datos Saludable

Descarga de SQL Server Management Studio (SSMS)

Eliminar usuarios huérfanos SQL server

Convertir una Fecha y Hora a Solo Fecha en SQL

Script para saber el histórico de queries ejecutados SQL

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *


El periodo de verificación de reCAPTCHA ha caducado. Por favor, recarga la página.

error: Contenido protegido :)