Fala pessoal!
Nesse post, eu gostaria de compartilhar um erro que foi reportado em um grupo do Whatsapp e que eu particularmente, nunca havia visto antes, que era a mensagem de erro abaixo e o relato que os usuários que utilizavam autenticação SQL só conseguiam se conectar na instância se tivessem privilégios de sysadmin.

Login failed for user ‘teste’. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors

De cara, já pedi pra ele verificar se a instância estava permitindo conexões vindas de autenticação SQL Server, já que o padrão é vir habilitado para aceitar somentes conexões com autenticação 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:

Primeira validação foi realizada com sucesso. Outro ponto que pode causar esse tipo de erro, são as Triggers de Logon. Pedi que ele verificasse se existiam triggers de logon na instância e, caso existisse, desabilitasse as triggers para testar se era isso que estava bloqueando:

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

Resultado:

Desativei a trigger e não tinha nada impedindo o login. Vamos olhar no log do SQL Server para ver se temos alguma pista do problema:

A mensagem de erro é bem clara. Meu próximo suspeito agora são as permissões nos endpoints da instância. Vou analisar como está as permissões dos Endpoints:

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! A role public está sem permissão para acessar os endpoints. Alguém provavelmente fez alguma modificação na permissão padrão e os usuários com autenticação SQL não estão com acesso nos endpoints. Para resolver isso, vamos conceder essas permissões:

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:

Agora copie esses comandos de Grant e aplique na sua instância para liberar permissão de CONNECT para a role public em todos os endpoints.

Após isso, os usuários com autenticação SQL Server conseguiram conectar normalmente na instância 🙂

Observação 1: Remover a permissão de CONNECT nos endpoints da role public não está “errado”. O DBA apenas precisa pensar em formas de liberar essa permissão para usuários ou grupos de autenticação SQL, para que eles possam continuar acessando o ambiente sem permissão de sysadmin.

Observação 2: Esse tipo de problema pode acontecer tanto com usuários de autenticação Windows, quanto autenticação SQL.

Espero que tenham gostado desse caso de erro resolvido a partir de um grupo do Whatsapp, e se um dia isso acontecer com vocês também, espero que esse artigo ajude 🙂
Um grande abraço e até a próxima!