Несколько раз сталкивалась с проблемой - в какой-то момент пользователь теряет право login-а в SQL Server. Никаких видимых причин - изменения прав, удаления login-а - а пользователь получает ошибку "Login failed".
В чем была причина, в тех случаях, о которых идет речь…
Представим следующий сценарий:
1) Вы создаете новую конечную точку для TCP:
CREATE ENDPOINT tcp_endpoint
STATE = STARTED
AS TCP(LISTENER_PORT = 7022)
FOR TSQL()
GO
При этом вы получаете следующее предупреждение:
"Creation of a TSQL endpoint will result in the revocation of any 'Public' connect permissions on the 'TSQL Default TCP' endpoint. If 'Public' access is desired on this endpoint, reapply this permission using 'GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]'."
Однако точка успешно создается, так что предупреждение остается без внимания J
2) Через какое-то время вы удаляете недавно созданную конечную точку:
DROP ENDPOINT tcp_endpoint
GO
И - сюрприз, сюрприз - у вас начинаются проблемы с невозможностью залогиниться.
Что произошло…
По умолчанию, в SQL Server существуют конечные точки для всех протоколов:
|
Протокол |
Имя конечной точки |
|
Shared memory |
TSQL LocalMachine |
|
Named pipes |
TSQL Named Pipes |
|
TCP/IP |
TSQL Default TCP |
|
VIA |
TSQL Default VIA |
|
DAC |
Dedicated Admin Connection |
|
HTTP |
HyperText Transport Protocol |
По умолчанию же, серверная роль public (все логины принадлежат этой роли) имеет право коннектиться через эти конечные точки. Т.е., если пользователь пытается соединиться с SQL Server, например, протокол TCP\IP, он будет соединяться через конечную точку [TSQL Default TCP] и должен иметь соответствующие права. Как я уже сказала, по умолчанию он их имеет, т.к. принадлежит серверной роли public, которая, в свою очередь, имеет права соединяться через дефолтные конечные точки.
Когда вы создаете новую конечную точку для TCP протокола, права public на дефолтную конечную точку отменяются, но, когда вы эту новую точку удаляете, они не возвращаются (о чем, собственно, и говорит предупреждение, приведенное выше). Таким образом, серверная роль public не имеет больше прав соединяться, используя протокол TCP\IP.
Как распознать…
Errorlog будет содержать ошибку "Login failed" со статусом 11 или 12. Например:
2009-05-22 18:33:25.78 Logon Error: 18456, Severity: 14, State: 12.
2009-05-22 18:33:25.78 Logon Login failed for user 'lgn_valid'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ...]
Следующий запрос возвращает список конечных точек, права соединяться через которые даны public:
Select sp.permission_name, sp.state_desc, ep.name
from sys.server_permissions sp join sys.endpoints ep
on sp.major_id = ep.endpoint_id
where sp.grantee_principal_id = 2 and sp.class = 105
GO
В нашем примере результат будет таким:
CONNECT GRANT TSQL Local Machine
CONNECT GRANT TSQL Named Pipes
CONNECT GRANT TSQL Default VIA
Как видно, протокол TCP отсутствует в списке.
Как поправить…
Нужно вернуть права соединяться через данную конечную точку серверной роли public:
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to public
GO