Использование Kerberos при работе с SQL Server Reporting Service

Написать эту статью меня сподвигла работа, которую я недавно выполнял для одного из заказчиков.

По работе с Kerberos и делегированием написано достаточно, как на русском (https://www.osp.ru/winitpro/2011/03/13009255/), так и на английском языке, и пересказывать весь этот материал - пустая трата времени. В данной статье мы лишь обратимся к практической части настройки системы делегирования.

Здесь я постарался изложить в доступной форме пошаговую инструкцию, которая позволит вам не только настроить Kerberos для работы с SQL Server Reporting Service (SSRS), но и с другим подобными сервисами, использующими SQL Server.

Несколько слов о конфигурации.

Мы имеем пять действующих лиц. Три из них это основные и две вспомогательные.

К основным относятся:

• Web-клиент, выполняющий запрос к порталу SSRS.
• SQL Server Reporting Service (SSRS) базы которого размещены на SQL Server.
• И собственно SQL Server.

Немного об этой тройке.

Если SSRS и SQL Server размещены на одном сервере, то проблем, о которых мы будем говорить ниже, не возникает, поскольку (в такой конфигурации) нет потребности в делегировании, и, в крайнем случае (при проблемах с Kerberos), сработает NTLM-аутентификация и запрос от SSRS к SQL Server будет выполнен.

Основные трудности возникают при “выносе” SQL Server на отдельный компьютер. В чем суть возникающей проблемы? А суть состоит в том, что при таком размещении NTLM аутентификация не сработает, поскольку не она “не умеет” передавать данные о пользователе с SSRS на SQL Server. При такой конфигурации SSRS примет запрос от Web-клиента, в котором будут данные о пользователе, прошедшем аутентификацию либо по NTLM, либо по Kerberos, но без дополнительных настроек эти данные терминируются на SSRS и далее не передаются. В результате SSRS будет пытаться выполнять запрос к SQK Server используя анонимное подключение, которое по умолчанию запрещено, что приведет к ошибке выполнения запроса. В прошлые времена мы решали эту проблему просто создавая отдельную учетную запись от которой выполнялся запрос к SQL Server. Однако такое решение имеет серьезные риски безопасности, поскольку не позволяет разграничить доступ к данным на SQL Server в соответствии с полномочиями пользователей.

Какое же решение мы можем использовать при размещении всех компонент на разных компьютерах?

Мы должны использовать Kerberos аутентификацию и делегирование.

Для реализации этого нам понадобятся еще две обязательные дополнительные компоненты:

• Контроллер домена (DC).
• Служба разрешения доменных имен (DNS).

Контроллер домена будет выполнять несколько функций (https://www.osp.ru/winitpro/2011/03/13009255/), а DNS – выполнять разрешение имен в IP-адреса.

И так приступим.

Ниже приведена конфигурация, с которой мы будем работать.

Подключимся к точке 3 и выполним запрос.

Как видно из результат выполнения запроса SSRS использует для подключения к SQL Server NTLM аутентификацию.

Откроем SQL Server Management Studio на SSRS и подключимся к SQL Server (точка 2).

Как видно из результатов выполнения запроса, все подключения выполняются с аутентификацией NTLM.

Выполним с SSRS (2) запрос, который мы будем использовать для отчета.

И еще раз посмотрим на SQL Server (3), какой метод аутентификации использует запрос.

Как и ожидалось это опять NTLM.

Давайте попробуем открыть отчет, созданный с помощью Report Builder и использующий запрос, показанный выше.

Все сработало. Неужели Report Builder использует протокол Kerberos вместо NTLM?

Нет. Тоже NTLM. Так почему все работает?

Давайте выполним тот же отчет с SSRS.

С SSRS отчет не работает, выдавая какую-то странную ошибку о попытке анонимного логона.

Посмотрим тип аутентификации.

Как видно в обоих случаях (и с Report Builder и с SSRS) используется NTLM. Почему в одном случае отчет работает, а во втором нет?

Попытаемся разобраться.

При работе с Report Builder пользователь передает ему свой маркер доступа (Token Assess, TA), полученный от контроллера домена, и приложение (а Report Builder это приложение), передает TA на SQL Server. Поскольку у меня есть логин на сервере баз данных и этот логин имеет достаточно привилегий, то все работает правильно и отчет выполняется.

При работе же с SSRS пользователь обращается к Reporting Service, а уже он от своего имени пытается выполнить запрос. Он не может передать мой маркер доступа на SQL Server поскольку он не может произвести делегирование (передачу как эстафетной палочки) моей учетной записи на SQL Server. Для выполнения такой операции необходим Kerberos, этот злой трех-головый пес охраняющий вход.

Почему же SQL Server не использует Kerberos аутентификацию?

А все потому, что не выполнено одно из условий использования Kerberos - наличие Service Principal Name (SPN) для учетной записи от которой стартован SQL Server. Об SPN написано много статей (https://technet.microsoft.com/ru-ru/library/cc280745%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396), и я их повторять не буду. Я просто возьму и создам SPN-ы для учетной записи от которой стартован SQL Service.

Создать SPN-ы можно с использование специальной утилиты SETSPN.EXE (https://technet.microsoft.com/ru-ru/library/ms191153(v=sql.105).aspx), а для ленивых (к которым отношусь и я) можно воспользоваться графической оболочкой на контроллере домена. Однако, хотя графическая оболочка - это удобно, я вам все же рекомендую разобраться и с возможностями утилиты. У нее есть ключ,  позволяющий проверять наличие дублированных SPN (ключ -X), а также ключ позволяющий, создавая SPN, сразу проверять на наличие дублированных и если они есть, то заменять их (ключ -D). По правде сказать, наличие дублированных SPN наиболее часто встречающаяся проблема почему не работает Kerberos.

Создадим SPN-ы для SQL Server из графической оболочки оснастки Active Directory Users and Computers.

Обратите внимание, что создано два SPN-а. Один с коротким именем, один с длинным. Принцип такой: вы ДОЛЖНЫ создавать SPN-ы таким образом, каким вы собираетесь обращаться к данному сервису.

В данном случае вы сможете обратиться к SQL Service либо через SQL2016-N1 на порт 1433, либо через SQL2016-N1.demo.local на порт 1433.

Выполним запрос к SQL Server из SSRS (2), используя SQL Server Management Studio (SSMS).

Обратите внимание, что уже для подключения через SSMS используется Kerberos. Однако SSRS все еще использует протокол NTLM. Для решения этой проблемы выполним рестарт Reporting Service.

После рестарта проверим режим аутентификации.

Вот теперь все как нужно.

Попробуем обратиться через SSRS.

Результат один и тот же.

Давайте выполним обращение с рабочей станции (1).

Обратите внимание, что здесь уже другая ошибка. Это связано с тем какая информация передается на рабочую станцию.

Почему не работает подключение, а потому, что не произошло делегирование учетной записи, от которой выполняется запрос на SQL Server.

Давайте посмотрим что происходит на SQL Server, воспользовавшись утилитой KLIST.EXE для просмотра информации по Kerberos.

Выполните на SQL Server команду KLIST.EXE sessions. Эта команда показывает сессии установленные к SQL Server. Попытайтесь отыскать строчку, где "x" это произвольные цифры.

[x] Session 0 0:0xxxxxxxx DEMO\Test Kerberos:Network 

Вы не сможете найти такую строку поскольку не работает делегирование, и информация о пользователе DEMO\Test не попадает на SQL Server.

Для решения данной проблемы выполним несколько действий:

1. Установите SPN-ы (как показано на рисунке ниже) для пользователя от которого функционирует Reporting Service.

1. Проверим, что в свойствах учетной записи, от которой стартован Reporting Service, не установлено свойство "Account is sensitive and cannot be delegated"
2. В свойствах компьютерного объекта, на котором функционирует Reporting Service, в закладке Delegation устновите "Trust this computer for delegation to any service (Kerberos only) "
3. В свойствах пользователя, от которого функционирует Reporting Service, в закладке Delegation устновите "Trust this user for delegation to any service (Kerberos only) "
   Примечание: Если у пользователя нет такой закладки, то это значит, что не установлен ни один SPN.

Теперь все условия выполнены, и попробуем еще раз выполнить обращение через Reporting Service к SQL Server.

Все работает.

Проверим наличие Kerberos сессии на SQL Server

KLIST.EXE sessions

Просмотрим результат и видим, что появилась строка с сессией для пользователя DEMO\Test.

Прежде чем завершить мое изложение, одно замечаение: Kerberos работает только с именами серверов, а IP-адреса он получает через DNS, поэтому создание SPN-а, содержащего IP-адрес, как показано ниже, не даст вам возможность использовать Kerberos при обращении по IP-адресу.

До свидания коллеги, до следующих встреч.

Александр Каленик, Senior Premier Field Engineer (PFE), MSFT (Russia)

Comments

• Anonymous
  October 04, 2016
  Ну это стандартная настройка учетных записей для MS SQL Server.Аналогичное делегирование нужно включать и для связанных серверов при использование текущего контекста в настройках связанного сервера
  • Anonymous
    July 26, 2017
    Это да.