Hey guys!
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.
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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] |
Result:
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:
|
1 2 3 4 |
SELECT * FROM sys.server_triggers WHERE is_ms_shipped = 0 AND is_disabled = 0 |
Result:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
Result:
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:
|
1 2 3 4 5 6 |
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 |
Result:
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 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!
Portuguese 
English
Muito bom o artigo!
Estou com um problema parecido. Em uma máquina SQL Server Standard não consigo conectar localhost, já realizei as devidas configurações no SQL Configuration Manager.. porém sem sucesso.. Alguma dica?
Muito interessante! Parabéns
Dirceu, Bom dia!
Excelente artigo!!
Estou com um problema pouco parecido. Não consigo conectar em uma instância Sql Server Standard via localhost, já realizei as configurações via SQL Server Manager porém não tive sucesso.. tem uma dica?
Olá Dirceu, bom dia.
Muito legal este post, eu mesmo nunca havia presenciado este cenário.
Vou guardar os procedimentos que você adotou.
Abraços.