Эти коварные "конечные точки" в SQL Server

Несколько раз сталкивалась с проблемой - в какой-то момент пользователь теряет право 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 существуют конечные точки для всех протоколов:

По умолчанию же, серверная роль 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

Ссылки по теме

• Купить продукты Microsoft в интернет-магазине ITShop.ru
• Купить SQL Server 2008 в интернет-магазине ITShop.ru
• Курсы обучения компании Microsoft в интернет-магазине ITSHhop.ru
• Подписаться на рассылку "Вопросы и ответы по MS SQL Server"