Запрос к каталогу Active Directory (AD) из системы SQL Server позволяет получить полезные данные. Существует три распространенных метода запроса каталога AD:
- использование связанного сервера;
- использование менеджера подключений ADO.NET, входящего в состав службы SQL Server Integration Services (SSIS);
- использование компонента Script, входящего в состав службы SSIS.
Однако эти три метода не всегда возвращают одинаковые результаты. Давайте посмотрим, как реализовать каждый из них, и сравним полученные результаты.
Метод использования связанного сервера
Для запроса каталога AD с помощью связанного сервера необходимо сначала задействовать хранимую процедуру sp_add_linked_server для создания соединения с этим сервером. Затем нужно использовать команду OPENQUERY для прямого обращения к службе AD. Не стоит импортировать данные каталога AD в таблицу с целью ее последующего опроса, так как данные в таблице могут устареть. Предположим, что вы добавляете данные из AD в таблицу для выполнения запроса, и сразу же после этого сетевой администратор переводит пользователя из одной группы в другую. Данные в таблице окажутся устаревшими и неточными. Для получения актуальных данных вам необходимо напрямую опросить каталог AD с помощью команды OPENQUERY.
Для выполнения опроса связанного сервера учетная запись, от имени которой запущены службы SQL Server, должна быть членом домена. Кроме того, учетная запись должна иметь право на чтение каталога AD. Чтобы проверить настройку учетной записи, откройте папку Administrative Tools/Сontrol Panel/Services и дважды щелкните на ярлыке SQL Server (MSSQLSERVER) Service. Настройки учетной записи находятся на вкладке Log on. Если необходимо внести изменения в настройки учетной записи, то для вступления изменений в силу потребуется перезапустить службу.
После корректной настройки учетной записи можно использовать код, приведенный в листинге 1, для создания связанного сервера и получения информации о пользовательских учетных записях AD (имя, фамилия, уникальное имя, имя пользователя).
Для использования кода необходимо заменить фразу LDAP://DC=MyDomain, DC=com в блоке A на путь LDAP к конкретному домену. В результате запуска кода в моей среде AD было получено 2 500 записей.
Метод использования менеджера подключений ADO.NET
Менеджер подключений ADO.NET позволяет службе SSIS получать доступ к источникам данных с помощью провайдеров данных .NET. Одним из таких провайдеров является служба OLE DB Provider for Microsoft Directory Services. Вы можете использовать данного провайдера для установки подключения ADO.NET к каталогу AD и выполнения запроса. Для создания пакета SSIS, использующего менеджер подключений ADO.NET, необходимо сделать следующее.
- Запустите службу SQL Server Business Intelligence Development Studio (BIDS).
- В меню File выберите пункт New, Project. Убедитесь, что установлен тип проекта Business Intelligence Projects, и выберите шаблон Integration Services Project. Введите имя проекта.
- Добавьте поток данных. Выберите пункт Toolbox в меню View. Перетащите значок Data Flow Task из секции Toolbox Control Flow Items в рабочую область Control Flow. Дважды щелкните на добавленном потоке данных, чтобы перейти на вкладку Data Flow.
- Добавьте менеджер подключений. В меню SSIS выберите пункт New Connection, как показано на экране 1. Если отображаются не все пункты меню SSIS, приведенные на экране 1, щелкните на любом элементе рабочей области SSIS.
В диалоговом окне Add SSIS Connection Manager перейдите в раздел ADO.NET и щелкните кнопку Add. Выберите пункт New, чтобы открыть диалоговое окно Connection Manager, изображенное на экране 2.
В раскрывающемся меню Provider выберите пункт .Net Providers for OleDb\OLE DB Provider for Microsoft Directory Services. В поле Server or File name введите имя своего домена. Нажмите кнопку Test Connection. При удачном подключении к службе AD дважды щелкните OK, чтобы вернуться к рабочей области Data Flow.
5. Добавьте источник данных. Перетащите значок DataReader Source из окна Toolbox Data Flow Sources в рабочую область Data Flow. Дважды щелкните на источнике данных для его настройки. В таблице Connection Managers выберите созданное подключение ADO.NET из раскрывающегося списка. В таблице Component Properties введите свой запрос в поле SqlCommand. В листинге 2 приведен пример простейшего запроса.
Для использования данного кода необходимо заменить строку «LDAP://DC=MyDomain, DC=com» в блоке A на путь LDAP к конкретному домену AD. Дважды нажмите кнопку OK.
6. Добавьте выходной контейнер. В данном примере выходным контейнером для данных, полученных при запросе каталога, является текстовый файл. Перетащите значок Flat File Destination из раздела Toolbox Data Flow Destination в рабочую область Data Flow. Выберете компонент Source. Подведите зеленую стрелку, указывающую хранилище выходных данных, к значку выходного контейнера и дважды щелкните на нем мышью. В окне Flat File Destination Editor нажмите на кнопке New, после чего на экране появится диалоговое окно Flat File Format. Выберите неограниченный формат файла Delimited и нажмите OK. В появившемся окне Flat File Connection Manager Editor введите имя файла «C:\AD_Output_ADONET.txt» в поле File name. Установите флаг в поле Unicode — поставщик OLE DB Provider for Microsoft Directory Services возвращает данные в виде текста с кодировкой Unicode. В поле Text Qualifier введите двойную кавычку ("). Двойная кавычка используется в качестве разделителя текста, так как значения некоторых свойств объектов AD могут содержать запятые. Установите флажок в поле Column names in the first data row. В остальных полях редактора Flat File Connection Manager Editor можете оставить значения, заданные по умолчанию (например, в поле настройки разделителя строк оставить выражение {CR}{LF}, а в поле настройки разделителя столбцов — запятую). Щелкните OK. В левой панели Flat File Destination Editor левым щелчком мыши выберите параметр Mappings и нажмите OK.
7. Сохраните пакет SSIS, выбрав пункт Save Selected Items в меню File.
8. Запустите обработку созданного пакета, выбрав пункт Start Debugging в меню Debug. Приблизительный вид вкладки Data Flow в этот момент, приведен на рисунке. Как мы видим, при выполнении запроса AD было найдено 2 500 записей.
9. Нажмите кнопку Stop Debugging, чтобы вернуться к редактированию пакета SSIS.
Метод использования компонента Script
Компоненты Script позволяют добавлять пользовательские сценарии в поток данных пакета SSIS. В данном случае компонент Script применяется для добавления сценария Visual Basic.NET, использующего сборку Directory Services.NET (System.DirectoryServices.dll) для доступа к службе AD и запроса каталога. Для создания пакета SSIS, содержащего компонент Script, выполните следующие шаги.
1. Повторите первые два действия, описанные в разделе «Метод использования менеджера подключений ADO.NET».
2. Добавьте две переменные. Переменные gLDAPQuery и gLDAPFilter содержат информацию, используемую при запросе AD. В меню SSIS выберите пункт Variables. В диалоговом окне Variables щелкните на кнопке Add Variables. Сначала создайте переменную gLDAPQuery, задав ей строковый тип данных.
В качестве значения переменной gLDAPQuery задайте путь LDAP к своему домену AD (например, LDAP://DC=MyDomain, DC=com). После этого создайте еще одну строковую переменную — gLDAPFilter. В качестве значения переменной gLDAPFilter задайте фильтр, который будет использоваться при запросе каталога AD, например (& (objectCategory=Person) (objectClass=user)). Благодаря этим переменным вам не нужно будет редактировать компонент Script при каждом изменении фильтра запроса. Вы можете просто изменить значения переменных.
3. Добавьте компонент Script. Перетащите значок Script Component из раздела Toolbox's Data Flow Transformations в рабочую область Data Flow. При добавлении компонента Script появится диалоговое окно Select Script Component Type. Выберите тип Source, так как создаваемый компонент будет использоваться в качестве источника данных каталога AD. Нажмите OK, чтобы завершить добавление компонента Script в рабочую область Data Flow. Дважды щелкните на значке компонента для вызова редактора Script Transformation Editor, после чего выберите в левой панели Inputs and Outputs. В средней панели выберите элемент Output 0 и после этого в таблице Common Properties, размещенной в правой панели, замените название Output 0 на более информативное — ActiveDirectoryOutput.
4. Укажите список столбцов выходных данных, необходимых для окончательного формирования потока трансформации. В данном примере в качестве столбцов зададим список столбцов AD. В центральной панели разверните узел ActiveDirectoryOutput и выберите Output Columns. Для каждого из свойств каталога AD, указанных в таблице, нажмите кнопку Add Column и отредактируйте значения полей Name, DataType и Length соответственно.
Как показано на экране 3, поле Name находится в таблице Common Properties, а поля DataType и Length — в таблице Data Type Properties.
5. Укажите имена переменных в окне Script Transformation Editor. Выберите Script в левой панели и введите gLDAPQuery, gLDAPFilter в поле ReadOnlyVariables. Постарайтесь избежать ошибок при написании данной строки. Щелкните на кнопке Design Script для вызова окна приложения Microsoft Visual Studio for Applications (VSA).
6. Задайте ссылку на библиотеку System.DirectoryServices.dll, которая в дальнейшем будет использоваться для опроса каталога. В окне VSA щелкните правой кнопкой в Project Explorer и в контекстном меню выберите пункт Add Reference. В диалоговом окне Add Reference выберите библиотеку System.DirectoryServices.dll из списка компонентов .NET (экран 4).
Нажмите кнопку Add и далее OK.
7. Замените сценарий, предложенный по умолчанию, на собственный сценарий Visual Basic.NET. Выберите пункт Project Explorer в меню View и дважды щелкните на названии ScriptMain, чтобы просмотреть сценарий Visual Basic.NET. Замените заданный по умолчанию сценарий на код из листинга 3.
Обратите внимание: из-за того, что сценарий не устанавливает полного соответствия между именами классов и соответствующими пространствами имен .NET, файл ActiveDirectory_Source_Script.txt содержит выражение Imports System.DirectoryServices. Если вы хотите удалить выражение Imports System.DirectoryServices, то при объявлении классов DirectoryServices необходимо указывать соответствующее им пространство имен, в данном случае — System.DirectoryServices. Например, строку
Dim de As New DirectoryEntry
необходимо заменить на
Dim de As New
System.DirectoryServices.
DirectoryEntry
Чтобы закрыть редактор VSA Script Editor и вернуться к рабочей области SSIS, щелкните на команде Close & Return в меню File. Нажмите OK.
8. Добавьте выходной контейнер. Следуйте указаниям шага 5 в разделе «Метод использования менеджера подключений ADO. NET». Только при этом измените имя файла на «C:\AD_Output_Script.txt» и не устанавливайте флажок в поле Unicode.
9. Укажите средство просмотра данных, с помощью которого будете просматривать данные при использовании пакета SSIS в режиме разработки. В рабочей области Data Flow щелкните правой кнопкой мыши на зеленой стрелке и в контекстном меню выберите пункт Data Viewers (экран 5).
Щелкните кнопку Add и затем дважды OK. Имейте в виду, что, если на данном этапе вы решите добавить новые столбцы AD (то есть новые столбцы выходных данных), вам придется перенастроить выбранное средство просмотра данных. Для этого щелкните правой кнопкой мыши на зеленой стрелке, в контекстном меню выберите пункт Data Viewers и в появившемся окне нажмите кнопку Configure. Закончив выбор просматриваемых столбцов, дважды щелкните OK.
10. Сохраните пакет SSIS, выбрав пункт Save Selected Items в меню File.
11. Нажмите клавишу F5, чтобы запустить обработку пакета в среде SSIS Designer. Вы увидите картину, аналогичную приведенной на экране 6.
В процессе обработки данные, полученные из каталога AD, размещаются в наборах буферов. В строке состояния выбранного средства просмотра отображается количество использованных буферов и общее количество полученных на данный момент строк. Число строк в текущем буфере отображается в поле «Rows displayed=».При нажатии кнопки Play программа переходит к заполнению следующего буфера. При обработке пакета в моем домене AD на выходе было получено 3 598 записей.
Подведем итоги
Если сравнить количество записей, полученных в результате применения каждого из рассмотренных методов, то окажется, что метод использования компонента Script возвращает на выходе намного больше записей, чем два других метода (3 598 против 2 500). Методы использования связанного сервера и менеджера подключений ADO.NET имеют ограничение на количество записей, которые могут быть получены из каталога AD. Этот предел положен для защиты от атак типа denial of service и перегрузки сети. Дополнительную информацию можно найти в статье Microsoft «Performing a SQL distributed query by using ADSI» (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q299410). В моем случае лимит был равен 2 500 записям, однако в другом окружении это значение может быть иным, в зависимости от применяемого программного и аппаратного обеспечения и сетевой инфраструктуры.
При использовании компонента Script подобного ограничения нет. Данное преимущество достигается благодаря наличию в файле ActiveDirectory_Source_Script.txt следующей строки:
searcher.PageSize = 1000
Действительно, если закомментировать данную строку и запустить повторную обработку пакета SSIS, на выходе будет получено только 2500 записей. Использование различных классов, методов и свойств классов Microsoft.NET Framework позволит получить более полный контроль над поведением запросов к каталогу AD. В данном примере применяется свойство PageSize из библиотеки System.DirectoryServices.dll. Оно доступно только при использовании компонента Script и не может применяться при запросе с помощью связанного сервера или менеджера подключений ADO.NET.
Выбор того или иного метода запроса к каталогу AD зависит от конкретной ситуации. Если в вашем окружении используется крупная база данных AD, применение компонента Script будет оптимальным выбором. Однако в окружениях с небольшими по объему базами данных, возможно, лучше использовать связанный сервер или менеджер подключений ADO.NET, так как эти два метода проще в реализации.
Как уже упоминалось выше, сценарий ActiveDirectory_Source_Script содержит простейшие пакеты SSIS, применяемые при использовании менеджера подключений ADO.NET и компонента Script. Создание и тестирование кода связанного сервера и двух пакетов SSIS проводилось в системе SQL Server 2005 SP2 (x64). Имейте в виду, что сценарий ActiveDirectory_Source_Script.txt разрабатывался для службы SSIS 2005. Для использования сценария службой SSIS 2008, возможно, его придется отредактировать. Дело в том, что служба SSIS 2005 при работе с компонентом Script использует обработчик сценариев VSA 8.0, в то время как служба SSIS 2008 в качестве обработчика сценариев задействует пакет Microsoft Visual Studio Tools for Applications (VSTA) 2.0.
Джамиль Ахмед (jameel_ahmed@canaccord.com) — старший администратор баз данных и аналитик в Canaccord Capital. Работает с SQL Server с 1998 года и имеет сертификаты MCDBA и MCAD
Листинг 1. Запрос к каталогу AD с помощью связанного сервера
Листинг 2. Опрос каталога AD с помощью источника данных DataReader Source
Листинг 3. Сценарий ActiveDirectory_Source_Script Imports System
Таблица. Столбцы выходных данных