Hoje no meu trabalho tivemos esta mensagem de erro ao ser criado um novo login em um servidor de Homologação SQL Server 2008 R2.

O cenário era o seguinte:

  • O usuário não deveria ser sysadmin, mas ter acesso apenas à uma determinada base de dados.
  • Deveria ser um usuário do tipo SQL Server Authentication
  • Criado o usuário, dadas as permissões necessárias, o usuário conectava localmente à instância do SQL Server
  • Remotamente o usuário não conectava e logava o erro no Log do SQL Server.

Validei todas as configurações do servidor, mesmo ele sendo um servidor já configurado à bastante tempo:

  • Mixed authentication – OK
  • Usuário criado corretamente – OK
  • Permissões – OK

Pesquisando na internet, encontrei um post aqui que indicava a necessidade de dar direito de connect do login no ENDPOINT “TSQL Default TCP”.

Problema, resolvido. Entretanto, eu não fiquei nem um pouco satisfeito com essa solução, já que teria que ser feito o mesmo procedimento para todos os logins que fossem criados no mesmo servidor desta data em diante.

Parti então para a investigação mais profunda… Com a informação de que o login conseguia conectar ao SQL localmente, eu lembrei do processo de conexão padrão do SQL Server:

  • Localmente o usuário estava logando utilizando Shared Memory, como é padrão para as conexões locais no servidor que hospeda o SQL Server.
  • Remotamente o usuário estava tentando logar via TCP e por conta disso, o Endpoint do SQL deveria estar corretamente configurado.
  • A mensagem de erro logada indicava um State: 12, que indica que o SQL conseguia autenticar o usuário, mas não conseguia validar as permissões. Veja neste link a tabela contendo todos os States possíveis no SQL Server.

De posse destas informações, eu já estava imaginando que havia algum problema com a role Public, e que ela não estava conseguindo acessar o SQL. Desta forma, bastou fazer a consulta:

exec sp_executesql N’SELECT
prmssn.state AS [PermissionState],
null AS [Code],
grantor_principal.name AS [Grantor],
prmssn.type AS [SqlCodePP]
FROM
sys.endpoints AS e
INNER JOIN sys.server_permissions AS prmssn ON prmssn.major_id=e.endpoint_id AND prmssn.class=105
INNER JOIN sys.server_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.server_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name=@_msparam_0)and((e.name=@_msparam_1))’,N’@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)’,@_msparam_0=N’public’,@_msparam_1=N’TSQL Default TCP’

Esta consulta trouxe a informação que me faltava, de que a role Public estava sem o direito de Connect (G de Grant na coluna State e CO na coluna SqlCodePP)

Finalmente então, bastou executar o comando abaixo para que o usuário conseguisse logar corretamente

grant connect on endpoint::[TSQL Default TCP] to [public]

Anúncios