
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
usandosession_id
yspid
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.

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