Error login failed for user al conectar a SQL Server desde una aplicacion .NET (C#)

Hola, chicos,
Buenas tardes.

En este post comentaré un problema que encontré recientemente en una empresa, en la que ninguna aplicación lograba conectarse a la base de datos de producción, presentando el mensaje “Error de inicio de sesión para el usuario ‘usuario’”.

Introducción y descripción del problema.

En la empresa donde ocurrió este error de conexión, existen varias aplicaciones que se conectan a la base de datos SQL Server de producción, utilizando el mismo usuario, con autenticación SQL Server. Dado este escenario, resulta difícil identificar desde la base de datos con qué aplicación está asociada una sesión, o incluso qué usuario de AD inició sesión en la aplicación y realiza esas acciones en la base de datos.

Para hacer posible esta identificación, un analista experimentado desarrolló un cambio en las aplicaciones C#, de modo que informó el nombre del usuario que inició sesión en AD y el nombre del sistema en el parámetro “Nombre del programa”, en la cadena de conexión con la base de datos, quedando así:
Data Source=myServer; Initial Catalog=myDB; User Id=myUsername; Password=myPassword; Application Name=UsuarioAD/Sistema;

Hicimos las pruebas y todo funcionó muy bien. La aplicación estaba enviando el usuario/sistema y a través de WhoIsActive se pudo identificar claramente al usuario y sistema responsable de cada sesión en la base de datos de producción, a través de la columna program_name.

Unos minutos después de que este cambio se pusiera en marcha en producción, comenzaron a llegar varios tickets y alertas informando el error "Error de inicio de sesión para el usuario 'usuario'", donde los nombres de host de origen eran los servidores IIS de producción.

El primer paso para intentar identificar lo que estaba sucediendo fue analizar la base de datos. Validamos que la contraseña fuera correcta y el inicio de sesión fue exitoso utilizando el usuario de la aplicación, ya que hasta ese momento sospechaba un cambio de contraseña.

Se verificó en los registros de SQL Server (Administración > Registros de SQL Server) y no hubo ningún registro de error de inicio de sesión debido a una contraseña incorrecta. Confirmé en la configuración del servidor que la opción de auditoría de inicio de sesión para errores de inicio de sesión estaba efectivamente habilitada (figura a continuación) y de hecho no había ningún registro de contraseñas incorrectas en los servidores de producción.

Después de eso, confirmamos que la cadena de conexión, que se cambió, apuntaba efectivamente al servidor de producción. Mientras tanto, los mensajes de error se fueron acumulando y aunque esto ocurría, varios usuarios pudieron utilizar el sistema con normalidad, conectándose a la base de datos con normalidad. Hicimos algunas pruebas en la pantalla del sistema y el error era intermitente: a veces funcionaba, a veces mostraba un error de "Error de inicio de sesión".

Decidimos reiniciar los servidores IIS, lo que resolvió el problema durante unos minutos, pero volvió a ocurrir unos 20 minutos después.

Fue entonces cuando plantearon el tema del grupo de conexiones de SQL Server. Según la documentación de Microsoft (https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx), se crea un grupo de 100 conexiones (valor predeterminado) para cada cadena de conexión única, es decir, para cada combinación de usuario/sistema, SQL Server estaba reservando 100 conexiones.

Debido a este cambio en la cadena de conexión, se acabó alcanzando el límite de conexiones del banco y provocando el problema mencionado en el post.

Para aquellos que no lo saben, el pool de conexiones es una característica muy útil, ya que reduce considerablemente la sobrecarga generada en las aplicaciones por la apertura/cierre de conexiones, ya que el pool de conexiones mantiene las conexiones a la base de datos siempre abiertas (durante un período determinado), incluso si están inactivas por falta de actividad, y gestiona la apertura/cierre de las conexiones a la base de datos.

Parámetros relacionados con el grupo de conexiones en la cadena de conexión

Pruebas del grupo de conexiones

Si esto le sucede a usted, o a un desarrollador tiene la misma idea, ahora conoce los impactos de cambiar la cadena de conexión y cómo resolverlo. En este caso, como se abriría un pool de conexiones para cada usuario/sistema, se podría utilizar un pool más pequeño, de 4 conexiones, por ejemplo, pero habría muchos pools abiertos en el banco y seguiría existiendo la necesidad de seguir abriendo y cerrando conexiones, por lo que el pool de conexiones no tendría mucho sentido en este modo de operación.

Después de resolver el problema, el equipo de desarrollo decidió probar esta teoría creando un pequeño programa que simplemente abría 100 conexiones usando una cadena de conexión normal, con el nombre de la aplicación fijo y el parámetro Tamaño máximo del grupo configurado con el valor 20. Después de la ejecución, solo se abrieron 20 conexiones en el banco.

Al cambiar el programa para generar valores aleatorios para el parámetro Nombre de la aplicación, SQL Server en realidad reservó 20 conexiones para cada una de las 100 conexiones realizadas.

Cómo identificar la cantidad de conexiones de instancias de SQL Server

Para realizar estas comprobaciones del número de conexiones en la instancia, puede utilizar una de las siguientes consultas:

SELECT 
    original_login_name,
    [host_name],
    client_interface_name,
    COUNT(*)
FROM 
    sys.dm_exec_sessions
GROUP BY
    original_login_name,
    [host_name],
    client_interface_name
ORDER BY
    1, 2, 3


SELECT
    B.login_name,
    B.[host_name],
    B.[program_name],
    DB_NAME(B.database_id) AS [database],
    COUNT(*) AS connections
FROM
    sys.dm_exec_connections             A
    LEFT JOIN sys.dm_exec_sessions      B   ON  A.session_id = B.session_id
GROUP BY
    B.login_name,
    B.[host_name],
    B.[program_name],
    DB_NAME(B.database_id)


-- Apenas SQL Server 2014 em diante
SELECT
    DB_NAME(A.database_id) AS [database],
    A.is_user_process,
    COUNT(A.session_id) AS connections
FROM
    sys.dm_exec_sessions A
GROUP BY
    DB_NAME(A.database_id),
    A.is_user_process
ORDER BY
    1,
    2


SELECT *
FROM sys.dm_os_performance_counters A
WHERE A.counter_name = 'User Connections'

Cómo identificar la cantidad máxima de conexiones de instancias de SQL Server

Para identificar el límite máximo de usuarios configurados en la instancia (el límite máximo de SQL Server es 32,767 conexiones), puede usar uno de los siguientes comandos:

Cómo cambiar el número máximo de conexiones de instancia de SQL Server

Para cambiar la cantidad máxima de conexiones de SQL Server, puede usar el siguiente comando:

USE [master]
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'user connections', 5000; -- Alterando para 5.000 conexões no máximo
GO
RECONFIGURE;
GO

La opción de conexiones de usuario especifica el número máximo de conexiones de usuario simultáneas permitidas en una instancia de SQL Server. La cantidad real de conexiones de usuario permitidas también depende de la versión de SQL Server que esté utilizando y de los límites de su aplicación o aplicaciones y hardware. SQL Server permite un máximo de 32.767 conexiones de usuarios.

Debido a que las conexiones de usuario son una opción dinámica (autoconfigurable), SQL Server ajusta automáticamente el número máximo de conexiones de usuario según sea necesario, hasta el valor máximo permitido. Por ejemplo, si solo 10 usuarios inician sesión, se asignan 10 objetos de conexión de usuario. En la mayoría de los casos, no es necesario cambiar el valor de esta opción. El valor predeterminado es 0, lo que significa que se permiten las conexiones de usuario máximas permitidas (32.767).

¡Eso es todo, amigos!
Espero que hayas disfrutado de esta publicación y ¡hasta luego!