¡Hola, chicos!
En esta publicación, me gustaría compartir un error que se informó en un grupo de Whatsapp y que personalmente nunca había visto antes, que era el siguiente mensaje de error y el informe de que los usuarios que usaban autenticación SQL solo podían conectarse a la instancia si tenían privilegios de administrador de sistemas.

El inicio de sesión falló para la "prueba" del usuario. Motivo: La validación del acceso al servidor basada en el inicio de sesión falló debido a un error de infraestructura. Comprobar errores anteriores

De inmediato le pedí que verificara si la instancia permitía conexiones provenientes de autenticación de SQL Server, ya que de manera predeterminada está habilitado para aceptar solo conexiones con autenticación de Windows (AD):

DECLARE @AuthenticationMode INT

EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer',   
    N'LoginMode', 
    @AuthenticationMode OUTPUT  

SELECT 
    CASE @AuthenticationMode    
        WHEN 1 THEN 'Windows Authentication'   
        WHEN 2 THEN 'Windows and SQL Server Authentication'   
        ELSE 'Unknown'  
    END AS [Authentication Mode]

Resultado:

La primera validación se llevó a cabo con éxito. Otro punto que puede provocar este tipo de error es el Activadores de inicio de sesión. Le pedí que verificara si había activadores de inicio de sesión en la instancia y, de ser así, que los deshabilitara para probar si eso era lo que la estaba bloqueando:

SELECT * 
FROM sys.server_triggers
WHERE is_ms_shipped = 0
AND is_disabled = 0

Resultado:

Desactivé el disparador y no había nada que impidiera iniciar sesión. Miremos el registro de SQL Server para ver si tenemos alguna pista sobre el problema:

El mensaje de error es muy claro. Mi próximo sospechoso ahora son los permisos en los puntos finales de la instancia. Analizaré los permisos del Endpoint:

SELECT
    sp2.[permission_name],
    e.state_desc,
    e.[name] AS endpoint_name,
    e.principal_id,
    sp.[sid],
    e.is_admin_endpoint,
    sp.is_disabled,
    sp.[name] AS granted_name,
    e.protocol_desc
FROM
    sys.server_permissions AS sp2
    JOIN sys.server_principals AS sp ON sp2.grantee_principal_id = sp.principal_id
    LEFT OUTER JOIN sys.endpoints AS e ON sp2.major_id = e.endpoint_id
WHERE
    sp2.class_desc = 'ENDPOINT'
    AND e.is_admin_endpoint = 0

Resultado:

¡Problema encontrado! La función pública no tiene permiso para acceder a los puntos finales. Probablemente alguien realizó alguna modificación al permiso predeterminado y los usuarios con autenticación SQL no tienen acceso a los puntos finales. Para resolver esto, otorguemos estos permisos:

SELECT
    'USE [master]; GRANT CONNECT ON ENDPOINT::[' + [name] COLLATE SQL_Latin1_General_CP1_CI_AI + '] TO [public];' AS GrantCmd
FROM
    sys.endpoints
WHERE
    is_admin_endpoint = 0

Resultado:

Ahora copie estos comandos Grant y aplíquelos a su instancia para liberar el permiso CONNECT para la función pública en todos los puntos finales.

Después de eso, los usuarios con autenticación de SQL Server pudieron conectarse normalmente a la instancia 🙂

Nota 1: Quitar el permiso CONNECT en puntos finales de función pública no es "incorrecto". El DBA solo necesita pensar en formas de liberar este permiso a usuarios o grupos de autenticación SQL para que puedan continuar accediendo al entorno sin el permiso del administrador del sistema.

Nota 2: Este tipo de problema puede ocurrirle tanto a los usuarios de autenticación de Windows como a los de autenticación de SQL.

Espero que hayas disfrutado de este caso de error resuelto desde un grupo de Whatsapp, y si algún día te pasa esto también, espero que este artículo te ayude 🙂
¡Un abrazo grande y hasta la próxima!