Categoría: Seguridad de la base de datos

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.

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

Eliminar usuarios huérfanos en SQL Server, puedes seguir estos pasos detallados y hacer uso de un script SQL que te ayudará a identificar y eliminar dichos usuarios de una manera ordenada. Aquí te dejo una guía detallada, que también podría ser útil como entrada de blog:

¿Por qué borrar usuarios huérfanos en SQL después de una migración?

Las migraciones de bases de datos son procesos complejos que, a menudo, pueden generar datos residuales no deseados, como usuarios huérfanos. Estos usuarios son cuentas que ya no están asociadas a ningún inicio de sesión válido y pueden suponer diversos riesgos para la seguridad y el rendimiento de la base de datos.

En este artículo, exploraremos las razones principales por las que es crucial eliminar los usuarios huérfanos en SQL después de una migración:

1. Mejorar la seguridad:

Los usuarios huérfanos representan una brecha de seguridad potencial, ya que podrían ser utilizados por actores maliciosos para acceder a la base de datos sin autorización. Al eliminarlos, se elimina este punto de entrada vulnerable y se protege la integridad de los datos.

2. Optimizar el rendimiento:

Las bases de datos con un gran número de usuarios huérfanos pueden experimentar un rendimiento más lento, especialmente durante las consultas que involucran permisos y roles. Eliminar estos usuarios innecesarios libera recursos del sistema y mejora la eficiencia general de la base de datos.

3. Simplificar la administración:

La presencia de usuarios huérfanos dificulta la administración de la base de datos, ya que aumenta la complejidad de tareas como la concesión de permisos y la auditoría de acceso. Eliminar estos usuarios simplifica el entorno y facilita la gestión de la base de datos.

4. Mejorar la calidad de los datos:

Los usuarios huérfanos pueden generar entradas de registro y datos de auditoría incorrectos, lo que dificulta la identificación de patrones y la comprensión del uso real de la base de datos. Eliminar estos usuarios garantiza que los datos de la base de datos sean precisos y confiables.

5. Cumplimiento normativo:

En algunos sectores, las regulaciones de cumplimiento de datos, como GDPR y HIPAA, exigen la eliminación de datos personales no utilizados. Eliminar usuarios huérfanos ayuda a garantizar el cumplimiento de estas regulaciones y protege la privacidad de la información sensible.

Script para borrar usuarios huérfanos y sus esquemas

---------------------------------------------------------------------------------------
-- ELIMINAR usuarios huérfanos y sus eschemas  
---------------------------------------------------------------------------------------

-- www.sql-ninja.com

set nocount on

declare @cadena varchar(4000)
declare @usuario sysname
declare @nombre_usuario sysname
declare @principal_id int
declare @usuario2 sysname

