Доступ к Active Directory с помощью прилинкованного SQL-сервера

Иван Коробко

Доступ к Active Directory с помощью прилинкованного SQL-сервера

Ни для кого не секрет, что скорость получения данных из Active Directory с помощью штатных средств в сценарии оставляет желать лучшего. Одно из решений проблемы – использовать прилинкованный SQL-сервер.

Осуществляя доступ к Active Directory с помощью ADsDSOObject, обеспечивают полный доступ к свойствам ее объектов: их можно создавать, удалять, изменять свойства. Основной недостаток использования ADsDSOObject-провайдера – очень низкая скорость получения данных. Например, если читать из Active Directory свойства 500-600 объектов, отфильтровывая их из общей массы, то эта процедура занимает по времени 1-3 минуты. Увеличить скорость чтения данных можно, используя в качестве провайдера прилинкованный к Active Directory SQL-сервер. Скорость решения аналогичной задачи сокращается до нескольких секунд. Однако не все так хорошо, как кажется с первого взгляда. Во-первых, с помощью прилинкованного сервера можно осуществлять только быстрый поиск и чтение данных. Так что записывать данные придется с помощью ADODB‑соединения. Во-вторых – по умолчанию прилинкованный сервер имеет ограничение по количеству выводимых записей – 1000. Это ограничение – изменяемый параметр (см. KB243281, Conrolling the Active Directory Search Buffer Size (http://support.microsoft.com/kb/243281)), но все-таки необходимо о нем помнить при формировании запросов.

Теория создания прилинкованного сервера

Прилинкованный сервер можно создать двумя способами – программным и вручную.

Создание прилинкованных серверов вручную

Чтобы создать прилинкованный сервер вручную, необходимо войти в «SQL Server Enterprise Manager -> SQL Server Group -> Сервер -> Sequrity -> Linked Server» и в контекстном меню выбрать «New Linked Server…». В диалоговом окне присутствуют следующие параметры (см. рис. 1):

• Linked name – произвольное имя создаваемого сервера. Максимальная длина 128 символов.
• Provider name – имя провайдера выбирается из списка, например Microsoft Jet 4.0 OLE DB Provider. Максимальная длина этого обязательного параметра – 128 символов. Остальным параметрам значения присваиваются по необходимости.
• Product name – комментарий прилинкованного сервера размером до 128 символов.
• DataSource – имя источника данных или его IP-адрес. Максимальная длина 4000 символов.
• Provider String – строка соединения, в которой могут быть заданы имя, пароль и другие характеристики доступа, например – «Encrypt Password=False;Integrated Security=SSPI;». Максимальная длина 4000 символов.

Рисунок 1. Создание прилинкованного сервера

Во второй вкладке – «Security» осуществляется конфигурация безопасности. Для прилинкованного сервера к Active Directory настройки можно оставить по умолчанию: «Be may using the login’s current security context».

Третья вкладка – «Server Options». Удостоверьтесь, что отмечены опции Data Access и Use Remote Collation. Использование RPC в случае соединения с Active Directory не обязательно.

Создание прилинкованного сервера к Active Directory на практике

Для создания прилинкованного к AD сервера используется провайдер OLE DB Provider for Microsot Directory Services (ADsDSOObject). Именно его необходимо выбрать в списке «Provider Name» во вкладке «Settings». В поле «Data Source» указывают adsdatasource. Рекомендуется добавить описание созданного сервера в поле «Product Name», например Active Directory Service Interfaces. Остальные настройки остаются без изменений (см. рис. 2).

Рисунок 2. Прилинкованный к AD сервер

Если по какой-то причине OLE DB Provider for Microsot Directory Services провайдер отсутствует в предлагаемом списке, необходимо изменить значение параметра AllowInProcess (тип DWORD) в ветви HKLM\Software\Microsoft\MSSQLServer\Providers\ADSDSOObject на 1 (см. рис. 3).

Рисунок 3. Провайдер OLE DB Provider for Microsoft Directory Services в реестре

Существует альтернативный вариант прилинкованного сервера с помощью Microsoft Jet 4.0 OLE DB Provider. Все настройки остаются прежними, за исключением Provider String:

Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648

В созданном прилинкованном сервере находятся два объекта – Tables и Views. При попытке посмотреть их наполнение провайдер данных выдаст сообщение об ошибке:

Не обращайте внимания на него и переходите к созданию запроса.

Программное создание прилинкованного сервера

Для программного создания прилинкованного сервера программным способом используется функция sp_addlinkedserver, у которой следующий синтаксис:

sp_addlinkedserver [ @server = ] 'server'

[ , [ @srvproduct = ] 'product_name' ]

[ , [ @provider = ] 'provider_name' ]

[ , [ @datasrc = ] 'data_source' ]

[ , [ @location = ] 'location' ]

[ , [ @provstr = ] 'provider_string' ]

[ , [ @catalog = ] 'catalog' ]

где:

• server – имя создаваемого прилинкованного сервера;
• product_name – его описание;
• provider_name – имя провайдера, к которому прилинковывается сервер;
• data_source – источник данных – имя или IP-адрес ресурса;
• location – местонахождение базы как интерпретатора OLE-DB провайдера;
• provider_string – строка соединения, содержащая параметры создаваемого соединения, при необходимости;
• catalog – имя каталога, который будет использоваться как временный во время соединения с провайдером.

На практике запрос, формирующий прилинкованный сервер (см. рис. 2) к Active Directory, следующий:

Использование прилинкованного сервера

Прежде чем рассказывать о чтении данных через прилинкованный сервер, приведу пример получения данных из АD с помощью обычного ADODB-соединения. Рассмотрим задачу формирования списка компьютеров в текущем домене. Листинг на VBScript:

Set objConnection = СreateОbject("ADODB.Connection")

Set objCommand = СreateОbject("ADODB.Command")

objConnection.CommandTimeout = 120

objConnection.Provider = "ADsDSOObject"

objConnection.Open "Active Directory Provider"

Set objCommand.ActiveConnection = objConnection

Set sql=objconnection.execute("SELECT cn FROM 'LDAP://DC=firm,DC=ru' WHERE objectClass='Computer'" )

sql.Movefirst

Do Until sql.EOF

    Wscript.Echo sql.Fields("cn").Value

    sql.MoveNext

Loop

В данном и во всех последующих листингах для наглядности имя домена намеренно присутствует в явном виде.

Query Analyzer

Чтение данных в Query Analyzer осуществляется с помощью:

openquery( linked_server , 'query' )

где linked_server – имя прилинкованного сервера, а query – SQL-запрос, заключенный в кавычки.

Запрос, формирующий список компьютеров, входящих в домен, включает в себя подзапрос, в котором непосредственно идет обращение к AD:

select * from openquery

(ADSI,

'

    select cn

    from ''LDAP://DC=firm,DC=ru''

    where objectclass=''computer''

')

Замечание: в SQL-запросе «select cn from ''LDAP://DC=firm,DC=ru'' where objectclass=''computer''» имя домена и параметр фильтра заключаются в двойные апострофы, а не в кавычки.

Характеристики, считываемые из Active Directory, могут относиться к одному из следующих типов данных: строка, массив, бинарное значение, число. Создавая более сложные SQL-запросы, учитывайте тип данных считываемой переменной. Используя функцию Convevert, осуществляйте преобразование типов. Она имеет три параметра: type – тип данных, к которому необходимо преобразовать переменную, num – максимальное ограничение количества символов, field – имя поля. При считывании данных из AD данные преобразуются к строке переменной длины: varchar(50). Синтаксис функции выглядит следующим образом:

openquery( linked_server , 'query' )

Рассмотрим характерный пример. Требуется построить таблицу, в которой будет две колонки – UserName и Description. В первую колонку должны попасть все имена пользователей в домене. Им соответствует значение поля cn в AD. Во вторую – должности соответствующих сотрудников, которые зафиксированы в поле Description его учетной записи.

В основном запросе вместо «*» должны быть указаны два поля. Причем данные необходимо преобразовать, а столбцы переименовать. Преобразование типов данных осуществляется с помощью описанной ранее функции convert(), а переименование столбцов – с помощью инструкции OldFieldName as NewFieldName, где OldFieldName – считанное имя поля, а NewFieldName – отображаемое. Таким образом, основной запрос выглядит так:

Select Convert(varchar(50), cn) as UserName,

Convert(varchar(50), description)

From openquery (ADSI, '…')

Поскольку желаемое название второго столбца совпадает с именем переменной, то нет необходимости его переименовывать.

С помощью вложенного запроса считываем данные из AD:

Select cn, description from 'LDAP://DC=firm,DC=ru' where objectclass='person' and not objectclass='computer'

Суммируя сказанное, получим следующий запрос:

Select Convert(varchar(50), cn) as UserName,

Convert(varchar(50), description)

From openquery (ADSI,

    'Select cn, description

    from ''LDAP://DC=firm,DC=ru''

    where objectclass=''person'' and not objectclass=''computer''

')

VBScript

Для подключения к SQL-серверу на VBScript также используется ADODB-соединение, однако связь с AD идет не напрямую через ADsDSOObject, а с помощью прилинкованного сервера.

С помощью функции СreateОbject создают два объекта: Adodb.Сonnection – для соединения с SQL-сервером и Аdodb.Recordset – с прилинкованным к нему сервером. В строке соединения не нужно указывать имя базы, поскольку ни к одной из них подключения не осуществляется:

set a=СreateОbject("Adodb.Сonnection")

str="driver={sql server};server=SQLServer;trusted_connected=yes;"

a.open(str)

a.cursorlocation=3

set rs=СreateОbject("Аdodb.Recordset")

str_q="select * from ОpenQuery (ADSI, 'select cn from ''LDAP://DC=firm,DC=ru'' where objectclass=''computer'' ')"

rs.open str_q, a, 1, 2, 1

rs.movefirst

Do Until rs.EOF

    Wscript.Echo rs.Fields(0).Value

    rs.MoveNext

Loop

Замечание: если созданный скрипт запускается от имени локального, а не доменного пользователя, то SQL-сервер выдаст сообщение об ошибке:

Практика использования прилинкованного сервера

Ранее говорилось об ограничении прилинкованного сервера: он может использоваться только для чтения данных. Записать какие-либо параметры в AD с его помощью невозможно. Комбинация чтения данных из AD с помощью прилинкованного сервера и запись их в базу с помощью ADODB позволяют создавать быстро работающие скрипты, которые можно интегрировать в ASP, ASPX-файлы. Предположим, что необходимо анализировать поле telephonenumber всех учетных записей пользователей в Active Directory, и если поле содержит информацию – записать в него «***».

Алгоритм сценария следующий: с помощью прилинкованного сервера сформируем запрос, который будет выводить список пользователей, у которых поле telephonenumber пустое. Прежде чем создавать скрипт на VBScript, настоятельно рекомендуется сформировать запрос к прилинкованному серверу в Query Analyzer. Это позволит ускорить создание скрипта. Для удобства контроля в Query Analyzer сделаем в таблице два поля – имя (UserName) и номер телефона (Tel), которым соответствуют поля cn и telephonenumber. В подзапросе непосредственно к AD укажем оба эти поля.

Теперь нужно правильно сформировать фильтр where. Во-первых, необходимо отфильтровать учетные записи пользователей от всех объектов (см. таблицу).

Соответствия объектов классам AD

Как видно из таблицы, objectclass должен принадлежать к классу Person или User и не принадлежать к классу Computer. Поэтому фильтр должен быть таким:

objectclass=''person'' and not objectclass=''computer''

К нему необходимо добавить еще одно условие:

not telephonenumber=''*''

Замечание:

1. Обратите внимание, что инструкция «telephonenumber is NULL» не будет работать, так как в запросе к AD используется упрощенная версия интерпретатора SQL, многие функции не поддерживаются.
2. Необходимо помнить, что последовательность выражений в запросе играет роль: наивысший приоритет у первого выражения, наименьший – у последнего.

Учитывая описанное, составим SQL-запрос к AD через прилинкованный сервер:

select

convert(varchar(50), cn) as UserName,

convert(varchar(50), telephonenumber) as Tel

from openquery

(ADSI, '

    select cn, telephonenumber

    from ''LDAP://DC=firm,DC=ru''

    where objectclass=''person''

    and not objectclass=''computer''

    and not telephonenumber=''*тел.*''

')

Сценарий будет работать гораздо медленнее, если, к примеру, фильтр телефонного номера вынести из подзапроса в основной, поэтому старайтесь использовать все фильтры в непосредственном запросе к Active Directory. Приведу пример неправильного использования фильтра. Красным отмечены внесенные в листинг изменения:

select

convert(varchar(50), cn) as UserName,

convert(varchar(50), telephonenumber) as Tel

from openquery

(ADSI, '

    select cn, telephonenumber

    from ''LDAP://DC=firm,DC=ru''

    where objectclass=''person''

    and not objectclass=''computer''

/*  and not telephonenumber=''*тел.*'' */

')

where telephonenumber is null

Добившись правильной работы сценария, перейдем к формированию скрипта на VBScript.

Запись значений параметров в Active Directory осуществляется с помощью ADODB-соединения с использованием провайдера ADsDSOObject. Исходными данными являются distinguishedName – местоположение объекта и имя корректируемого поля, поэтому созданный SQL-запрос необходимо трансформировать, заменив поле cn на distinguishedName.

Сценарий работает по следующему алгоритму: сначала создается запрос. Его результатом является список, каждая строка которого содержит два параметра. Они передаются в функцию записи параметров.

Рассмотрим отдельно сценарий записи данных в Active Directory. Изменение атрибутов любого объекта осуществляется с помощью метода Put() с предварительным получением к нему доступа:

Domain = GetObject("LDAP://rootDSE").Get("defaultNamingContext")

Set oUser = GetObject("LDAP://cn=Ivanov,Ivan,"& Domain)

oUser.Put "Description", "Иванов Иван Петрович"

oUser.SetInfo

Set oUser = Nothing

Первый аргумент метода Put – изменяемое поле, второй – его значение. Описанному алгоритму соответствует следующий сценарий:

set a=СreateОbject("Adodb.Сonnection")

str="driver={sql server};server=SQLServer; ?

    trusted_connected=yes;"

a.open(str)

a.cursorlocation=3

set rs=СreateОbject("Аdodb.Recordset")

str_q="select convert(varchar(50), distinguishedName ) as Path, convert(varchar(50), telephonenumber) \

as Tel from openquery (ADSI, 'select distinguishedName , telephonenumber from ''"+Domain+"'' \

where objectclass=''person'' and not objectclass=''computer'' and not telephonenumber=''*тел.*'' ')"

rs.open str_q, a, 1, 2, 1

rs.movefirst

Do Until rs.EOF

    WriteDate (rs.Fields(Path).Value, rs.Fields(Tel).Value)

    rs.MoveNext

Loop

Function WriteDate(a,b)

    Set oUser = GetObject("LDAP://"& a)

    oUser.Put " telephonenumber", b

    oUser.SetInfo

    Set oUser = Nothing

End Function

Заключение

Прилинкованные серверы, кажущиеся с первого взгляда неудобными и громоздкими в использовании, позволяют значительно повысить скорость работы приложений, в которых они применяются. Отсутствие возможности записи в прилинкованные базы с помощью сервера может быть не только недостатком, но и преимуществом.

Если необходимо добиться, чтобы все пользователи имели доступ к базе данных только на чтение, – использование прилинкованного сервера является идеальным вариантом для решения задачи с точки зрения администрирования Если же необходимо обеспечить возможность делать изменения в базе – используйте сервер для получения необходимой для записи информации и записывайте данные традиционным способом.

Такой подход обеспечивает увеличение скорости работы созданных приложений.

Комментарии могут оставлять только зарегистрированные пользователи