---- coge los usuarios huérfanos (que no tiene un login con mismo nombre ni mismo codigo (SID)
select name, principal_id 
into #usuarios    --- tabla temporal con los usuarios
from sys.database_principals
where type in ('S','U','G')
and (sid is not null and sid <> 0x0)
and name not in (select name from sys.server_principals)  -- no existe con mismo nombre
and sid not in (select sid from sys.server_principals)    -- no existe con mismo sid
and principal_id > 1
and db_id() > 4 
order by name

declare cur_usuarios cursor 
  for  select name,principal_id from #usuarios
  order by name

  Open cur_usuarios

  FETCH NEXT FROM cur_usuarios 
  INTO @nombre_usuario, @principal_id

  WHILE @@FETCH_STATUS = 0
  BEGIN
    select @usuario= @nombre_usuario
	select @usuario2='[' + @nombre_usuario +']'
	
    declare @schema_name sysname , @cant_schemas int
	set @schema_name = null
	set @cant_schemas = 0
	--- verifica la cantidad de schemas que tiene
	select @cant_schemas = count(*) from sys.schemas where  principal_id = @principal_id

	print '*********   USUARIO ---> ' + @nombre_usuario
	if @cant_schemas > 0
	begin
	    While @cant_schemas > 0
		begin
			select top(@cant_schemas) @schema_name = '[' + name +']' from sys.schemas where  principal_id = @principal_id
			print ' --- Se procedera a borrar el SCHEMA: ' + @schema_name
			set @cadena='   drop schema ' + @schema_name + ''
			print @cadena
			exec ( @cadena )   ----- ELIMINA schema
			set @cant_schemas = @cant_schemas - 1
		end
	end
	---- select schema_name(s.schema_id),user_name(s.principal_id),*
	print ' --- Se procedera a borrar el USUARIO: ' + @usuario
	set @cadena='   drop user ' + @usuario2 + ''
	print @cadena
	exec ( @cadena )     ----- ELIMINA usuario
	  
  FETCH NEXT FROM cur_usuarios 
  INTO @nombre_usuario,@principal_id
END
CLOSE cur_usuarios
DEALLOCATE cur_usuarios

set nocount off

drop table #usuarios
go

Explicación del código para borrar usuarios huerfános en SQL

Este script de SQL está diseñado para eliminar usuarios huérfanos y sus esquemas asociados en una base de datos. Los usuarios huérfanos son aquellos que no tienen un login correspondiente con el mismo nombre o el mismo SID (Security Identifier). Aquí está el desglose del script:

Parte Superior: Comentarios y Configuración Inicial

sqlCopiar código---------------------------------------------------------------------------------------
-- ELIMINAR usuarios huérfanos y sus esquemas  --- Usuario que NO EXISTE LOGIN CON MISMO NOMBRE NI CON MISMO SID
---------------------------------------------------------------------------------------

-- www.sql-ninja.com

set nocount on

• Comentarios: Explica el propósito del script.
• set nocount on: Previene que SQL Server devuelva mensajes de cuenta de filas afectadas, lo que hace que el script sea más limpio en términos de salida.

Declaración de Variables

declare @cadena varchar(4000)
declare @usuario sysname
declare @nombre_usuario sysname
declare @principal_id int
declare @usuario2 sysname

• Declaración de variables: Estas variables se utilizarán para almacenar información temporal durante la ejecución del script.

Selección de Usuarios Huérfanos

select name, principal_id 
into #usuarios    --- tabla temporal con los usuarios
from sys.database_principals
where type in ('S','U','G')
and (sid is not null and sid <> 0x0)
and name not in (select name from sys.server_principals)  -- no existe con mismo nombre
and sid not in (select sid from sys.server_principals)    -- no existe con mismo sid
and principal_id > 1
and db_id() > 4 
order by name

• select: Selecciona los usuarios que no tienen un login correspondiente con el mismo nombre o SID y los almacena en una tabla temporal #usuarios.

Cursor para Iterar sobre los Usuarios Huérfanos

declare cur_usuarios cursor 
  for  select name,principal_id from #usuarios
  order by name

Open cur_usuarios

FETCH NEXT FROM cur_usuarios 
INTO @nombre_usuario, @principal_id

• Cursor: Define un cursor cur_usuarios para iterar sobre los usuarios huérfanos seleccionados anteriormente.

Bucle para Procesar Cada Usuario Huérfano

WHILE @@FETCH_STATUS = 0
BEGIN
    select @usuario= @nombre_usuario
	select @usuario2='[' + @nombre_usuario +']'
	
    declare @schema_name sysname , @cant_schemas int
	set @schema_name = null
	set @cant_schemas = 0
	--- verifica la cantidad de schemas que tiene
	select @cant_schemas = count(*) from sys.schemas where  principal_id = @principal_id

	print '*********   USUARIO ---> ' + @nombre_usuario
	if @cant_schemas > 0
	begin
	    While @cant_schemas > 0
		begin
			select top(@cant_schemas) @schema_name = '[' + name +']' from sys.schemas where  principal_id = @principal_id
			print ' --- Se procedera a borrar el SCHEMA: ' + @schema_name
			set @cadena='   drop schema ' + @schema_name + ''
			print @cadena
			exec ( @cadena )   ----- ELIMINA schema
			set @cant_schemas = @cant_schemas - 1
		end
	end
	---- select schema_name(s.schema_id),user_name(s.principal_id),*
	print ' --- Se procedera a borrar el USUARIO: ' + @usuario
	set @cadena='   drop user ' + @usuario2 + ''
	print @cadena
	exec ( @cadena )     ----- ELIMINA usuario
	  
  FETCH NEXT FROM cur_usuarios 
  INTO @nombre_usuario,@principal_id
END

• Bucle WHILE: Itera sobre cada usuario huérfano.
  • Variables y Preparación: Asigna el nombre del usuario a las variables @usuario y @usuario2 (con corchetes para manejar nombres con espacios o caracteres especiales).
  • Verificación de Esquemas: Cuenta la cantidad de esquemas asociados con el usuario.
  • Eliminación de Esquemas: Si hay esquemas asociados, los elimina uno por uno.
  • Eliminación de Usuario: Después de eliminar los esquemas, elimina el usuario.

Cierre y Liberación de Recursos

CLOSE cur_usuarios
DEALLOCATE cur_usuarios

set nocount off

drop table #usuarios
go

• Cierre del Cursor: Cierra y libera el cursor.
• Restauración de nocount: Vuelve a permitir que SQL Server devuelva mensajes de cuenta de filas.
• Eliminación de Tabla Temporal: Elimina la tabla temporal #usuarios.

Este script es útil para mantener la base de datos limpia al eliminar usuarios huérfanos y sus esquemas asociados, evitando así posibles problemas de seguridad y administración.

La importancia eliminar usuarios huérfanos en SQL después de una migración.

Más allá de las razones generales mencionadas en la entrada anterior, eliminar usuarios huérfanos en SQL después de una migración reviste especial importancia por las siguientes razones:

1. Complejidad de la migración:

Las migraciones de bases de datos, por su naturaleza compleja, pueden generar un mayor número de usuarios huérfanos debido a diversos factores, como:

• Eliminación incompleta de usuarios: Si la migración no elimina correctamente las cuentas de usuario asociadas a objetos de la base de datos origen, pueden quedar usuarios huérfanos en el destino.
• Cambios en la estructura de la base de datos: Si la estructura de la base de datos destino difiere de la origen, es posible que algunos usuarios ya no tengan permisos para acceder a los objetos relevantes, quedando huérfanos.
• Migración de datos incompleta: Si la migración no transfiere completamente los datos de permisos y roles, puede haber usuarios huérfanos en el destino que no estén asociados a ningún objeto o rol.

2. Mayor impacto en el rendimiento:

En el contexto de una migración, el impacto en el rendimiento causado por usuarios huérfanos puede ser más significativo, ya que:

• La base de datos destino es un nuevo entorno: Es posible que la base de datos destino no esté optimizada para manejar un gran número de usuarios huérfanos, lo que puede amplificar la degradación del rendimiento.
• Los recursos del sistema se encuentran en fase de adaptación: Tras la migración, la base de datos destino se encuentra en un proceso de adaptación a la nueva carga de trabajo. La presencia de usuarios huérfanos puede sobrecargar aún más el sistema durante esta fase crucial.

3. Riesgos de seguridad amplificados:

En el contexto posterior a una migración, los riesgos de seguridad asociados a usuarios huérfanos se intensifican:

• Entorno menos familiar: Los administradores del sistema pueden estar menos familiarizados con la nueva base de datos destino, lo que aumenta la probabilidad de pasar por alto usuarios huérfanos durante las revisiones de seguridad.
• Mayor superficie de ataque: Una migración puede ampliar la superficie de ataque de la base de datos, ya que los actores maliciosos pueden explorar los nuevos usuarios huérfanos como puntos de entrada vulnerables.

4. Mayor dificultad para la resolución de problemas:

La presencia de usuarios huérfanos puede dificultar la resolución de problemas después de una migración:

• Diagnóstico confuso: Los errores o fallos de funcionamiento pueden tener su origen en usuarios huérfanos, lo que dificulta la identificación precisa de la causa raíz del problema.
• Soluciones más complejas: La resolución de problemas relacionados con usuarios huérfanos puede requerir soluciones más complejas y que consumen más tiempo, desviando recursos de otras tareas importantes.

Cambiar el collation en un servidor sql server 2019

Creación de Roles en SQL Server

Descarga de SQL Server Management Studio (SSMS)

Generando Script de creación de Usuarios en SQL Server